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

Commit9710d3d

Browse files
committed
Fix TRUNCATE .. CASCADE on partitions
When running TRUNCATE CASCADE on a child of a partitioned tablereferenced by another partitioned table, the truncate was not applied topartitions of the referencing table; this could leave rows violating theconstraint in the referencing partitioned table. Repair by walking thepg_constraint chain all the way up to the topmost referencing table.Note: any partitioned tables containing FKs that reference otherpartitioned tables should be checked for possible violating rows, ifTRUNCATE has occurred in partitions of the referenced table.Reported-by: Christophe CourtoisAuthor: Jehan-Guillaume de RorthaisDiscussion:https://postgr.es/m/20200204183906.115f693e@firost
1 parentcb5b286 commit9710d3d

File tree

4 files changed

+173
-2
lines changed

4 files changed

+173
-2
lines changed

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,9 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="parameter">name</replaceable> [
124124
option can be used to automatically include all dependent tables &mdash;
125125
but be very careful when using this option, or else you might lose data you
126126
did not intend to!
127+
Note in particular that when the table to be truncated is a partition,
128+
siblings partitions are left untouched, but cascading occurs to all
129+
referencing tables and all their partitions with no distinction.
127130
</para>
128131

129132
<para>

‎src/backend/catalog/heap.c

Lines changed: 82 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3396,16 +3396,27 @@ List *
33963396
heap_truncate_find_FKs(List*relationIds)
33973397
{
33983398
List*result=NIL;
3399+
List*oids=list_copy(relationIds);
3400+
List*parent_cons;
3401+
ListCell*cell;
3402+
ScanKeyDatakey;
33993403
RelationfkeyRel;
34003404
SysScanDescfkeyScan;
34013405
HeapTupletuple;
3406+
boolrestart;
3407+
3408+
oids=list_copy(relationIds);
34023409

34033410
/*
34043411
* Must scan pg_constraint. Right now, it is a seqscan because there is
34053412
* no available index on confrelid.
34063413
*/
34073414
fkeyRel=table_open(ConstraintRelationId,AccessShareLock);
34083415

3416+
restart:
3417+
restart= false;
3418+
parent_cons=NIL;
3419+
34093420
fkeyScan=systable_beginscan(fkeyRel,InvalidOid, false,
34103421
NULL,0,NULL);
34113422

@@ -3418,16 +3429,85 @@ heap_truncate_find_FKs(List *relationIds)
34183429
continue;
34193430

34203431
/* Not referencing one of our list of tables */
3421-
if (!list_member_oid(relationIds,con->confrelid))
3432+
if (!list_member_oid(oids,con->confrelid))
34223433
continue;
34233434

3424-
/* Add referencer to result, unless present in input list */
3435+
/*
3436+
* If this constraint has a parent constraint which we have not seen
3437+
* yet, keep track of it for the second loop, below. Tracking parent
3438+
* constraints allows us to climb up to the top-level level constraint
3439+
* and look for all possible relations referencing the partitioned
3440+
* table.
3441+
*/
3442+
if (OidIsValid(con->conparentid)&&
3443+
!list_member_oid(parent_cons,con->conparentid))
3444+
parent_cons=lappend_oid(parent_cons,con->conparentid);
3445+
3446+
/*
3447+
* Add referencer to result, unless present in input list. (Don't
3448+
* worry about dupes: we'll fix that below).
3449+
*/
34253450
if (!list_member_oid(relationIds,con->conrelid))
34263451
result=lappend_oid(result,con->conrelid);
34273452
}
34283453

34293454
systable_endscan(fkeyScan);
3455+
3456+
/*
3457+
* Process each parent constraint we found to add the list of referenced
3458+
* relations by them to the oids list. If we do add any new such
3459+
* relations, redo the first loop above. Also, if we see that the parent
3460+
* constraint in turn has a parent, add that so that we process all
3461+
* relations in a single additional pass.
3462+
*/
3463+
foreach(cell,parent_cons)
3464+
{
3465+
Oidparent=lfirst_oid(cell);
3466+
3467+
ScanKeyInit(&key,
3468+
Anum_pg_constraint_oid,
3469+
BTEqualStrategyNumber,F_OIDEQ,
3470+
ObjectIdGetDatum(parent));
3471+
3472+
fkeyScan=systable_beginscan(fkeyRel,ConstraintOidIndexId,
3473+
true,NULL,1,&key);
3474+
3475+
tuple=systable_getnext(fkeyScan);
3476+
if (HeapTupleIsValid(tuple))
3477+
{
3478+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
3479+
3480+
/*
3481+
* pg_constraint rows always appear for partitioned hierarchies
3482+
* this way: on the each side of the constraint, one row appears
3483+
* for each partition that points to the top-most table on the
3484+
* other side.
3485+
*
3486+
* Because of this arrangement, we can correctly catch all
3487+
* relevant relations by adding to 'parent_cons' all rows with
3488+
* valid conparentid, and to the 'oids' list all rows with a
3489+
* zero conparentid. If any oids are added to 'oids', redo the
3490+
* first loop above by setting 'restart'.
3491+
*/
3492+
if (OidIsValid(con->conparentid))
3493+
parent_cons=list_append_unique_oid(parent_cons,
3494+
con->conparentid);
3495+
elseif (!list_member_oid(oids,con->confrelid))
3496+
{
3497+
oids=lappend_oid(oids,con->confrelid);
3498+
restart= true;
3499+
}
3500+
}
3501+
3502+
systable_endscan(fkeyScan);
3503+
}
3504+
3505+
list_free(parent_cons);
3506+
if (restart)
3507+
gotorestart;
3508+
34303509
table_close(fkeyRel,AccessShareLock);
3510+
list_free(oids);
34313511

34323512
/* Now sort and de-duplicate the result list */
34333513
list_sort(result,list_oid_cmp);

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -542,3 +542,53 @@ SELECT * FROM tp_chk_data();
542542

543543
DROP TABLE truncprim, truncpart;
544544
DROP FUNCTION tp_ins_data(), tp_chk_data();
545+
-- test cascade when referencing a partitioned table
546+
CREATE TABLE trunc_a (a INT PRIMARY KEY) PARTITION BY RANGE (a);
547+
CREATE TABLE trunc_a1 PARTITION OF trunc_a FOR VALUES FROM (0) TO (10);
548+
CREATE TABLE trunc_a2 PARTITION OF trunc_a FOR VALUES FROM (10) TO (20)
549+
PARTITION BY RANGE (a);
550+
CREATE TABLE trunc_a21 PARTITION OF trunc_a2 FOR VALUES FROM (10) TO (12);
551+
CREATE TABLE trunc_a22 PARTITION OF trunc_a2 FOR VALUES FROM (12) TO (16);
552+
CREATE TABLE trunc_a2d PARTITION OF trunc_a2 DEFAULT;
553+
CREATE TABLE trunc_a3 PARTITION OF trunc_a FOR VALUES FROM (20) TO (30);
554+
INSERT INTO trunc_a VALUES (0), (5), (10), (15), (20), (25);
555+
-- truncate a partition cascading to a table
556+
CREATE TABLE ref_b (
557+
b INT PRIMARY KEY,
558+
a INT REFERENCES trunc_a(a) ON DELETE CASCADE
559+
);
560+
INSERT INTO ref_b VALUES (10, 0), (50, 5), (100, 10), (150, 15);
561+
TRUNCATE TABLE trunc_a1 CASCADE;
562+
NOTICE: truncate cascades to table "ref_b"
563+
SELECT a FROM ref_b;
564+
a
565+
---
566+
(0 rows)
567+
568+
DROP TABLE ref_b;
569+
-- truncate a partition cascading to a partitioned table
570+
CREATE TABLE ref_c (
571+
c INT PRIMARY KEY,
572+
a INT REFERENCES trunc_a(a) ON DELETE CASCADE
573+
) PARTITION BY RANGE (c);
574+
CREATE TABLE ref_c1 PARTITION OF ref_c FOR VALUES FROM (100) TO (200);
575+
CREATE TABLE ref_c2 PARTITION OF ref_c FOR VALUES FROM (200) TO (300);
576+
INSERT INTO ref_c VALUES (100, 10), (150, 15), (200, 20), (250, 25);
577+
TRUNCATE TABLE trunc_a21 CASCADE;
578+
NOTICE: truncate cascades to table "ref_c"
579+
NOTICE: truncate cascades to table "ref_c1"
580+
NOTICE: truncate cascades to table "ref_c2"
581+
SELECT a as "from table ref_c" FROM ref_c;
582+
from table ref_c
583+
------------------
584+
(0 rows)
585+
586+
SELECT a as "from table trunc_a" FROM trunc_a ORDER BY a;
587+
from table trunc_a
588+
--------------------
589+
15
590+
20
591+
25
592+
(3 rows)
593+
594+
DROP TABLE trunc_a, ref_c;

‎src/test/regress/sql/truncate.sql

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -289,3 +289,41 @@ TRUNCATE TABLE truncpart;
289289
SELECT*FROM tp_chk_data();
290290
DROPTABLE truncprim, truncpart;
291291
DROPFUNCTION tp_ins_data(), tp_chk_data();
292+
293+
-- test cascade when referencing a partitioned table
294+
CREATETABLEtrunc_a (aINTPRIMARY KEY) PARTITION BY RANGE (a);
295+
CREATETABLEtrunc_a1 PARTITION OF trunc_a FORVALUESFROM (0) TO (10);
296+
CREATETABLEtrunc_a2 PARTITION OF trunc_a FORVALUESFROM (10) TO (20)
297+
PARTITION BY RANGE (a);
298+
CREATETABLEtrunc_a21 PARTITION OF trunc_a2 FORVALUESFROM (10) TO (12);
299+
CREATETABLEtrunc_a22 PARTITION OF trunc_a2 FORVALUESFROM (12) TO (16);
300+
CREATETABLEtrunc_a2d PARTITION OF trunc_a2 DEFAULT;
301+
CREATETABLEtrunc_a3 PARTITION OF trunc_a FORVALUESFROM (20) TO (30);
302+
INSERT INTO trunc_aVALUES (0), (5), (10), (15), (20), (25);
303+
304+
-- truncate a partition cascading to a table
305+
CREATETABLEref_b (
306+
bINTPRIMARY KEY,
307+
aINTREFERENCES trunc_a(a)ON DELETE CASCADE
308+
);
309+
INSERT INTO ref_bVALUES (10,0), (50,5), (100,10), (150,15);
310+
311+
TRUNCATE TABLE trunc_a1 CASCADE;
312+
SELECT aFROM ref_b;
313+
314+
DROPTABLE ref_b;
315+
316+
-- truncate a partition cascading to a partitioned table
317+
CREATETABLEref_c (
318+
cINTPRIMARY KEY,
319+
aINTREFERENCES trunc_a(a)ON DELETE CASCADE
320+
) PARTITION BY RANGE (c);
321+
CREATETABLEref_c1 PARTITION OF ref_c FORVALUESFROM (100) TO (200);
322+
CREATETABLEref_c2 PARTITION OF ref_c FORVALUESFROM (200) TO (300);
323+
INSERT INTO ref_cVALUES (100,10), (150,15), (200,20), (250,25);
324+
325+
TRUNCATE TABLE trunc_a21 CASCADE;
326+
SELECT aas"from table ref_c"FROM ref_c;
327+
SELECT aas"from table trunc_a"FROM trunc_aORDER BY a;
328+
329+
DROPTABLE trunc_a, ref_c;

0 commit comments

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