[hibernate-dev] 4.1.x and HHH-8092 aka no unique constraints after 4.1.10 upgrade

[Guillaume Smet]

Hi Brett,

On Fri, Apr 5, 2013 at 2:38 AM, Brett Meyer <brmeyer at 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