When ALTER TABLE rewrites a table, it reindexes, but the reindex does not
revalidate UNIQUE/EXCLUDE constraints. This behaves badly in cases like this,
neglecting to throw an error on the new UNIQUE violation:
CREATE TABLE t (c numeric UNIQUE);
INSERT INTO t VALUES (1.1),(1.2);
ALTER TABLE t ALTER c TYPE int;
The comment gave a reason for skipping the checks: it would cause deadlocks when
we rewrite a system catalog. So, this patch changes things to only skip the
check for system catalogs.
*** a/src/backend/catalog/index.c
--- b/src/backend/catalog/index.c
***************
*** 2544,2552 **** reindex_index(Oid indexId, bool skip_constraint_checks)
* catalog indexes while collecting the list.)
*
* We also skip rechecking uniqueness/exclusion constraint properties if
! * heap_rebuilt is true. This avoids likely deadlock conditions when doing
! * VACUUM FULL or CLUSTER on system catalogs. REINDEX should be used to
! * rebuild an index if constraint inconsistency is suspected.
*
* Returns true if any indexes were rebuilt. Note that a
* CommandCounterIncrement will occur after each index rebuild.
--- 2544,2554 ----
* catalog indexes while collecting the list.)
*
* We also skip rechecking uniqueness/exclusion constraint properties if
! * heap_rebuilt is true and the relation is a system catalog. This avoids
! * likely deadlock conditions when doing VACUUM FULL or CLUSTER. We trust
that
! * constraints will remain consistent across a user-issued ALTER TABLE
against a
! * system catalog. REINDEX should be used to rebuild an index if constraint
! * inconsistency is suspected.
*
* Returns true if any indexes were rebuilt. Note that a
* CommandCounterIncrement will occur after each index rebuild.
***************
*** 2629,2635 **** reindex_relation(Oid relid, bool toast_too, bool
heap_rebuilt)
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, heap_rebuilt);
CommandCounterIncrement();
--- 2631,2637 ----
if (is_pg_class)
RelationSetIndexList(rel, doneIndexes,
InvalidOid);
! reindex_index(indexOid, heap_rebuilt &&
IsSystemRelation(rel));
CommandCounterIncrement();
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 1774,1779 **** DEBUG: Rebuilding index "t_strarr_idx"
--- 1774,1781 ----
DEBUG: Rebuilding index "t_square_idx"
DEBUG: Rebuilding index "t_expr_idx"
DEBUG: Rebuilding index "t_touchy_f_idx"
+ ERROR: could not create unique index "t_touchy_f_idx"
+ DETAIL: Key (touchy_f(constraint1))=(100) is duplicated.
ALTER TABLE t ALTER constraint2 TYPE trickint;
-- noop-e
DEBUG: Rewriting table "t"
DEBUG: Rebuilding index "t_constraint4_key"
***************
*** 1792,1816 **** DEBUG: Rebuilding index "t_strarr_idx"
DEBUG: Rebuilding index "t_square_idx"
DEBUG: Rebuilding index "t_touchy_f_idx"
DEBUG: Rebuilding index "t_expr_idx"
! -- Temporary fixup until behavior of the previous two improves.
! ALTER TABLE t ALTER constraint1 TYPE int, ALTER constraint2 TYPE int;
! DEBUG: Rewriting table "t"
! DEBUG: Rebuilding index "t_constraint4_key"
! DEBUG: Rebuilding index "t_integral_key"
! DEBUG: Rebuilding index "t_rational_key"
! DEBUG: Rebuilding index "t_daytimetz_key"
! DEBUG: Rebuilding index "t_daytime_key"
! DEBUG: Rebuilding index "t_stamptz_key"
! DEBUG: Rebuilding index "t_stamp_key"
! DEBUG: Rebuilding index "t_timegap_key"
! DEBUG: Rebuilding index "t_bits_key"
! DEBUG: Rebuilding index "t_network_key"
! DEBUG: Rebuilding index "t_string_idx"
! DEBUG: Rebuilding index "t_string_idx1"
! DEBUG: Rebuilding index "t_strarr_idx"
! DEBUG: Rebuilding index "t_square_idx"
! DEBUG: Rebuilding index "t_touchy_f_idx"
! DEBUG: Rebuilding index "t_expr_idx"
-- Change a column with an outgoing foreign key constraint.
ALTER TABLE t ALTER constraint3 TYPE numeric(8,1); -- rewrite, FK error
DEBUG: Rewriting table "t"
--- 1794,1801 ----
DEBUG: Rebuilding index "t_square_idx"
DEBUG: Rebuilding index "t_touchy_f_idx"
DEBUG: Rebuilding index "t_expr_idx"
! ERROR: could not create unique index "t_expr_idx"
! DETAIL: Key ((1))=(1) is duplicated.
-- Change a column with an outgoing foreign key constraint.
ALTER TABLE t ALTER constraint3 TYPE numeric(8,1); -- rewrite, FK error
DEBUG: Rewriting table "t"
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 1246,1253 **** FROM pg_trigger WHERE tgrelid = 't'::regclass ORDER BY
tgname;
ALTER TABLE t ALTER constraint0 TYPE trickint;
-- verify-e
ALTER TABLE t ALTER constraint1 TYPE trickint;
-- noop-e
ALTER TABLE t ALTER constraint2 TYPE trickint;
-- noop-e
- -- Temporary fixup until behavior of the previous two improves.
- ALTER TABLE t ALTER constraint1 TYPE int, ALTER constraint2 TYPE int;
-- Change a column with an outgoing foreign key constraint.
ALTER TABLE t ALTER constraint3 TYPE numeric(8,1); -- rewrite, FK error
--- 1246,1251 ----
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
