Обсуждение: [BUGS] ON CONFLICT with constraint name doesn't work
Hi,
I'm trying to explicitly use the constraint name of my UNIQUE INDEX but it doesn't work (Postgres 9.6.2):
[local]:5432 nikolay@test=# create table constr(id serial, msg text);
CREATE TABLE
[local]:5432 nikolay@test=# create unique index i_constr_msg on constr using btree(md5(msg));
CREATE INDEX
[local]:5432 nikolay@test=# insert into constr (msg) values ('hoho') on conflict on constraint i_constr_msg do nothing;
ERROR: constraint "i_constr_msg" for table "constr" does not exist
[local]:5432 nikolay@test=# \d constr
Table "public.constr"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('constr_id_seq'::regclass)
msg | text |
Indexes:
"i_constr_msg" UNIQUE, btree (md5(msg))
This works:
[local]:5432 nikolay@test=# insert into constr (msg) values ('hoho') on conflict (md5(msg)) do nothing;
INSERT 0 1
I don't see anything in the current docs https://www.postgresql.org/docs/9.6/static/sql-insert.html saying that I cannot use the unique index' name here. So it definitely looks like a bug.
Hi, On 2017-03-16 12:34:49 -0700, Nikolay Samokhvalov wrote: > I'm trying to explicitly use the constraint name of my UNIQUE INDEX but it > doesn't work (Postgres 9.6.2): > > [local]:5432 nikolay@test=# create table constr(id serial, msg text); > CREATE TABLE > > [local]:5432 nikolay@test=# create unique index i_constr_msg on constr > using btree(md5(msg)); > CREATE INDEX A unique index isn't exactly the same as a unique constraint - you really need to create a constraint. - Andres -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 03/16/2017 09:34 PM, Nikolay Samokhvalov wrote: > I don't see anything in the current docs > https://www.postgresql.org/docs/9.6/static/sql-insert.html saying that I > cannot use the unique index' name here. So it definitely looks like a bug. This is by design. The docs on conflict_target says: "Specifies which conflicts ON CONFLICT takes the alternative action on by choosing arbiter indexes. Either performs unique index inference, or names a *constraint* explicitly." (emphasis mine) As it says, you can name a constraint explicitly. A unique index is not a constraint. We debated this for a long time when the ON CONFLICT feature was being developed. In the end, we settled on this behavior, on the grounds that a constraint is a logical concept, while an index is a physical implementation detail. Note that the SQL standard also doesn't say anything about indexes, but constraints are in the standard. - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Mar 16, 2017 at 12:42 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote: > We debated this for a long time when the ON CONFLICT feature was being > developed. In the end, we settled on this behavior, on the grounds that a > constraint is a logical concept, while an index is a physical implementation > detail. Note that the SQL standard also doesn't say anything about indexes, > but constraints are in the standard. Right. Besides, you really are only supposed to use the ON CONSTRAINT syntax when inference won't work, as an escape hatch. This doesn't look like an example of where inference won't work. That's limited to ON CONFLICT DO NOTHING with exclusion constraints, which is fairly limited. -- Peter Geoghegan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 2017-03-16 12:44:23 -0700, Peter Geoghegan wrote: > On Thu, Mar 16, 2017 at 12:42 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote: > > We debated this for a long time when the ON CONFLICT feature was being > > developed. In the end, we settled on this behavior, on the grounds that a > > constraint is a logical concept, while an index is a physical implementation > > detail. Note that the SQL standard also doesn't say anything about indexes, > > but constraints are in the standard. > > Right. Besides, you really are only supposed to use the ON CONSTRAINT > syntax when inference won't work, as an escape hatch. This doesn't > look like an example of where inference won't work. That's limited to > ON CONFLICT DO NOTHING with exclusion constraints, which is fairly > limited. FWIW, I never was completely on board with this design goal, and I think we should have (and still should) support using indexes directly. - Andres -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Mar 16, 2017 at 12:49 PM, Andres Freund <andres@anarazel.de> wrote: > FWIW, I never was completely on board with this design goal, and I think > we should have (and still should) support using indexes directly. FWIW I agree that we probably should have exposed indexes as a target that can be named directly, while still generally discouraging the practice. But, it hardly matters now. -- Peter Geoghegan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Mar 16, 2017 at 12:40 PM, Andres Freund <andres@anarazel.de> wrote:
A unique index isn't exactly the same as a unique constraint - you
really need to create a constraint.
Then we probably need to fix this message?
[local]:5432 nikolay@test=# insert into constr (msg) values ('hohoho') ;
INSERT 0 1
[local]:5432 nikolay@test=# insert into constr (msg) values ('hohoho') ;
ERROR: duplicate key value violates unique constraint "i_constr_msg"
DETAIL: Key (md5(msg))=(8b0dc2e34844337434b8475108a490ab) already exists.
-- it tells us explicitly, that we have a *constraint* named "i_constr_msg".
On 2017-03-16 13:08:53 -0700, Nikolay Samokhvalov wrote:
> On Thu, Mar 16, 2017 at 12:40 PM, Andres Freund <andres@anarazel.de> wrote:
> >
> > A unique index isn't exactly the same as a unique constraint - you
> > really need to create a constraint.
>
>
> Then we probably need to fix this message?
>
> [local]:5432 nikolay@test=# insert into constr (msg) values ('hohoho') ;
> INSERT 0 1
>
> [local]:5432 nikolay@test=# insert into constr (msg) values ('hohoho') ;
> ERROR: duplicate key value violates unique constraint "i_constr_msg"
> DETAIL: Key (md5(msg))=(8b0dc2e34844337434b8475108a490ab) already exists.
>
> -- it tells us explicitly, that we have a *constraint* named "i_constr_msg".
Seems like a good idea to improve that message. I wouldn't vote for
backpatching it, however. Could you propose a patch for that?
Greetings,
Andres Freund
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Mar 16, 2017 at 1:23 PM, Andres Freund <andres@anarazel.de> wrote:
Seems like a good idea to improve that message. I wouldn't vote for
backpatching it, however. Could you propose a patch for that?
OK. Here it is.
But:
1) what's next with all the i18n stuff? I've changed RU version as well, but unfortunately I don't the other languages.
2) it will definitely break many regression tests, should I patch them as well?
Also, documentation explaining ON CONFLICT might be still not clear, at least for some readers. Do you want me to propose a patch for that as well?
Вложения
On 2017-03-16 13:56:27 -0700, Nikolay Samokhvalov wrote: > On Thu, Mar 16, 2017 at 1:23 PM, Andres Freund <andres@anarazel.de> wrote: > > > > Seems like a good idea to improve that message. I wouldn't vote for > > backpatching it, however. Could you propose a patch for that? > > > > OK. Here it is. 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. > But: > 1) what's next with all the i18n stuff? I've changed RU version as well, > but unfortunately I don't the other languages. You don't need to patch any (including RU). They're maintained separately and the in-core stuff is periodically refreshed by the translators. > 2) it will definitely break many regression tests, should I patch them as > well? Yes, after above adaption, that'll probably reduce the size of the diff. > Also, documentation explaining ON CONFLICT might be still not clear, at > least for some readers. Do you want me to propose a patch for that as well? Please feel free to give it a try. Greetings, Andres Freund -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
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;
2) proposed change to the documentation https://www.postgresql.org/docs/current/static/sql-insert.html
Вложения
This is a kindly reminder, that this problem (message about "constraint" violation, while there is no such a constraint defined, just an index) is still unresolved.
Let's fix that naming?
Patch is attached in the previous message (posted to -bugs list)
On Thu, Mar 16, 2017 at 9:15 PM, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
Anyway, attached are 2 separate patches:1) version 2 of patch fixing the message, including regression tests;2) proposed change to the documentation https://www.postgresql.org/docs/current/static/sql- insert.html