Here's what I'd like to do. In 4.1.x, all of the unique constraint/index/column changes from HHH-7797 [1] and the random constraint names in HHH-1904 [2] will be reverted. When we originally started working on them, they weren't intended to be this complex. We'll cut a new 4.1.x release. But as always, the rabbit hole is deep. The changes will be kept in 4.2 & 4.3.
I'll wait a while before reverting and pushing in 4.1. If there are any objections, please say so ASAP. Regarding the concern brought up in HHH-8092 [3] (constraints re-generated in schema update, even if they exist): Defaulting to unique constraints, as opposed to indexes or unique columns, was the best route and most widely supported. As discussed, it's difficult and un-reliable to attempt to find existing constraints prior to updating. And remember that schema generation was never meant to be used for production environments -- supporting every single use case and situation is impossible. "and I'm curious of what you obtain in validate mode" Schema validation currently checks tables, columns, and sequences only. All that being said, I'm definitely up for improvement suggestions in 4.2/4.3. Feel free to create tickets and we'll discuss them. Guillaume, thanks much for the input. [1] https://hibernate.atlassian.net/browse/HHH-7797 [2] https://hibernate.atlassian.net/browse/HHH-1904 [3] https://hibernate.atlassian.net/browse/HHH-8092?focusedCommentId=50658&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-50658 Brett Meyer Red Hat Software Engineer, Hibernate ----- Original Message ----- From: "Guillaume Smet" <guillaume.s...@gmail.com> To: "Brett Meyer" <brme...@redhat.com> Cc: "Hibernate" <hibernate-dev@lists.jboss.org> Sent: Friday, April 5, 2013 4:40:45 AM Subject: Re: [hibernate-dev] 4.1.x and HHH-8092 aka no unique constraints after 4.1.10 upgrade Hi Brett, On Fri, Apr 5, 2013 at 2:38 AM, Brett Meyer <brme...@redhat.com> wrote: > One other issue was pointed out: > https://hibernate.atlassian.net/browse/HHH-8092?focusedCommentId=50658&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-50658 Oh, yeah. I started the application only once last night to check for the constraints creation. And another problem came to my mind this morning. What if I update an existing database to 4.1.12-SNAPSHOT: I'm going to have twice the same constraints (which is going to lead to twice the same index in PostgreSQL - bad idea for write performances and query planning time). Here is what I obtain on a database created before 4.1.10 after an upgrade to 4.1.12-SNAPSHOT: Indexes: "uk_user__followedticket_1" UNIQUE CONSTRAINT, btree (followingusers_id, followedtickets_id) "user__followedticket_followingusers_id_followedtickets_id_key" UNIQUE CONSTRAINT, btree (followingusers_id, followedtickets_id) Apart from more questions, I think this behaviour and your line of questioning give us at least a clear answer: we should revert these patches entirely for the time being and see if we can find an acceptable solution based on your patch, which looks good from a design point of view but definitely needs more thoughts on the upgrade path in update mode (and I'm curious of what you obtain in validate mode with a preexisting database). And if this patch ever gets commited, it should be in a x.x.0 release with a prominent release note about the upgrade, not in a maintenance release. That said, here are my 2 cents about your questions: > To my knowledge, there's not a reliable way to find existing constraints > using java.sql.DatabaseMetaData or queries (someone correct me if that's > wrong). Therefore, it will be either hard or impossible to know if a > constraint already exists prior to a schema update, leading to exceptions if > it does. Right. For a few databases (the ones which implement unique constraints with unique indexes), getIndexInfo can give you part of the information but it's not reliable and incomplete. > 1.) Should we simply attempt to drop constraints before re-creating all of > them on an update? No. I often create additional objects in my database aka the ones you cannot create with Hibernate (partial or implementation specific indexes, exclusion constraints...). You cannot drop what's in the database in update mode. > 2.) Was defaulting to unique constraints a bad idea? Switch to indexes or > unique columns? I don't think I can help with this one. I'm a bit too much a PostgreSQL specialist where unique constraints are indeed implemented as unique indexes. I don't know the implementation details of the other databases and if it's a good idea for them. Unique indexes and unique columns sound appealing as you'll be able to get the information from JDBC. The update path point pointed above still stands... > 3.) Was using randomly-generated constraint names a bad idea? Personnaly not my bag of tea. Did you do it to mitigate the problem with identifier name length limits? The update path point pointed above is also to consider for this type of changes... Happy to discuss these issues further with you if needed. -- Guillaume _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
