11:43 a.m.
----- Original Message -----
From: "Itamar Heim" <iheim(a)redhat.com>
To: "Maor" <mlipchuk(a)redhat.com>
Cc: engine-devel(a)ovirt.org, "Laszlo Hornyak" <lhornyak(a)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(a)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
- 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.
>
> 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(a)ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel