[Engine-devel] short research on postgresql enums

[Roy Golan]

On Mon 12 Dec 2011 10:43:39 AM IST, Laszlo Hornyak wrote:
>
>
> ----- Original Message -----
>> From: "Itamar Heim"<iheim at redhat.com>
>> To: "Maor"<mlipchuk at redhat.com>
>> Cc: engine-devel at ovirt.org, "Laszlo Hornyak"<lhornyak at redhat.com>
>> Sent: Sunday, December 11, 2011 4:57:57 PM
>> Subject: Re: [Engine-devel] short research on postgresql enums
>>
>> On 12/11/2011 05:54 PM, Maor wrote:
>>> On 12/09/2011 10:43 PM, Laszlo Hornyak wrote:
>>>> Hi,
>>>>
>>>> I looked into Mike's database patch (
>>>> http://gerrit.ovirt.org/#change,500 ) today and read Yaniv's
>>>> comment on it. I have seen another patches related to enums and
>>>> how they are stored in the database. I made a quick test to
>>>> compare between varchar and enum and the results are here:
>>>> http://dummywarhead.blogspot.com/2011/12/postgresql-enums-vs-varchar.html
>>>>
>>>> IMO enums could be a good solution, but changing enums could be a
>>>> pain under postgres 9.1. So what if we could use varchar now and
>>>> migrate to enum once postgres 9.1 replaces the older
>>>> installations :)
>>>>
>>>> Laszlo
>>>> _______________________________________________
>>>> Engine-devel mailing list
>>>> Engine-devel at ovirt.org
>>>> http://lists.ovirt.org/mailman/listinfo/engine-devel
>>> +1 liked the article.
>>> I wonder, how common are database enums among different DB
>>> providers,
>>> for supporting DB migration in the future if needed.
>>
>> that's a very good point, considering we are still on the path of
>> moving
>> to hibernate.
>
> I did not do a test with hibernate yet, but since a postgresql enum column is read/written as character data, I believe hibernate would not see a difference between enum and varchar. On other RDBMS' these columns could be just plain varchar columns. So we can look at this as a PostfgreSQL-specific optimization.
>
> JPA supports ordinal or name ( http://docs.oracle.com/javaee/6/api/javax/persistence/EnumType.html ). The current JDBC DAO's persist using the custom logic written into the enums. I am not sure this is portable to JPA. So if we move to JPA, we will have a choice:
> - persist using ordinal - I think we would want to avoid this, since the code we have in enums and the DAO classes is to avoid this
+1 avoid in any case. Ordinal is very fragile and thus unreliable. 
> - persist using name - in this case the column can be an enum on postgresql, which actually has the same performance as the ordinal values (and the custom DAO code), or it can be a varchar (if enum is not supported by the rdbms) and it can be plain varchar, which performs somewhat under the integers.
do we need to watch out from long enum-member names in that case?
>
>>
>>>
>>> I also searched a little bit on the subject and found the following
>>> article:
>>>     http://www.designmagick.com/article/29/PostgreSQL-Enum-Types
>>> Postgres 8.4.7 supports the CHECK function, which validate specific
>>> values. (not sure how is the performance).
>>> but maybe we can use it, if we choose to adapt the enum idea later.
>>>
>>>
>>> _______________________________________________
>>> Engine-devel mailing list
>>> Engine-devel at ovirt.org
>>> http://lists.ovirt.org/mailman/listinfo/engine-devel
>>
>>
> _______________________________________________
> Engine-devel mailing list
> Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel