Skip to content

Commit 984a6ce

Browse files
tglsfdctglsfdc
committed
Add CASCADE option to TRUNCATE. Joachim Wieland
1 parent 2a0ba3f commit 984a6ce

File tree

12 files changed

+252
-47
lines changed

12 files changed

+252
-47
lines changed

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

Lines changed: 38 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.19 2005/02/22 19:06:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.20 2006/03/03 03:30:52 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
23+
TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
2424
</synopsis>
2525
</refsynopsisdiv>
2626

@@ -47,6 +47,27 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
4747
</para>
4848
</listitem>
4949
</varlistentry>
50+
51+
<varlistentry>
52+
<term><literal>CASCADE</literal></term>
53+
<listitem>
54+
<para>
55+
Automatically truncate all tables that have foreign-key references
56+
to any of the named tables, or to any tables added to the group
57+
due to <literal>CASCADE</literal>.
58+
</para>
59+
</listitem>
60+
</varlistentry>
61+
62+
<varlistentry>
63+
<term><literal>RESTRICT</literal></term>
64+
<listitem>
65+
<para>
66+
Refuse to truncate if any of the tables have foreign-key references
67+
from tables that are not to be truncated. This is the default.
68+
</para>
69+
</listitem>
70+
</varlistentry>
5071
</variablelist>
5172
</refsect1>
5273

@@ -61,7 +82,10 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
6182
<command>TRUNCATE</> cannot be used on a table that has foreign-key
6283
references from other tables, unless all such tables are also truncated
6384
in the same command. Checking validity in such cases would require table
64-
scans, and the whole point is not to do one.
85+
scans, and the whole point is not to do one. The <literal>CASCADE</>
86+
option can be used to automatically include all dependent tables &mdash;
87+
but be very careful when using this option, else you might lose data you
88+
did not intend to!
6589
</para>
6690

6791
<para>
@@ -78,10 +102,20 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
78102

79103
<programlisting>
80104
TRUNCATE TABLE bigtable, fattable;
105+
</programlisting>
106+
</para>
107+
108+
<para>
109+
Truncate the table <literal>othertable</literal>, and cascade to any tables
110+
that are referencing <literal>othertable</literal> via foreign-key
111+
constraints:
112+
113+
<programlisting>
114+
TRUNCATE othertable CASCADE;
81115
</programlisting>
82116
</para>
83117
</refsect1>
84-
118+
85119
<refsect1>
86120
<title>Compatibility</title>
87121

‎src/backend/catalog/heap.c

Lines changed: 59 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.293 2005/11/22 18:17:08 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.294 2006/03/03 03:30:52 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -2043,7 +2043,7 @@ heap_truncate_check_FKs(List *relations, bool tempTables)
20432043
if (con->contype != CONSTRAINT_FOREIGN)
20442044
continue;
20452045

2046-
/* Not for one of our list of tables */
2046+
/* Not referencing one of our list of tables */
20472047
if (!list_member_oid(oids, con->confrelid))
20482048
continue;
20492049

@@ -2066,11 +2066,67 @@ heap_truncate_check_FKs(List *relations, bool tempTables)
20662066
get_rel_name(con->conrelid),
20672067
get_rel_name(con->confrelid),
20682068
NameStr(con->conname)),
2069-
errhint("Truncate table \"%s\" at the same time.",
2069+
errhint("Truncate table \"%s\" at the same time, "
2070+
"or use TRUNCATE ... CASCADE.",
20702071
get_rel_name(con->conrelid))));
20712072
}
20722073
}
20732074

