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

----- 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 > > _______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel