Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit00a40e3

Browse files
alvherretenderwg
andcommitted
Fix ALTER TABLE DETACH for inconsistent indexes
When a partitioned table has an index that doesn't support a constraint,but a partition has an equivalent index that does, then a DETACHoperation would misbehave: a crash in assertion-enabled systems (becausewe fail to find the constraint in the parent that we expect to), or abroken coninhcount value (-1) in production systems (because we blindlybelieve that we've successfully detached the parent).While we should reject an ATTACH of a partition with such an index, wehave failed to do so in existing releases, so adding an error in stablereleases might break the (unlikely) existing applications that rely onthis behavior. At this point I don't even want to reject them inmaster, because it'd break pg_upgrade if such databases exist, and therewould be no easy way to fix existing databases without expensive indexrebuilds.(Later on we could add ALTER TABLE ... ADD CONSTRAINT USING INDEX topartitioned tables, which would allow the user to fix such patterns. Atthat point we could add more restrictions to prevent the problem fromits root.)Also, add a test case that leaves one table in this condition, so thatwe can verify that pg_upgrade continues to work if we later decide tochange the policy on the master branch.Backpatch to all supported branches.Co-authored-by: Tender Wang <tndrwang@gmail.com>Reported-by: Alexander Lakhin <exclusion@gmail.com>Reviewed-by: Tender Wang <tndrwang@gmail.com>Reviewed-by: Michael Paquier <michael@paquier.xyz>Discussion:https://postgr.es/m/18500-62948b6fe5522f56@postgresql.org
1 parent2f3304c commit00a40e3

File tree

3 files changed

+95
-4
lines changed

3 files changed