20742075
systable_endscan(fkeyScan);
20752076
heap_close(fkeyRel, AccessShareLock);
20762077
}
2078+
2079+
/*
2080+
* heap_truncate_find_FKs
2081+
* Find relations having foreign keys referencing any relations that
2082+
* are to be truncated
2083+
*
2084+
* This is almost the same code as heap_truncate_check_FKs, but we don't
2085+
* raise an error if we find such relations; instead we return a list of
2086+
* their OIDs. Also note that the input is a list of OIDs not a list
2087+
* of Relations. The result list does *not* include any rels that are
2088+
* already in the input list.
2089+
*
2090+
* Note: caller should already have exclusive lock on all rels mentioned
2091+
* in relationIds. Since adding or dropping an FK requires exclusive lock
2092+
* on both rels, this ensures that the answer will be stable.
2093+
*/
2094+
List *
2095+
heap_truncate_find_FKs(List *relationIds)
2096+
{
2097+
List *result = NIL;
2098+
Relation fkeyRel;
2099+
SysScanDesc fkeyScan;
2100+
HeapTuple tuple;
2101+
2102+
/*
2103+
* Must scan pg_constraint. Right now, it is a seqscan because
2104+
* there is no available index on confrelid.
2105+
*/
2106+
fkeyRel = heap_open(ConstraintRelationId, AccessShareLock);
2107+
2108+
fkeyScan = systable_beginscan(fkeyRel, InvalidOid, false,
2109+
SnapshotNow, 0, NULL);
2110+
2111+
while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
2112+
{
2113+
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
2114+
2115+
/* Not a foreign key */
2116+
if (con->contype != CONSTRAINT_FOREIGN)
2117+
continue;
2118+
2119+
/* Not referencing one of our list of tables */
2120+
if (!list_member_oid(relationIds, con->confrelid))
2121+
continue;
2122+
2123+
/* Add referencer unless already in input or result list */
2124+
if (!list_member_oid(relationIds, con->conrelid))
2125+
result = list_append_unique_oid(result, con->conrelid);
2126+
}
2127+
2128+
systable_endscan(fkeyScan);
2129+
heap_close(fkeyRel, AccessShareLock);
2130+
2131+
return result;
2132+
}

‎src/backend/commands/tablecmds.c

Lines changed: 74 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.177 2006/01/30 16:18:58 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.178 2006/03/03 03:30:52 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -527,26 +527,79 @@ RemoveRelation(const RangeVar *relation, DropBehavior behavior)
527527
* ExecuteTruncate
528528
* Executes a TRUNCATE command.
529529
*
530-
* This is a multi-relation truncate. It first opens and grabs exclusive
531-
* locks on all relations involved, checking permissions and otherwise
532-
* verifying that the relation is OK for truncation. When they are all
533-
* open, it checks foreign key references on them, namely that FK references
534-
* are all internal to the group that's being truncated. Finally all
535-
* relations are truncated and reindexed.
530+
* This is a multi-relation truncate. We first open and grab exclusive
531+
* lock on all relations involved, checking permissions and otherwise
532+
* verifying that the relation is OK for truncation. In CASCADE mode,
533+
* relations having FK references to the targeted relations are automatically
534+
* added to the group; in RESTRICT mode, we check that all FK references are
535+
* internal to the group that's being truncated. Finally all the relations
536+
* are truncated and reindexed.
536537
*/
537538
void
538-
ExecuteTruncate(List *relations)
539+
ExecuteTruncate(TruncateStmt *stmt)
539540
{
540541
List *rels = NIL;
542+
List *directRelids = NIL;
541543
ListCell *cell;
544+
Oid relid;
545+
Relation rel;
542546

543-
foreach(cell, relations)
547+
/*
548+
* Open and exclusive-lock all the explicitly-specified relations
549+
*/
550+
foreach(cell, stmt->relations)
544551
{
545552
RangeVar *rv = lfirst(cell);
546-
Relation rel;
547553

548-
/* Grab exclusive lock in preparation for truncate */
549554
rel = heap_openrv(rv, AccessExclusiveLock);
555+
rels = lappend(rels, rel);
556+
directRelids = lappend_oid(directRelids, RelationGetRelid(rel));
557+
}
558+
559+
/*
560+
* In CASCADE mode, suck in all referencing relations as well. This
561+
* requires multiple iterations to find indirectly-dependent relations.
562+
* At each phase, we need to exclusive-lock new rels before looking
563+
* for their dependencies, else we might miss something.
564+
*/
565+
if (stmt->behavior == DROP_CASCADE)
566+
{
567+
List *relids = list_copy(directRelids);
568+
569+
for (;;)
570+
{
571+
List *newrelids;
572+
573+
newrelids = heap_truncate_find_FKs(relids);
574+
if (newrelids == NIL)
575+
break; /* nothing else to add */
576+
577+
foreach(cell, newrelids)
578+
{
579+
relid = lfirst_oid(cell);
580+
rel = heap_open(relid, AccessExclusiveLock);
581+
rels = lappend(rels, rel);
582+
relids = lappend_oid(relids, relid);
583+
}
584+
}
585+
}
586+
587+
/* now check all involved relations */
588+
foreach(cell, rels)
589+
{
590+
rel = (Relation) lfirst(cell);
591+
relid = RelationGetRelid(rel);
592+
593+
/*
594+
* If this table was added to the command by CASCADE, report it.
595+
* We don't do this earlier because if we error out on one of the
596+
* tables, it'd be confusing to list subsequently-added tables.
597+
*/
598+
if (stmt->behavior == DROP_CASCADE &&
599+
!list_member_oid(directRelids, relid))
600+
ereport(NOTICE,
601+
(errmsg("truncate cascades to table \"%s\"",
602+
RelationGetRelationName(rel))));
550603

551604
/* Only allow truncate on regular tables */
552605
if (rel->rd_rel->relkind != RELKIND_RELATION)
@@ -585,25 +638,30 @@ ExecuteTruncate(List *relations)
585638
ereport(ERROR,
586639
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
587640
errmsg("cannot truncate temporary tables of other sessions")));
588-
589-
/* Save it into the list of rels to truncate */
590-
rels = lappend(rels, rel);
591641
}
592642

