On Wed, Mar 18, 2015 at 9:19 AM, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Mar 17, 2015 at 3:11 PM, Heikki Linnakangas <hlinn...@iki.fi> wrote: >> I've been thinking that it would be nice to be able to specify a constraint >> name. Naming an index directly feels wrong, as in relational and SQL >> philosophy, indexes are just an implementation detail, but naming a >> constraint is a fair game. It would also be nice to be able to specify "use >> the primary key". > > Intuitively, I think you should specify an operator name, not a > constraint name. That's what we do for, e.g., exclusion constraints, > and it feels right. People sometimes create and drop indexes (and > thus, perhaps, the constraints that depend on them) for maintenance > reasons where a change in semantics will be unwelcome.
I think we should use a constraint name. That is the plain reality of what we're doing, and is less ambiguous than an operator. 99% of the time you'll be happy with an unspecified, across-the-board IGNORE, or won't be using exclusion constraints anyway (so we can infer a unique index). A constraint name covers all reasonable cases, since partial unique indexes are otherwise covered (partial unique indexes do not have a pg_constraint entry). Oracle has a hint for ignoring particular, named unique indexes (not constraints). I realize that Oracle hints are not supposed to affect semantics, but this is actually true (Google it). This is a bit ugly, but less ugly as the hint, since as Heikki points out we're only naming a constraint. Naming a constraint reflects the reality of how the feature needs to work, and has a sort of precedent from other systems. > But I don't > accept Peter's argument that it's OK to be indifferent to which > particular equality semantics are being used. I am not suggesting actual indifference makes sense. I am leaving it up to the definition of available indexes. And there are no known cases where it could matter anyway, unless you consider the === operator for tuples to be a counter example. And you need multiple conflicting unique indexes on the exact same attributes/expressions on attributes to begin with. Isn't that a highly esoteric thing to have to worry about? Perhaps to the extent that literally no one will ever have to care anyway? It's an oddball use-case, if ever I saw one. Note: the issue of caring about equality semantics across B-Tree opclasses of the same type, and the issue of naming unique indexes are separate issues, AFAICT. No one should confuse them. The only crossover is that the oddball use-case mentioned could use the named constraint thing as an escape hatch. As I've said, I think it's misguided to try to make unique indexes 100% an implementation detail. It's going to fall apart in edge cases, like the one with multiple unique indexes that I mentioned in my last e-mail. No one thinks of them that way, including users. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
