ON CONFLICT with constraint name doesn't work
Started by Nikolay Samokhvalovalmost 9 years ago12 messages
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.
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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".
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
From 5896a26f506767be87aaeec2f847eaccc4377e65 Mon Sep 17 00:00:00 2001
From: NikolayS <github2@samokhvalov.com>
Date: Thu, 16 Mar 2017 23:48:27 +0300
Subject: [PATCH] Fix message that wrongly uses index' name as contraint's name
---
src/backend/access/nbtree/nbtinsert.c | 2 +-
src/backend/po/ru.po | 4 ++--
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 6dca810..a4a582c 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -426,7 +426,7 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
ereport(ERROR,
(errcode(ERRCODE_UNIQUE_VIOLATION),
- errmsg("duplicate key value violates unique constraint \"%s\"",
+ errmsg("duplicate key value violates unique constraint associated with index \"%s\"",
RelationGetRelationName(rel)),
key_desc ? errdetail("Key %s already exists.",
key_desc) : 0,
diff --git a/src/backend/po/ru.po b/src/backend/po/ru.po
index 5bd8059..7b5549a 100644
--- a/src/backend/po/ru.po
+++ b/src/backend/po/ru.po
@@ -1049,8 +1049,8 @@ msgstr "\"%s\" - это не индекс"
#: access/nbtree/nbtinsert.c:428
#, c-format
-msgid "duplicate key value violates unique constraint \"%s\""
-msgstr "повторяющееся значение ключа нарушает ограничение уникальности \"%s\""
+msgid "duplicate key value violates unique constraint associated with index \"%s\""
+msgstr "повторяющееся значение ключа нарушает ограничение уникальности, связанное с индексом \"%s\""
#: access/nbtree/nbtinsert.c:430
#, c-format
--
2.1.4
Re: ON CONFLICT with constraint name doesn't work
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
Re: ON CONFLICT with constraint name doesn't work
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
Attachments:
fix_messaging_unique_index_vs_constraint_v2.patchapplication/octet-stream; name=fix_messaging_unique_index_vs_constraint_v2.patchDownload
From a543c925f7f411642946adbd9fc8c72cb3f43015 Mon Sep 17 00:00:00 2001
From: NikolayS <github2@samokhvalov.com>
Date: Fri, 17 Mar 2017 06:35:15 +0300
Subject: [PATCH] violates unique constraint --> violates unique index
---
contrib/citext/expected/citext.out | 6 +-
contrib/citext/expected/citext_1.out | 6 +-
contrib/postgres_fdw/expected/postgres_fdw.out | 2 +-
contrib/test_decoding/expected/replorigin.out | 2 +-
src/backend/access/nbtree/nbtinsert.c | 2 +-
.../expected/compat_informix-test_informix.stderr | 4 +-
.../expected/compat_informix-test_informix.stdout | 4 +-
src/test/isolation/expected/alter-table-3.out | 96 +++++++++++-----------
.../isolation/expected/read-write-unique-2.out | 2 +-
.../isolation/expected/read-write-unique-4.out | 4 +-
src/test/isolation/expected/read-write-unique.out | 2 +-
src/test/regress/expected/alter_table.out | 12 +--
src/test/regress/expected/arrays.out | 2 +-
src/test/regress/expected/create_index.out | 6 +-
src/test/regress/expected/create_table_like.out | 4 +-
src/test/regress/expected/insert_conflict.out | 4 +-
src/test/regress/expected/plpgsql.out | 4 +-
src/test/regress/expected/privileges.out | 4 +-
src/test/regress/expected/rowsecurity.out | 2 +-
src/test/regress/expected/transactions.out | 4 +-
src/test/regress/expected/uuid.out | 2 +-
src/test/regress/output/constraints.source | 16 ++--
src/test/regress/output/tablespace.source | 4 +-
23 files changed, 97 insertions(+), 97 deletions(-)
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
index bc5d92e..752469b 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -272,13 +272,13 @@ SELECT name, 'A' = name AS t FROM try where name = 'A';
-- expected failures on duplicate key
INSERT INTO try (name) VALUES ('a');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(a) already exists.
INSERT INTO try (name) VALUES ('A');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(A) already exists.
INSERT INTO try (name) VALUES ('aB');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(aB) already exists.
-- Make sure that citext_smaller() and citext_larger() work properly.
SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t;
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out
index 3d02d06..d55d2cd 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -272,13 +272,13 @@ SELECT name, 'A' = name AS t FROM try where name = 'A';
-- expected failures on duplicate key
INSERT INTO try (name) VALUES ('a');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(a) already exists.
INSERT INTO try (name) VALUES ('A');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(A) already exists.
INSERT INTO try (name) VALUES ('aB');
-ERROR: duplicate key value violates unique constraint "try_pkey"
+ERROR: duplicate key value violates unique index "try_pkey"
DETAIL: Key (name)=(aB) already exists.
-- Make sure that citext_smaller() and citext_larger() work properly.
SELECT citext_smaller( 'ab'::citext, 'ac'::citext ) = 'ab' AS t;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 059c5c3..a85382a 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -5386,7 +5386,7 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
-- Test errors thrown on remote side during update
ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
-ERROR: duplicate key value violates unique constraint "t1_pkey"
+ERROR: duplicate key value violates unique index "t1_pkey"
DETAIL: Key ("C 1")=(11) already exists.
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works
diff --git a/contrib/test_decoding/expected/replorigin.out b/contrib/test_decoding/expected/replorigin.out
index 76d4ea9..cae7135 100644
--- a/contrib/test_decoding/expected/replorigin.out
+++ b/contrib/test_decoding/expected/replorigin.out
@@ -10,7 +10,7 @@ SELECT pg_replication_origin_create('test_decoding: regression_slot');
-- ensure duplicate creations fail
SELECT pg_replication_origin_create('test_decoding: regression_slot');
-ERROR: duplicate key value violates unique constraint "pg_replication_origin_roname_index"
+ERROR: duplicate key value violates unique index "pg_replication_origin_roname_index"
DETAIL: Key (roname)=(test_decoding: regression_slot) already exists.
--ensure deletions work (once)
SELECT pg_replication_origin_create('test_decoding: temp');
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 6dca810..9e5090d 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -426,7 +426,7 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
ereport(ERROR,
(errcode(ERRCODE_UNIQUE_VIOLATION),
- errmsg("duplicate key value violates unique constraint \"%s\"",
+ errmsg("duplicate key value violates unique index \"%s\"",
RelationGetRelationName(rel)),
key_desc ? errdetail("Key %s already exists.",
key_desc) : 0,
diff --git a/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stderr b/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stderr
index 43d16b0..6538ac4 100644
--- a/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stderr
+++ b/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stderr
@@ -22,10 +22,10 @@
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 32: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_check_PQresult on line 32: bad response - ERROR: duplicate key value violates unique constraint "test_pkey"
+[NO_PID]: ecpg_check_PQresult on line 32: bad response - ERROR: duplicate key value violates unique index "test_pkey"
DETAIL: Key (i)=(7) already exists.
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: raising sqlstate 23505 (sqlcode -239): duplicate key value violates unique constraint "test_pkey" on line 32
+[NO_PID]: raising sqlstate 23505 (sqlcode -239): duplicate key value violates unique index "test_pkey" on line 32
[NO_PID]: sqlca: code: -239, state: 23505
[NO_PID]: ECPGtrans on line 34: action "rollback"; connection "ecpg1_regression"
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stdout b/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stdout
index 454fd18..56c488a 100644
--- a/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stdout
+++ b/src/interfaces/ecpg/test/expected/compat_informix-test_informix.stdout
@@ -1,5 +1,5 @@
-doSQLprint: Error: duplicate key value violates unique constraint "test_pkey" on line 32
-INSERT: -239=duplicate key value violates unique constraint "test_pkey" on line 32
+doSQLprint: Error: duplicate key value violates unique index "test_pkey" on line 32
+INSERT: -239=duplicate key value violates unique index "test_pkey" on line 32
doSQLprint: Error: more than one row returned by a subquery used as an expression on line 40
SELECT: 0=
0
diff --git a/src/test/isolation/expected/alter-table-3.out b/src/test/isolation/expected/alter-table-3.out
index b4f3b5a..24fd8c9 100644
--- a/src/test/isolation/expected/alter-table-3.out
+++ b/src/test/isolation/expected/alter-table-3.out
@@ -11,7 +11,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s1c s2a s1d s2b s2c s2d
@@ -25,7 +25,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s1c s2a s2b s1d s2c s2d
@@ -39,7 +39,7 @@ i
1
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s1c s2a s2b s2c s1d s2d
@@ -54,7 +54,7 @@ i
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s1c s1d s2b s2c s2d
@@ -68,7 +68,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s1c s2b s1d s2c s2d
@@ -82,7 +82,7 @@ i
1
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s1c s2b s2c s1d s2d
@@ -97,7 +97,7 @@ i
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s2b s1c s1d s2c s2d
@@ -111,7 +111,7 @@ i
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s2b s1c s2c s1d s2d
@@ -126,7 +126,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s1b s2a s2b s2c s1c s1d s2d
@@ -141,7 +141,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s1c s1d s2b s2c s2d
@@ -155,7 +155,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s1c s2b s1d s2c s2d
@@ -169,7 +169,7 @@ i
1
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s1c s2b s2c s1d s2d
@@ -184,7 +184,7 @@ i
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s2b s1c s1d s2c s2d
@@ -198,7 +198,7 @@ i
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s2b s1c s2c s1d s2d
@@ -213,7 +213,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s1b s2b s2c s1c s1d s2d
@@ -228,7 +228,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s2b s1b s1c s1d s2c s2d
@@ -242,7 +242,7 @@ step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s2b s1b s1c s2c s1d s2d
@@ -257,7 +257,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s2b s1b s2c s1c s1d s2d
@@ -272,7 +272,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s1a s2a s2b s2c s1b s1c s1d s2d
@@ -283,7 +283,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
@@ -297,7 +297,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2d: COMMIT;
@@ -311,7 +311,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s2d: COMMIT;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -325,7 +325,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -342,7 +342,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s1b s1c s2b s1d s2c s2d
@@ -356,7 +356,7 @@ i
1
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s1b s1c s2b s2c s1d s2d
@@ -371,7 +371,7 @@ i
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s1b s2b s1c s1d s2c s2d
@@ -385,7 +385,7 @@ i
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s1b s2b s1c s2c s1d s2d
@@ -400,7 +400,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s1b s2b s2c s1c s1d s2d
@@ -415,7 +415,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s2b s1b s1c s1d s2c s2d
@@ -429,7 +429,7 @@ step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s2b s1b s1c s2c s1d s2d
@@ -444,7 +444,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s2b s1b s2c s1c s1d s2d
@@ -459,7 +459,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s1a s2b s2c s1b s1c s1d s2d
@@ -470,7 +470,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
@@ -484,7 +484,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2d: COMMIT;
@@ -498,7 +498,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s2d: COMMIT;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -512,7 +512,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -529,7 +529,7 @@ step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s2b s1a s1b s1c s2c s1d s2d
@@ -544,7 +544,7 @@ step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s2b s1a s1b s2c s1c s1d s2d
@@ -559,7 +559,7 @@ step s2c: INSERT INTO a VALUES (0); <waiting ...>
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
step s2c: <... completed>
-error in steps s1d s2c: ERROR: duplicate key value violates unique constraint "a_pkey"
+error in steps s1d s2c: ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
starting permutation: s2a s2b s1a s2c s1b s1c s1d s2d
@@ -570,7 +570,7 @@ i
1
step s1a: BEGIN;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s1d: COMMIT;
@@ -584,7 +584,7 @@ i
1
step s1a: BEGIN;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
step s2d: COMMIT;
@@ -598,7 +598,7 @@ i
1
step s1a: BEGIN;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s2d: COMMIT;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -612,7 +612,7 @@ i
1
step s1a: BEGIN;
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -625,7 +625,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1a: BEGIN;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -639,7 +639,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1a: BEGIN;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s1c: ALTER TABLE a ENABLE TRIGGER t;
@@ -653,7 +653,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1a: BEGIN;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
step s2d: COMMIT;
@@ -667,7 +667,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s1a: BEGIN;
step s2d: COMMIT;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
@@ -681,7 +681,7 @@ i
1
step s2c: INSERT INTO a VALUES (0);
-ERROR: duplicate key value violates unique constraint "a_pkey"
+ERROR: duplicate key value violates unique index "a_pkey"
step s2d: COMMIT;
step s1a: BEGIN;
step s1b: ALTER TABLE a DISABLE TRIGGER t;
diff --git a/src/test/isolation/expected/read-write-unique-2.out b/src/test/isolation/expected/read-write-unique-2.out
index 5e27f0a..f4cdad6 100644
--- a/src/test/isolation/expected/read-write-unique-2.out
+++ b/src/test/isolation/expected/read-write-unique-2.out
@@ -25,5 +25,5 @@ i
42
step w2: INSERT INTO test VALUES (42);
-ERROR: duplicate key value violates unique constraint "test_pkey"
+ERROR: duplicate key value violates unique index "test_pkey"
step c2: COMMIT;
diff --git a/src/test/isolation/expected/read-write-unique-4.out b/src/test/isolation/expected/read-write-unique-4.out
index 64ff157..9c86603 100644
--- a/src/test/isolation/expected/read-write-unique-4.out
+++ b/src/test/isolation/expected/read-write-unique-4.out
@@ -25,7 +25,7 @@ step w1: INSERT INTO invoice VALUES (2016, 3);
step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
step c1: COMMIT;
step w2: <... completed>
-error in steps c1 w2: ERROR: duplicate key value violates unique constraint "invoice_pkey"
+error in steps c1 w2: ERROR: duplicate key value violates unique index "invoice_pkey"
step c2: COMMIT;
starting permutation: r2 w1 w2 c1 c2
@@ -37,5 +37,5 @@ step w1: INSERT INTO invoice VALUES (2016, 3);
step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
step c1: COMMIT;
step w2: <... completed>
-error in steps c1 w2: ERROR: duplicate key value violates unique constraint "invoice_pkey"
+error in steps c1 w2: ERROR: duplicate key value violates unique index "invoice_pkey"
step c2: COMMIT;
diff --git a/src/test/isolation/expected/read-write-unique.out b/src/test/isolation/expected/read-write-unique.out
index fb32ec3..c0d94de 100644
--- a/src/test/isolation/expected/read-write-unique.out
+++ b/src/test/isolation/expected/read-write-unique.out
@@ -25,5 +25,5 @@ i
42
step w2: INSERT INTO test VALUES (42);
-ERROR: duplicate key value violates unique constraint "test_pkey"
+ERROR: duplicate key value violates unique index "test_pkey"
step c2: COMMIT;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 2227f2d..dcfd301 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -736,7 +736,7 @@ alter table atacc1 add constraint atacc_test1 unique (test);
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: duplicate key value violates unique constraint "atacc_test1"
+ERROR: duplicate key value violates unique index "atacc_test1"
DETAIL: Key (test)=(2) already exists.
-- should succeed
insert into atacc1 (test) values (4);
@@ -773,7 +773,7 @@ alter table atacc1 add constraint atacc_test1 unique (test, test2);
insert into atacc1 (test,test2) values (4,4);
-- should fail
insert into atacc1 (test,test2) values (4,4);
-ERROR: duplicate key value violates unique constraint "atacc_test1"
+ERROR: duplicate key value violates unique index "atacc_test1"
DETAIL: Key (test, test2)=(4, 4) already exists.
-- should all succeed
insert into atacc1 (test,test2) values (4,5);
@@ -786,7 +786,7 @@ alter table atacc1 add unique (test2);
-- should fail for @@ second one @@
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
-ERROR: duplicate key value violates unique constraint "atacc1_test_key"
+ERROR: duplicate key value violates unique index "atacc1_test_key"
DETAIL: Key (test)=(3) already exists.
drop table atacc1;
-- test primary key constraint adding
@@ -797,7 +797,7 @@ alter table atacc1 add constraint atacc_test1 primary key (test);
insert into atacc1 (test) values (2);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: duplicate key value violates unique constraint "atacc_test1"
+ERROR: duplicate key value violates unique index "atacc_test1"
DETAIL: Key (test)=(2) already exists.
-- should succeed
insert into atacc1 (test) values (4);
@@ -861,7 +861,7 @@ ERROR: multiple primary keys for table "atacc1" are not allowed
insert into atacc1 (test,test2) values (4,4);
-- should fail
insert into atacc1 (test,test2) values (4,4);
-ERROR: duplicate key value violates unique constraint "atacc_test1"
+ERROR: duplicate key value violates unique index "atacc_test1"
DETAIL: Key (test, test2)=(4, 4) already exists.
insert into atacc1 (test,test2) values (NULL,3);
ERROR: null value in column "test" violates not-null constraint
@@ -882,7 +882,7 @@ create table atacc1 (test int, test2 int, primary key(test));
-- only first should succeed
insert into atacc1 (test2, test) values (3, 3);
insert into atacc1 (test2, test) values (2, 3);
-ERROR: duplicate key value violates unique constraint "atacc1_pkey"
+ERROR: duplicate key value violates unique index "atacc1_pkey"
DETAIL: Key (test)=(3) already exists.
insert into atacc1 (test2, test) values (1, NULL);
ERROR: null value in column "test" violates not-null constraint
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index c730563..77237a2 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1301,7 +1301,7 @@ insert into arr_tbl values ('{1,2,3}');
insert into arr_tbl values ('{1,2}');
-- failure expected:
insert into arr_tbl values ('{1,2,3}');
-ERROR: duplicate key value violates unique constraint "arr_tbl_f1_key"
+ERROR: duplicate key value violates unique index "arr_tbl_f1_key"
DETAIL: Key (f1)=({1,2,3}) already exists.
insert into arr_tbl values ('{2,3,4}');
insert into arr_tbl values ('{1,5,3}');
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 26cd059..845a50f 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2375,7 +2375,7 @@ INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
-ERROR: duplicate key value violates unique constraint "func_index_index"
+ERROR: duplicate key value violates unique index "func_index_index"
DETAIL: Key (textcat(f1, f2))=(ABCDEF) already exists.
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
@@ -2390,7 +2390,7 @@ INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
-ERROR: duplicate key value violates unique constraint "func_index_index"
+ERROR: duplicate key value violates unique index "func_index_index"
DETAIL: Key ((f1 || f2))=(ABCDEF) already exists.
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
@@ -2419,7 +2419,7 @@ CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
NOTICE: relation "concur_index2" already exists, skipping
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x');
-ERROR: duplicate key value violates unique constraint "concur_index2"
+ERROR: duplicate key value violates unique index "concur_index2"
DETAIL: Key (f1)=(b) already exists.
-- check if constraint is enforced properly at build time
CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index a25b221..9316f9e 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -69,7 +69,7 @@ DROP TABLE inhg;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
-ERROR: duplicate key value violates unique constraint "inhg_pkey"
+ERROR: duplicate key value violates unique index "inhg_pkey"
DETAIL: Key (xx)=(10) already exists.
DROP TABLE inhg;
/* Multiple primary keys creation should fail */
@@ -82,7 +82,7 @@ CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
-ERROR: duplicate key value violates unique constraint "inhg_x_key"
+ERROR: duplicate key value violates unique index "inhg_x_key"
DETAIL: Key (x)=(15) already exists.
DROP TABLE inhg;
DROP TABLE inhz;
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 8d005fd..8cd8800 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -360,7 +360,7 @@ insert into insertconflicttest values (26, 'Fig') on conflict (key) do update se
-- fails, since UPDATE is to row with key value 26, and we're updating "fruit"
-- to a value that happens to exist in another row ('peach'):
insert into insertconflicttest values (26, 'Peach') on conflict (key) do update set fruit = excluded.fruit;
-ERROR: duplicate key value violates unique constraint "fruit_index"
+ERROR: duplicate key value violates unique index "fruit_index"
DETAIL: Key (fruit)=(Peach) already exists.
-- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit"
-- arbitrates that statement updates existing "Fig" row:
@@ -733,7 +733,7 @@ create table twoconstraints (f1 int unique, f2 box,
exclude using gist(f2 with &&));
insert into twoconstraints values(1, '((0,0),(1,1))');
insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1
-ERROR: duplicate key value violates unique constraint "twoconstraints_f1_key"
+ERROR: duplicate key value violates unique index "twoconstraints_f1_key"
DETAIL: Key (f1)=(1) already exists.
insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2
ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl"
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 04848c1..bc6418d 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -1514,7 +1514,7 @@ select * from PField_v1 where pfname = 'PF0_2' order by slotname;
-- Finally we want errors
--
insert into PField values ('PF1_1', 'should fail due to unique index');
-ERROR: duplicate key value violates unique constraint "pfield_name"
+ERROR: duplicate key value violates unique index "pfield_name"
DETAIL: Key (name)=(PF1_1) already exists.
update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
ERROR: WS.not.there does not exist
@@ -1533,7 +1533,7 @@ ERROR: illegal slotlink beginning with XX
CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 77 at RAISE
PL/pgSQL function tg_slotlink_a() line 17 at assignment
insert into HSlot values ('HS', 'base.hub1', 1, '');
-ERROR: duplicate key value violates unique constraint "hslot_name"
+ERROR: duplicate key value violates unique index "hslot_name"
DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.
insert into HSlot values ('HS', 'base.hub1', 20, '');
ERROR: no manual manipulation of HSlot
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 7206750..1c5cbce 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -422,9 +422,9 @@ INSERT INTO t1 VALUES (2, 2, 2);
INSERT INTO t1 VALUES (3, 1, 3);
SET SESSION AUTHORIZATION regress_user2;
INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown
-ERROR: duplicate key value violates unique constraint "t1_pkey"
+ERROR: duplicate key value violates unique index "t1_pkey"
UPDATE t1 SET c2 = 1; -- fail, but row not shown
-ERROR: duplicate key value violates unique constraint "t1_pkey"
+ERROR: duplicate key value violates unique index "t1_pkey"
INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted
ERROR: null value in column "c1" violates not-null constraint
DETAIL: Failing row contains (c1, c2) = (null, null).
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 7bf2936..508cdc5 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -476,7 +476,7 @@ INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
SET SESSION AUTHORIZATION regress_rls_bob;
INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
-ERROR: duplicate key value violates unique constraint "document_pkey"
+ERROR: duplicate key value violates unique index "document_pkey"
SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
did | cid | dlevel | dauthor | dtitle
-----+-----+--------+---------+--------
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
index d9b702d..16d9a67 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -540,13 +540,13 @@ BEGIN;
CREATE TABLE koju (a INT UNIQUE);
INSERT INTO koju VALUES (1);
INSERT INTO koju VALUES (1);
-ERROR: duplicate key value violates unique constraint "koju_a_key"
+ERROR: duplicate key value violates unique index "koju_a_key"
DETAIL: Key (a)=(1) already exists.
rollback to x;
CREATE TABLE koju (a INT UNIQUE);
INSERT INTO koju VALUES (1);
INSERT INTO koju VALUES (1);
-ERROR: duplicate key value violates unique constraint "koju_a_key"
+ERROR: duplicate key value violates unique index "koju_a_key"
DETAIL: Key (a)=(1) already exists.
ROLLBACK;
DROP TABLE foo;
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index db66dc7..3c8ea58 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -118,7 +118,7 @@ CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field);
CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field);
-- should fail
INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');
-ERROR: duplicate key value violates unique constraint "guid1_unique_btree"
+ERROR: duplicate key value violates unique index "guid1_unique_btree"
DETAIL: Key (guid_field)=(11111111-1111-1111-1111-111111111111) already exists.
-- check to see whether the new indexes are actually there
SELECT count(*) FROM pg_class WHERE relkind='i' AND relname LIKE 'guid%';
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
index e6d3174..ccf4bcf 100644
--- a/src/test/regress/output/constraints.source
+++ b/src/test/regress/output/constraints.source
@@ -370,7 +370,7 @@ CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
INSERT INTO PRIMARY_TBL VALUES (1, 'one');
INSERT INTO PRIMARY_TBL VALUES (2, 'two');
INSERT INTO PRIMARY_TBL VALUES (1, 'three');
-ERROR: duplicate key value violates unique constraint "primary_tbl_pkey"
+ERROR: duplicate key value violates unique index "primary_tbl_pkey"
DETAIL: Key (i)=(1) already exists.
INSERT INTO PRIMARY_TBL VALUES (4, 'three');
INSERT INTO PRIMARY_TBL VALUES (5, 'one');
@@ -415,7 +415,7 @@ CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
INSERT INTO UNIQUE_TBL VALUES (2, 'two');
INSERT INTO UNIQUE_TBL VALUES (1, 'three');
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(1) already exists.
INSERT INTO UNIQUE_TBL VALUES (4, 'four');
INSERT INTO UNIQUE_TBL VALUES (5, 'one');
@@ -446,7 +446,7 @@ INSERT INTO UNIQUE_TBL VALUES (1, 'one');
INSERT INTO UNIQUE_TBL VALUES (2, 'two');
INSERT INTO UNIQUE_TBL VALUES (1, 'three');
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_t_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_t_key"
DETAIL: Key (i, t)=(1, one) already exists.
INSERT INTO UNIQUE_TBL VALUES (5, 'one');
INSERT INTO UNIQUE_TBL (t) VALUES ('six');
@@ -473,7 +473,7 @@ INSERT INTO unique_tbl VALUES (4, 'five');
BEGIN;
-- default is immediate so this should fail right away
UPDATE unique_tbl SET i = 1 WHERE i = 0;
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(1) already exists.
ROLLBACK;
-- check is done at end of statement, so this should succeed
@@ -530,13 +530,13 @@ SELECT * FROM unique_tbl;
BEGIN;
INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
COMMIT; -- should fail
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
-- make constraint check immediate
BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
COMMIT;
-- forced check when SET CONSTRAINTS is called
@@ -544,7 +544,7 @@ BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
SET CONSTRAINTS ALL IMMEDIATE; -- should fail
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
COMMIT;
-- test a HOT update that invalidates the conflicting tuple.
@@ -553,7 +553,7 @@ BEGIN;
INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three';
COMMIT; -- should fail
-ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+ERROR: duplicate key value violates unique index "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
SELECT * FROM unique_tbl;
i | t
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index aaedf5f..4dc8da8 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -170,7 +170,7 @@ DROP TABLE testschema.test_default_tab;
CREATE TABLE testschema.test_tab(id int) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_tab VALUES (1);
SET default_tablespace TO regress_tblspace;
-ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (id);
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_u(nique UNIQUE (id);
SET default_tablespace TO '';
ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_pkey PRIMARY KEY (id);
\d testschema.test_tab_unique
@@ -202,7 +202,7 @@ ALTER TABLE testschema.atable SET TABLESPACE regress_tblspace;
ALTER INDEX testschema.anindex SET TABLESPACE regress_tblspace;
INSERT INTO testschema.atable VALUES(3); -- ok
INSERT INTO testschema.atable VALUES(1); -- fail (checks index)
-ERROR: duplicate key value violates unique constraint "anindex"
+ERROR: duplicate key value violates unique index "anindex"
DETAIL: Key (column1)=(1) already exists.
SELECT COUNT(*) FROM testschema.atable; -- checks heap
count
--
2.1.4
fix_messaging_unique_index_vs_constraint_DOC.patchapplication/octet-stream; name=fix_messaging_unique_index_vs_constraint_DOC.patchDownload
From 1d8a5f3bc35203642531d51e32d49eab7138781c Mon Sep 17 00:00:00 2001
From: NikolayS <github2@samokhvalov.com>
Date: Fri, 17 Mar 2017 06:55:06 +0300
Subject: [PATCH] violates unique constraint --> violates unique index, DOC
---
doc/src/sgml/ref/insert.sgml | 12 ++++++++++--
1 file changed, 10 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 521216b..b43ab23 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -447,7 +447,11 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<para>
Explicitly specifies an arbiter
<emphasis>constraint</emphasis> by name, rather than inferring
- a constraint or index.
+ a constraint or index. Note that <literal>CREATE UNIQUE INDEX</literal>
+ creates only a unique index, not a unique constraint.
+ <literal>ON CONFLICT ON CONSTRAINT</literal> for unique constraints
+ can be used only when such a constraint is defined explicity
+ using <literal>ALTER TABLE ... ADD UNIQUE CONSTRAINT</literal>.
</para>
</listitem>
</varlistentry>
@@ -492,7 +496,11 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
correctly when the underlying index is replaced by another more
or less equivalent index in an overlapping way, for example when
using <literal>CREATE UNIQUE INDEX ... CONCURRENTLY</literal>
- before dropping the index being replaced.
+ before dropping the index being replaced (moreover,
+ <literal>CREATE UNIQUE INDEX</literal> defines only a unique
+ index, not a unique constraint, and <literal>ON CONFLICT ON
+ CONSTRAINT</literal> cannot be used unless the corresponding
+ unique constraint is defined explicitly).
</para>
</tip>
--
2.1.4
Re: ON CONFLICT with constraint name doesn't work
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