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?
Attachments:
fix_messaging_unique_index_vs_constraint.patchapplication/octet-stream; name=fix_messaging_unique_index_vs_constraint.patchDownload+3-4
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:
Show quoted text
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