Re: [Engine-devel] short research on postgresql enums

On Mon 12 Dec 2011 10:43:39 AM IST, Laszlo Hornyak wrote: > > > ----- 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 +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(a)ovirt.org >>> http://lists.ovirt.org/mailman/listinfo/engine-devel >> >> > _______________________________________________ > Engine-devel mailing list > Engine-devel(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/engine-devel