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

Commit8391779

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 parent734c057 commit8391779

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
@@ -19281,22 +19281,31 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1928119281
foreach(cell, indexes)
1928219282
{
1928319283
Oididxid = lfirst_oid(cell);
19284+
Oidparentidx;
1928419285
Relationidx;
1928519286
OidconstrOid;
19287+
OidparentConstrOid;
1928619288

1928719289
if (!has_superclass(idxid))
1928819290
continue;
1928919291

19290-
Assert((IndexGetRelation(get_partition_parent(idxid, false), false) ==
19291-
RelationGetRelid(rel)));
19292+
parentidx =get_partition_parent(idxid, false);
19293+
Assert((IndexGetRelation(parentidx, false) ==RelationGetRelid(rel)));
1929219294

1929319295
idx = index_open(idxid, AccessExclusiveLock);
1929419296
IndexSetParentIndex(idx, InvalidOid);
1929519297

19296-
/* If there's a constraint associated with the index, detach it too */
19298+
/*
19299+
* If there's a constraint associated with the index, detach it too.
19300+
* Careful: it is possible for a constraint index in a partition to be
19301+
* the child of a non-constraint index, so verify whether the parent
19302+
* index does actually have a constraint.
19303+
*/
1929719304
constrOid = get_relation_idx_constraint_oid(RelationGetRelid(partRel),
1929819305
idxid);
19299-
if (OidIsValid(constrOid))
19306+
parentConstrOid = get_relation_idx_constraint_oid(RelationGetRelid(rel),
19307+
parentidx);
19308+
if (OidIsValid(parentConstrOid) && OidIsValid(constrOid))
1930019309
ConstraintSetParentConstraint(constrOid, InvalidOid, InvalidOid);
1930119310

1930219311
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