On Thu, Mar 16, 2017 at 1:59 PM, Andres Freund <andres@anarazel.de> wrote:
I don't think that's an appropriate fix. ISTM we should say 'violates unique index' when it's just an index and 'violates unique constraint' when the index is backing a constraint.
To me, it now seems to be correct as well.
From what I see experimenting with unique indexes/constraints and looking to "pg_constraint" and "pg_indexes" catalogs:
a) if there is a unique constraint created by user, there is always the corresponding unique index defined, with the same name; and renaming of the index leads to implicit renaming of the constraint;
b) in contrast, creation of a unique index does not automatically lead to creation of the corresponding unique constraint;
c) any primary key is also a unique index by definition (in Postgres context, it's not a "unique constraint", it's a "unique index").
So violation of uniqueness is always a violation of a unique index, in all three cases. However, case (b) is very tricky and I suspect that many users will be consused -- just like I was today. Anyway, the proposed patches makes messaging and docs closer to the current implementation, minimizing the possible confusion.
Also, I assume that in the future, there is a possibility to distinguish cases "violates unique constraint", "violates primary key" and "violates unique index" – as I know, in Oracle, for example, you can have a *deferrable* unique constraint based on non-unique, regular index...
Anyway, attached are 2 separate patches:
1) version 2 of patch fixing the message, including regression tests;
