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

Commit30ca4e1

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 parentae4e072 commit30ca4e1

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
@@ -19299,22 +19299,31 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent,
1929919299
foreach(cell, indexes)
1930019300
{
1930119301
Oididxid = lfirst_oid(cell);
19302+
Oidparentidx;
1930219303
Relationidx;
1930319304
OidconstrOid;
19305+
OidparentConstrOid;
1930419306

1930519307
if (!has_superclass(idxid))
1930619308
continue;
1930719309

19308-
Assert((IndexGetRelation(get_partition_parent(idxid, false), false) ==
19309-
RelationGetRelid(rel)));
19310+
parentidx =get_partition_parent(idxid, false);
19311+
Assert((IndexGetRelation(parentidx, false) ==RelationGetRelid(rel)));
1931019312

1931119313
idx = index_open(idxid, AccessExclusiveLock);
1931219314
IndexSetParentIndex(idx, InvalidOid);
1931319315

19314-
/* If there's a constraint associated with the index, detach it too */
19316+
/*
19317+
* If there's a constraint associated with the index, detach it too.
19318+
* Careful: it is possible for a constraint index in a partition to be
19319+
* the child of a non-constraint index, so verify whether the parent
19320+
* index does actually have a constraint.
19321+
*/
1931519322
constrOid = get_relation_idx_constraint_oid(RelationGetRelid(partRel),
1931619323
idxid);
19317-
if (OidIsValid(constrOid))
19324+
parentConstrOid = get_relation_idx_constraint_oid(RelationGetRelid(rel),
19325+
parentidx);
19326+
if (OidIsValid(parentConstrOid) && OidIsValid(constrOid))
1931819327
ConstraintSetParentConstraint(constrOid, InvalidOid, InvalidOid);
1931919328

1932019329
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