search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30k stars 3.79k forks source link

sql: incorrect error message for constraint with duplicate name #129596

Open cockroach-teamcity opened 1 month ago

cockroach-teamcity commented 1 month ago

roachtest.pg_regress failed with artifacts on master @ a8f64695f5d025a3347c27ed03ee0e36fbdaacd3:

++ERROR:  relation "articles" (1530): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  DROP VIEW fdv4;
@@ -172389,7 +172301,7 @@
  (0 rows)

  ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
-+ERROR:  relation "articles" (1527): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "articles" (1530): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  EXECUTE foo;  -- fail
@@ -218090,7 +218002,7 @@
 -ERROR:  column "test" is in a primary key
 +ERROR:  column "test" is in a primary index
  alter table atacc1 drop constraint "atacc1_pkey";
-+ERROR:  relation "atacc1" (1890): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "atacc1" (1893): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  alter table atacc1 alter column test drop not null;
@@ -221642,13 +221554,13 @@
 +ERROR:  index with name "tt9_c_key" already exists
  ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c);  -- fail, dup name
 -ERROR:  constraint "foo" for relation "tt9" already exists
-+ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1984): duplicate constraint name: "foo"
++ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1987): duplicate constraint name: "foo"
  ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5);  -- fail, dup name
 -ERROR:  constraint "tt9_c_key" for relation "tt9" already exists
-+ERROR:  error executing StatementPhase stage 1 of 1 with 2 MutationType ops: relation "tt9" (1984): duplicate constraint name: "tt9_c_key"
++ERROR:  error executing StatementPhase stage 1 of 1 with 2 MutationType ops: relation "tt9" (1987): duplicate constraint name: "tt9_c_key"
  ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
  ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
-+ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1984): duplicate constraint name: "tt9_c_key2"
++ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1987): duplicate constraint name: "tt9_c_key2"
  \d tt9
 -                Table "public.tt9"
 - Column |  Type   | Collation | Nullable | Default 
@@ -221826,7 +221738,7 @@
  DELETE FROM old_system_table WHERE othercol = 'somedata';
  TRUNCATE old_system_table;
  ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
-+ERROR:  relation "old_system_table" (1987): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "old_system_table" (1990): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  ALTER TABLE old_system_table DROP COLUMN othercol;
If the diff is expected, copy regression.diffs from the test artifacts to pkg/cmd/roachtest/testdata/regression.diffs
test artifacts and logs in: /artifacts/pg_regress/run_1

Parameters:

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-41608

cockroach-teamcity commented 1 month ago

roachtest.pg_regress failed with artifacts on master @ a8f64695f5d025a3347c27ed03ee0e36fbdaacd3:

++ERROR:  relation "articles" (1530): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  DROP VIEW fdv4;
@@ -172389,7 +172301,7 @@
  (0 rows)

  ALTER TABLE articles DROP CONSTRAINT articles_pkey RESTRICT;
-+ERROR:  relation "articles" (1527): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "articles" (1530): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  EXECUTE foo;  -- fail
@@ -218090,7 +218002,7 @@
 -ERROR:  column "test" is in a primary key
 +ERROR:  column "test" is in a primary index
  alter table atacc1 drop constraint "atacc1_pkey";
-+ERROR:  relation "atacc1" (1890): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "atacc1" (1893): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  alter table atacc1 alter column test drop not null;
@@ -221642,13 +221554,13 @@
 +ERROR:  index with name "tt9_c_key" already exists
  ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c);  -- fail, dup name
 -ERROR:  constraint "foo" for relation "tt9" already exists
-+ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1984): duplicate constraint name: "foo"
++ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1987): duplicate constraint name: "foo"
  ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5);  -- fail, dup name
 -ERROR:  constraint "tt9_c_key" for relation "tt9" already exists
-+ERROR:  error executing StatementPhase stage 1 of 1 with 2 MutationType ops: relation "tt9" (1984): duplicate constraint name: "tt9_c_key"
++ERROR:  error executing StatementPhase stage 1 of 1 with 2 MutationType ops: relation "tt9" (1987): duplicate constraint name: "tt9_c_key"
  ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
  ALTER TABLE tt9 ADD UNIQUE(c);  -- picks nonconflicting name
-+ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1984): duplicate constraint name: "tt9_c_key2"
++ERROR:  error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "tt9" (1987): duplicate constraint name: "tt9_c_key2"
  \d tt9
 -                Table "public.tt9"
 - Column |  Type   | Collation | Nullable | Default 
@@ -221826,7 +221738,7 @@
  DELETE FROM old_system_table WHERE othercol = 'somedata';
  TRUNCATE old_system_table;
  ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
-+ERROR:  relation "old_system_table" (1987): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
++ERROR:  relation "old_system_table" (1990): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction
 +HINT:  You have attempted to use a feature that is not yet implemented.
 +See: https://go.crdb.dev/issue-v/48026/dev
  ALTER TABLE old_system_table DROP COLUMN othercol;
If the diff is expected, copy regression.diffs from the test artifacts to pkg/cmd/roachtest/testdata/regression.diffs
test artifacts and logs in: /artifacts/pg_regress/run_1

Parameters:

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

DrewKimball commented 1 month ago

We shouldn't be printing this non user-friendly error for a duplicate constraint name. Repro:

root@localhost:26257/defaultdb> create table xy (x int, y int);
CREATE TABLE

Time: 18ms total (execution 18ms / network 0ms)

root@localhost:26257/defaultdb> alter table xy add constraint foo check (x > 5);
ALTER TABLE

Time: 186ms total (execution 186ms / network 0ms)

root@localhost:26257/defaultdb> alter table xy add constraint foo unique (x);
ERROR: error executing StatementPhase stage 1 of 1 with 7 MutationType ops: relation "xy" (115): duplicate constraint name: "foo"
SQLSTATE: 42710