+95
-4
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18761,22 +18761,31 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1876118761
foreach(cell, indexes)
1876218762
{
1876318763
Oididxid = lfirst_oid(cell);
18764+
Oidparentidx;
1876418765
Relationidx;
1876518766
OidconstrOid;
18767+
OidparentConstrOid;
1876618768

1876718769
if (!has_superclass(idxid))
1876818770
continue;
1876918771

18770-
Assert((IndexGetRelation(get_partition_parent(idxid, false), false) ==
18771-
RelationGetRelid(rel)));
18772+
parentidx =get_partition_parent(idxid, false);
18773+
Assert((IndexGetRelation(parentidx, false) ==RelationGetRelid(rel)));
1877218774

1877318775
idx = index_open(idxid, AccessExclusiveLock);
1877418776
IndexSetParentIndex(idx, InvalidOid);
1877518777

18776-
/* If there's a constraint associated with the index, detach it too */
18778+
/*
18779+
* If there's a constraint associated with the index, detach it too.
18780+
* Careful: it is possible for a constraint index in a partition to be
18781+
* the child of a non-constraint index, so verify whether the parent
18782+
* index does actually have a constraint.
18783+
*/
1877718784
constrOid = get_relation_idx_constraint_oid(RelationGetRelid(partRel),
1877818785
idxid);
18779-
if (OidIsValid(constrOid))
18786+
parentConstrOid = get_relation_idx_constraint_oid(RelationGetRelid(rel),
18787+
parentidx);
18788+
if (OidIsValid(parentConstrOid) && OidIsValid(constrOid))
1878018789
ConstraintSetParentConstraint(constrOid, InvalidOid, InvalidOid);
1878118790

1878218791
index_close(idx, NoLock);

‎src/test/regress/expected/constraints.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -626,6 +626,48 @@ SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclas
626626
(1 row)
627627

628628
DROP TABLE parted_fk_naming;
629+
--
630+
-- Test various ways to create primary keys on partitions, linked to unique
631+
-- indexes (without constraints) on the partitioned table. Ideally these should
632+
-- fail, but we don't dare change released behavior, so instead cope with it at
633+
-- DETACH time.
634+
CREATE TEMP TABLE t (a integer, b integer) PARTITION BY HASH (a, b);
635+
CREATE TEMP TABLE tp (a integer, b integer, PRIMARY KEY (a, b), UNIQUE (b, a));
636+
ALTER TABLE t ATTACH PARTITION tp FOR VALUES WITH (MODULUS 1, REMAINDER 0);
637+
CREATE UNIQUE INDEX t_a_idx ON t (a, b);
638+
CREATE UNIQUE INDEX t_b_idx ON t (b, a);
639+
ALTER INDEX t_a_idx ATTACH PARTITION tp_pkey;
640+
ALTER INDEX t_b_idx ATTACH PARTITION tp_b_a_key;
641+
SELECT conname, conparentid, conislocal, coninhcount
642+
FROM pg_constraint WHERE conname IN ('tp_pkey', 'tp_b_a_key');
643+
conname | conparentid | conislocal | coninhcount
644+
------------+-------------+------------+-------------
645+
tp_pkey | 0 | t | 0
646+
tp_b_a_key | 0 | t | 0
647+
(2 rows)
648+
649+
ALTER TABLE t DETACH PARTITION tp;
650+
DROP TABLE t, tp;
651+
CREATE TEMP TABLE t (a integer) PARTITION BY LIST (a);
652+
CREATE TEMP TABLE tp (a integer PRIMARY KEY);
653+
CREATE UNIQUE INDEX t_a_idx ON t (a);
654+
ALTER TABLE t ATTACH PARTITION tp FOR VALUES IN (1);
655+
ALTER TABLE t DETACH PARTITION tp;
656+
DROP TABLE t, tp;
657+
CREATE TEMP TABLE t (a integer) PARTITION BY LIST (a);
658+
CREATE TEMP TABLE tp (a integer PRIMARY KEY);
659+
CREATE UNIQUE INDEX t_a_idx ON ONLY t (a);
660+
ALTER TABLE t ATTACH PARTITION tp FOR VALUES IN (1);
661+
ALTER TABLE t DETACH PARTITION tp;
662+
DROP TABLE t, tp;
663+
CREATE TABLE regress_constr_partitioned (a integer) PARTITION BY LIST (a);
664+
CREATE TABLE regress_constr_partition1 PARTITION OF regress_constr_partitioned FOR VALUES IN (1);
665+
ALTER TABLE regress_constr_partition1 ADD PRIMARY KEY (a);
666+
CREATE UNIQUE INDEX ON regress_constr_partitioned (a);
667+
BEGIN;
668+
ALTER TABLE regress_constr_partitioned DETACH PARTITION regress_constr_partition1;
669+
ROLLBACK;
670+
-- Leave this one in funny state for pg_upgrade testing
629671
-- test a HOT update that invalidates the conflicting tuple.
630672
-- the trigger should still fire and catch the violation
631673
BEGIN;

‎src/test/regress/sql/constraints.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -449,6 +449,46 @@ ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN (
449449
SELECT connameFROM pg_constraintWHERE conrelid='parted_fk_naming_1'::regclassAND contype='f';
450450
DROPTABLE parted_fk_naming;
451451

452+
--
453+
-- Test various ways to create primary keys on partitions, linked to unique
454+
-- indexes (without constraints) on the partitioned table. Ideally these should
455+
-- fail, but we don't dare change released behavior, so instead cope with it at
456+
-- DETACH time.
457+
CREATE TEMP TABLE t (ainteger, binteger) PARTITION BY HASH (a, b);
458+
CREATE TEMP TABLE tp (ainteger, binteger,PRIMARY KEY (a, b), UNIQUE (b, a));
459+
ALTERTABLE t ATTACH PARTITION tp FORVALUES WITH (MODULUS1, REMAINDER0);
460+
CREATEUNIQUE INDEXt_a_idxON t (a, b);
461+
CREATEUNIQUE INDEXt_b_idxON t (b, a);
462+
ALTERINDEX t_a_idx ATTACH PARTITION tp_pkey;
463+
ALTERINDEX t_b_idx ATTACH PARTITION tp_b_a_key;
464+
SELECT conname, conparentid, conislocal, coninhcount
465+
FROM pg_constraintWHERE connameIN ('tp_pkey','tp_b_a_key');
466+
ALTERTABLE t DETACH PARTITION tp;
467+
DROPTABLE t, tp;
468+
469+
CREATE TEMP TABLE t (ainteger) PARTITION BY LIST (a);
470+
CREATE TEMP TABLE tp (aintegerPRIMARY KEY);
471+
CREATEUNIQUE INDEXt_a_idxON t (a);
472+
ALTERTABLE t ATTACH PARTITION tp FORVALUESIN (1);
473+
ALTERTABLE t DETACH PARTITION tp;
474+
DROPTABLE t, tp;
475+
476+
CREATE TEMP TABLE t (ainteger) PARTITION BY LIST (a);
477+
CREATE TEMP TABLE tp (aintegerPRIMARY KEY);
478+
CREATEUNIQUE INDEXt_a_idxON ONLY t (a);
479+
ALTERTABLE t ATTACH PARTITION tp FORVALUESIN (1);
480+
ALTERTABLE t DETACH PARTITION tp;
481+
DROPTABLE t, tp;
482+
483+
CREATETABLEregress_constr_partitioned (ainteger) PARTITION BY LIST (a);
484+
CREATETABLEregress_constr_partition1 PARTITION OF regress_constr_partitioned FORVALUESIN (1);
485+
ALTERTABLE regress_constr_partition1 ADDPRIMARY KEY (a);
486+
CREATEUNIQUE INDEXON regress_constr_partitioned (a);
487+
BEGIN;
488+
ALTERTABLE regress_constr_partitioned DETACH PARTITION regress_constr_partition1;
489+
ROLLBACK;
490+
-- Leave this one in funny state for pg_upgrade testing
491+
452492
-- test a HOT update that invalidates the conflicting tuple.
453493
-- the trigger should still fire and catch the violation
454494

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp