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
Appearance settings

Commit 9710d3d

Browse filesBrowse files
alvherrealvherre
committed
Fix TRUNCATE .. CASCADE on partitions
When running TRUNCATE CASCADE on a child of a partitioned table referenced by another partitioned table, the truncate was not applied to partitions of the referencing table; this could leave rows violating the constraint in the referencing partitioned table. Repair by walking the pg_constraint chain all the way up to the topmost referencing table. Note: any partitioned tables containing FKs that reference other partitioned tables should be checked for possible violating rows, if TRUNCATE has occurred in partitions of the referenced table. Reported-by: Christophe Courtois Author: Jehan-Guillaume de Rorthais Discussion: https://postgr.es/m/20200204183906.115f693e@firost
1 parent cb5b286 commit 9710d3d
Copy full SHA for 9710d3d

File tree

Expand file treeCollapse file tree

4 files changed

+173
-2
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+173
-2
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/truncate.sgml
+3Lines 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

Copy file name to clipboardExpand all lines: src/backend/catalog/heap.c
+82-2Lines 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+
ScanKeyData key;
33993403
Relation fkeyRel;
34003404
SysScanDesc fkeyScan;
34013405
HeapTuple tuple;
3406+
bool restart;
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+
Oid parent = 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_constraint con = (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+
else if (!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+
goto restart;
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

Copy file name to clipboardExpand all lines: src/test/regress/expected/truncate.out
+50Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/truncate.sql
+38Lines 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
DROP TABLE truncprim, truncpart;
291291
DROP FUNCTION tp_ins_data(), tp_chk_data();
292+
293+
-- test cascade when referencing a partitioned table
294+
CREATE TABLE trunc_a (a INT PRIMARY KEY) PARTITION BY RANGE (a);
295+
CREATE TABLE trunc_a1 PARTITION OF trunc_a FOR VALUES FROM (0) TO (10);
296+
CREATE TABLE trunc_a2 PARTITION OF trunc_a FOR VALUES FROM (10) TO (20)
297+
PARTITION BY RANGE (a);
298+
CREATE TABLE trunc_a21 PARTITION OF trunc_a2 FOR VALUES FROM (10) TO (12);
299+
CREATE TABLE trunc_a22 PARTITION OF trunc_a2 FOR VALUES FROM (12) TO (16);
300+
CREATE TABLE trunc_a2d PARTITION OF trunc_a2 DEFAULT;
301+
CREATE TABLE trunc_a3 PARTITION OF trunc_a FOR VALUES FROM (20) TO (30);
302+
INSERT INTO trunc_a VALUES (0), (5), (10), (15), (20), (25);
303+
304+
-- truncate a partition cascading to a table
305+
CREATE TABLE ref_b (
306+
b INT PRIMARY KEY,
307+
a INT REFERENCES trunc_a(a) ON DELETE CASCADE
308+
);
309+
INSERT INTO ref_b VALUES (10, 0), (50, 5), (100, 10), (150, 15);
310+
311+
TRUNCATE TABLE trunc_a1 CASCADE;
312+
SELECT a FROM ref_b;
313+
314+
DROP TABLE ref_b;
315+
316+
-- truncate a partition cascading to a partitioned table
317+
CREATE TABLE ref_c (
318+
c INT PRIMARY KEY,
319+
a INT REFERENCES trunc_a(a) ON DELETE CASCADE
320+
) PARTITION BY RANGE (c);
321+
CREATE TABLE ref_c1 PARTITION OF ref_c FOR VALUES FROM (100) TO (200);
322+
CREATE TABLE ref_c2 PARTITION OF ref_c FOR VALUES FROM (200) TO (300);
323+
INSERT INTO ref_c VALUES (100, 10), (150, 15), (200, 20), (250, 25);
324+
325+
TRUNCATE TABLE trunc_a21 CASCADE;
326+
SELECT a as "from table ref_c" FROM ref_c;
327+
SELECT a as "from table trunc_a" FROM trunc_a ORDER BY a;
328+
329+
DROP TABLE trunc_a, ref_c;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.