On Mon 12 Dec 2011 10:43:39 AM IST, Laszlo Hornyak wrote:
----- Original Message -----
From: "Itamar Heim"<iheim@redhat.com> To: "Maor"<mlipchuk@redhat.com> Cc: engine-devel@ovirt.org, "Laszlo Hornyak"<lhornyak@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:
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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel +1 liked the article. I wonder, how common are database enums among different DB
On 12/09/2011 10:43 PM, Laszlo Hornyak wrote: 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@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
_______________________________________________ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel