[Engine-devel] short research on postgresql enums

[Itamar Heim]

On 12/14/2011 10:51 AM, Roy Golan wrote:
> 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.

afaik we are not using ordinal at all today. we are using the the 
"value" of the enum (all enums are enumerated numericaly regardless of 
ordinal)

>> - 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
>
>