Horton

at1-check-unique.patch

text/plain

Download
Filename: at1-check-unique.patch
Type: text/plain
Part: 0
Message: ALTER TYPE 1: recheck index-based constraints

Patch

Same data as JSON: GET /api/v1/attachments/:id/patch the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes. API reference →
Format: context
File+−
src/backend/catalog/index.c 6 0
src/test/regress/expected/alter_table.out 4 0
src/test/regress/sql/alter_table.sql 0 0 
*** 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 ----