593643
/*
594-
* Check foreign key references.
644+
* Check foreign key references. In CASCADE mode, this should be
645+
* unnecessary since we just pulled in all the references; but as
646+
* a cross-check, do it anyway if in an Assert-enabled build.
595647
*/
648+
#ifdef USE_ASSERT_CHECKING
596649
heap_truncate_check_FKs(rels, false);
650+
#else
651+
if (stmt->behavior == DROP_RESTRICT)
652+
heap_truncate_check_FKs(rels, false);
653+
#endif
597654

598655
/*
599656
* OK, truncate each table.
600657
*/
601658
foreach(cell, rels)
602659
{
603-
Relation rel = lfirst(cell);
604660
Oid heap_relid;
605661
Oid toast_relid;
606662

663+
rel = (Relation) lfirst(cell);
664+
607665
/*
608666
* Create a new empty storage file for the relation, and assign it as
609667
* the relfilenode value. The old storage file is scheduled for

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.327 2006/02/19 00:04:26 neilc Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.328 2006/03/03 03:30:52 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1961,6 +1961,7 @@ _copyTruncateStmt(TruncateStmt *from)
19611961
TruncateStmt *newnode = makeNode(TruncateStmt);
19621962

19631963
COPY_NODE_FIELD(relations);
1964+
COPY_SCALAR_FIELD(behavior);
19641965

19651966
return newnode;
19661967
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.263 2006/02/19 00:04:26 neilc Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.264 2006/03/03 03:30:52 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -930,6 +930,7 @@ static bool
930930
_equalTruncateStmt(TruncateStmt *a, TruncateStmt *b)
931931
{
932932
COMPARE_NODE_FIELD(relations);
933+
COMPARE_SCALAR_FIELD(behavior);
933934

934935
return true;
935936
}

‎src/backend/parser/gram.y

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.531 2006/02/28 22:37:26 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.532 2006/03/03 03:30:53 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -2938,10 +2938,11 @@ attrs: '.' attr_name
29382938
*****************************************************************************/
29392939

29402940
TruncateStmt:
2941-
TRUNCATE opt_table qualified_name_list
2941+
TRUNCATE opt_table qualified_name_list opt_drop_behavior
29422942
{
29432943
TruncateStmt *n = makeNode(TruncateStmt);
29442944
n->relations = $3;
2945+
n->behavior = $4;
29452946
$$ = (Node *)n;
29462947
}
29472948
;

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.252 2006/02/12 19:11:01 momjian Exp $
13+
* $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.253 2006/03/03 03:30:53 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -628,11 +628,7 @@ ProcessUtility(Node *parsetree,
628628
break;
629629

630630
case T_TruncateStmt:
631-
{
632-
TruncateStmt *stmt = (TruncateStmt *) parsetree;
633-
634-
ExecuteTruncate(stmt->relations);
635-
}
631+
ExecuteTruncate((TruncateStmt *) parsetree);
636632
break;
637633

638634
case T_CommentStmt:

‎src/include/catalog/heap.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/heap.h,v 1.76 2005/10/15 02:49:42 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/heap.h,v 1.77 2006/03/03 03:30:53 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -62,6 +62,8 @@ extern void heap_truncate(List *relids);
6262

6363
extern void heap_truncate_check_FKs(List *relations, bool tempTables);
6464

65+
extern List *heap_truncate_find_FKs(List *relationIds);
66+
6567
extern List *AddRelationRawConstraints(Relation rel,
6668
List *rawColDefaults,
6769
List *rawConstraints);

0 commit comments

Comments
 (0)