On Fri, Feb 03, 2006 at 10:27:30AM -0500, Tom Lane wrote:
> Basically: it's the user's fault if he says "TRUNCATE t2" in a situation
> where the referent of t2 might be changing concurrently. But once
> you've identified t2, it's your fault if you don't track the
> dependencies of t2 correctly, even if someone else is busy renaming them.
Ok, the attached patch now does it correctly as suggested by Alvaro.
For code simplicity I changed the locking order of the tables in the
RESTRICT-case as well.
Before they got locked and then checked one by one. To simplify integration
of the CASCADE-case however, I changed it to lock all - check all.
So it is now:
CASCADE:
lock direct - add and lock cascaded tables - check all - truncate all
RESTRICT:
lock direct (= all) - check all - truncate all.
Joachim
diff -ur cvs/pgsql/doc/src/sgml/ref/truncate.sgml
cvs.build/pgsql/doc/src/sgml/ref/truncate.sgml
--- cvs/pgsql/doc/src/sgml/ref/truncate.sgml 2005-02-22 20:06:18.000000000
+0100
+++ cvs.build/pgsql/doc/src/sgml/ref/truncate.sgml 2006-02-05
01:52:56.000000000 +0100
@@ -20,7 +20,7 @@
<refsynopsisdiv>
<synopsis>
-TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
+TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [
CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -59,9 +59,10 @@
<para>
<command>TRUNCATE</> cannot be used on a table that has foreign-key
- references from other tables, unless all such tables are also truncated
- in the same command. Checking validity in such cases would require table
- scans, and the whole point is not to do one.
+ references from other tables, unless either all such tables are also
+ truncated in the same command or the <literal>CASCADE</> keyword is
+ specified. Checking validity in such cases would require table scans,
+ and the whole point is not to do one.
</para>
<para>
@@ -80,8 +81,17 @@
TRUNCATE TABLE bigtable, fattable;
</programlisting>
</para>
+
+ <para>
+ Truncate the table <literal>othertable</literal> and cascade to tables that
+ are referencing <literal>othertable</literal> via foreign-key constraints:
+
+<programlisting>
+TRUNCATE othertable CASCADE;
+</programlisting>
+ </para>
</refsect1>
-
+
<refsect1>
<title>Compatibility</title>
diff -ur cvs/pgsql/src/backend/catalog/heap.c
cvs.build/pgsql/src/backend/catalog/heap.c
--- cvs/pgsql/src/backend/catalog/heap.c 2005-11-22 19:17:08.000000000
+0100
+++ cvs.build/pgsql/src/backend/catalog/heap.c 2006-02-05 01:52:56.000000000
+0100
@@ -2066,7 +2066,7 @@
get_rel_name(con->conrelid),
get_rel_name(con->confrelid),
NameStr(con->conname)),
- errhint("Truncate table \"%s\"
at the same time.",
+ errhint("Truncate table \"%s\"
at the same time or use TRUNCATE ... CASCADE.",
get_rel_name(con->conrelid))));
}
}
diff -ur cvs/pgsql/src/backend/commands/tablecmds.c
cvs.build/pgsql/src/backend/commands/tablecmds.c
--- cvs/pgsql/src/backend/commands/tablecmds.c 2006-01-30 22:52:35.000000000
+0100
+++ cvs.build/pgsql/src/backend/commands/tablecmds.c 2006-02-05
16:39:48.000000000 +0100
@@ -523,30 +523,163 @@
performDeletion(&object, behavior);
}
+/* This function is essentially copied from heap_truncate_check_FKs.
+ *
+ * We look here for relations referencing one of the relations in the
+ * oids list. We also pass the list of relations we have already found as
+ * found_earlier.
+ *
+ * What gets found in one run will appear in oids in the next call until
+ * no new relations are found.
+ */
+static List *
+BuildReferencingRelationList(List *oids, List *found_earlier)
+{
+ List *referencingRelids = NIL;
+ List *referencingRels = NIL;
+ Relation fkeyRel;
+ SysScanDesc fkeyScan;
+ HeapTuple tuple;
+
+ /* oids is a subset of found_earlier */
+ Assert(list_length(list_difference_oid(oids, found_earlier)) == 0);
+
+ /*
+ * Right now, it is a seqscan because there is no available index on
+ * confrelid (cf. heap_truncate_check_FKs()).
+ */
+ fkeyRel = heap_open(ConstraintRelationId, AccessShareLock);
+ fkeyScan = systable_beginscan(fkeyRel, InvalidOid, false,
+ SnapshotNow,
0, NULL);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ /* Not a foreign key */
+ if (con->contype != CONSTRAINT_FOREIGN)
+ continue;
+
+ /*
+ * Are we interested in this constraint?
+ *
+ * As an optimization to reduce the number of sequential scans
we
+ * also check for relations that have found in this very
sequential
+ * scan. We might find b -> c first and then later within the
+ * same scan a -> b.
+ */
+ if (!list_member_oid(oids, con->confrelid) &&
+ !list_member_oid(referencingRelids, con->confrelid))
+ continue;
+
+ /*
+ * Found a referencer that has to be truncated as well, add it
to the
+ * list if not in already.
+ */
+ if (!list_member_oid(found_earlier, con->conrelid) &&
+ !list_member_oid(referencingRelids, con->conrelid))
+ {
+ Relation rel = relation_open(con->conrelid,
AccessExclusiveLock);
+ referencingRels = lappend(referencingRels, rel);
+ referencingRelids = lappend_oid(referencingRelids,
con->conrelid);
+ }
+ }
+
+ systable_endscan(fkeyScan);
+ heap_close(fkeyRel, AccessShareLock);
+
+ return referencingRels;
+}
+
+
/*
* ExecuteTruncate
* Executes a TRUNCATE command.
*
- * This is a multi-relation truncate. It first opens and grabs exclusive
- * locks on all relations involved, checking permissions and otherwise
- * verifying that the relation is OK for truncation. When they are all
- * open, it checks foreign key references on them, namely that FK references
- * are all internal to the group that's being truncated. Finally all
- * relations are truncated and reindexed.
+ * This is a multi-relation truncate. It first opens and grabs exclusive locks
+ * on all relations involved, in CASCADE mode also on referencing relations.
+ *
+ * Then the function checks permissions and otherwise verifies that the
+ * relations are OK for truncation.
+ *
+ * In RESTRICT mode, when they are all open, it checks foreign key references
+ * on them, namely that FK references are all internal to the group that's
+ * being truncated.
+ *
+ * Finally all relations are truncated and reindexed.
*/
void
-ExecuteTruncate(List *relations)
+ExecuteTruncate(List *relations, DropBehavior behavior)
{
List *rels = NIL;
+ List *directRelids = NIL;
ListCell *cell;
+ RangeVar *rv;
+ Oid relid;
+ Relation rel;
+ /* Grab exclusive lock on all relations specified explicitly in
+ * preparation for truncate */
foreach(cell, relations)
{
- RangeVar *rv = lfirst(cell);
- Relation rel;
-
- /* Grab exclusive lock in preparation for truncate */
+ rv = lfirst(cell);
rel = heap_openrv(rv, AccessExclusiveLock);
+ rels = lappend(rels, rel);
+ if (behavior == DROP_CASCADE)
+ directRelids = lappend_oid(directRelids, rel->rd_id);
+ }
+
+ /* in CASCADED mode, suck in all referencing relations as well */
+ if (behavior == DROP_CASCADE)
+ {
+ List *cascadedRels;
+ /*
+ * - cascadedRelids contains all the OIDs of the relations that
+ * are affected by the TRUNCATE that we have found so far
+ * - newCascadedRels contains only those that we have found in
+ * the last run.
+ */
+ List *cascadedRelids = list_copy(directRelids);
+ List *newCascadedRelids = directRelids;
+ do {
+ /*
+ * Only extend the list further with respect to what
we've found in
+ * the previous run (cascadedRels - the OIDs of its
relations get
+ * passed via newCascadedRelids). Pass the complete
list of
+ * affected OIDs as well such that the function can
check what we
+ * have already. We have an AccessExclusiveLock on all
tables that
+ * are in cascadedRels.
+ */
+ cascadedRels =
BuildReferencingRelationList(newCascadedRelids,
+
cascadedRelids);
+ newCascadedRelids = NIL;
+ foreach(cell, cascadedRels)
+ {
+ rel = lfirst(cell);
+ rels = lappend(rels, rel);
+ newCascadedRelids =
lappend_oid(newCascadedRelids, rel->rd_id);
+ cascadedRelids = lappend_oid(cascadedRelids,
rel->rd_id);
+ }
+ } while (list_length(cascadedRels) > 0);
+ Assert(list_length(rels) == list_length(cascadedRelids));
+ }
+
+ /* now check all involved relations */
+ foreach(cell, rels)
+ {
+ rel = lfirst(cell);
+ relid = rel->rd_id;
+ if (behavior == DROP_CASCADE && !list_member_oid(directRelids,
relid))
+ /*
+ * We are about to execute a cascaded truncate. Display
the
+ * message about that only here. If we did it earlier
there could
+ * be several cascade-messages and then an (e.g.
permission)
+ * failure on one of the first tables being truncated
which could
+ * be confusing.
+ */
+ ereport(NOTICE,
+ (errmsg("truncate cascades to table
\"%s\"",
+
RelationGetRelationName(rel))));
/* Only allow truncate on regular tables */
if (rel->rd_rel->relkind != RELKIND_RELATION)
@@ -585,25 +718,24 @@
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot truncate temporary tables of other
sessions")));
-
- /* Save it into the list of rels to truncate */
- rels = lappend(rels, rel);
}
/*
- * Check foreign key references.
+ * Check foreign key references if not in cascade mode.
*/
- heap_truncate_check_FKs(rels, false);
+ if (behavior == DROP_RESTRICT)
+ heap_truncate_check_FKs(rels, false);
/*
* OK, truncate each table.
*/
foreach(cell, rels)
{
- Relation rel = lfirst(cell);
Oid heap_relid;
Oid toast_relid;
+ rel = lfirst(cell);
+
/*
* Create a new empty storage file for the relation, and assign
it as
* the relfilenode value. The old storage file is
scheduled for
diff -ur cvs/pgsql/src/backend/nodes/copyfuncs.c
cvs.build/pgsql/src/backend/nodes/copyfuncs.c
--- cvs/pgsql/src/backend/nodes/copyfuncs.c 2006-02-05 01:47:41.000000000
+0100
+++ cvs.build/pgsql/src/backend/nodes/copyfuncs.c 2006-02-05
17:59:17.000000000 +0100
@@ -1957,6 +1957,7 @@
TruncateStmt *newnode = makeNode(TruncateStmt);
COPY_NODE_FIELD(relations);
+ COPY_SCALAR_FIELD(behavior);
return newnode;
}
diff -ur cvs/pgsql/src/backend/nodes/equalfuncs.c
cvs.build/pgsql/src/backend/nodes/equalfuncs.c
--- cvs/pgsql/src/backend/nodes/equalfuncs.c 2006-02-05 01:47:41.000000000
+0100
+++ cvs.build/pgsql/src/backend/nodes/equalfuncs.c 2006-02-05
17:59:49.000000000 +0100
@@ -926,6 +926,7 @@
_equalTruncateStmt(TruncateStmt *a, TruncateStmt *b)
{
COMPARE_NODE_FIELD(relations);
+ COMPARE_SCALAR_FIELD(behavior);
return true;
}
diff -ur cvs/pgsql/src/backend/parser/gram.y
cvs.build/pgsql/src/backend/parser/gram.y
--- cvs/pgsql/src/backend/parser/gram.y 2006-02-05 01:47:41.000000000 +0100
+++ cvs.build/pgsql/src/backend/parser/gram.y 2006-02-05 01:52:56.000000000
+0100
@@ -2938,10 +2938,11 @@
*****************************************************************************/
TruncateStmt:
- TRUNCATE opt_table qualified_name_list
+ TRUNCATE opt_table qualified_name_list opt_drop_behavior
{
TruncateStmt *n =
makeNode(TruncateStmt);
n->relations = $3;
+ n->behavior = $4;
$$ = (Node *)n;
}
;
diff -ur cvs/pgsql/src/backend/tcop/utility.c
cvs.build/pgsql/src/backend/tcop/utility.c
--- cvs/pgsql/src/backend/tcop/utility.c 2005-11-29 02:25:49.000000000
+0100
+++ cvs.build/pgsql/src/backend/tcop/utility.c 2006-02-05 01:52:56.000000000
+0100
@@ -631,7 +631,7 @@
{
TruncateStmt *stmt = (TruncateStmt *) parsetree;
- ExecuteTruncate(stmt->relations);
+ ExecuteTruncate(stmt->relations,
stmt->behavior);
}
break;
diff -ur cvs/pgsql/src/include/commands/tablecmds.h
cvs.build/pgsql/src/include/commands/tablecmds.h
--- cvs/pgsql/src/include/commands/tablecmds.h 2005-11-21 13:49:32.000000000
+0100
+++ cvs.build/pgsql/src/include/commands/tablecmds.h 2006-02-05
01:52:56.000000000 +0100
@@ -36,7 +36,7 @@
Oid oldNspOid, Oid
newNspOid,
bool hasDependEntry);
-extern void ExecuteTruncate(List *relations);
+extern void ExecuteTruncate(List *relations, DropBehavior behavior);
extern void renameatt(Oid myrelid,
const char *oldattname,
diff -ur cvs/pgsql/src/include/nodes/parsenodes.h
cvs.build/pgsql/src/include/nodes/parsenodes.h
--- cvs/pgsql/src/include/nodes/parsenodes.h 2006-02-05 01:47:41.000000000
+0100
+++ cvs.build/pgsql/src/include/nodes/parsenodes.h 2006-02-05
01:52:56.000000000 +0100
@@ -1309,6 +1309,7 @@
{
NodeTag type;
List *relations; /* relations (RangeVars) to be
truncated */
+ DropBehavior behavior; /* RESTRICT or CASCADE behavior */
} TruncateStmt;
/* ----------------------
diff -ur cvs/pgsql/src/test/regress/expected/truncate.out
cvs.build/pgsql/src/test/regress/expected/truncate.out
--- cvs/pgsql/src/test/regress/expected/truncate.out 2005-01-27
04:19:08.000000000 +0100
+++ cvs.build/pgsql/src/test/regress/expected/truncate.out 2006-02-05
01:52:56.000000000 +0100
@@ -40,30 +40,37 @@
TRUNCATE TABLE truncate_a; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint
"trunc_b_a_fkey".
-HINT: Truncate table "trunc_b" at the same time.
+HINT: Truncate table "trunc_b" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE truncate_a,trunc_b; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "truncate_a" via foreign key constraint
"trunc_e_a_fkey".
-HINT: Truncate table "trunc_e" at the same time.
+HINT: Truncate table "trunc_e" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok
TRUNCATE TABLE truncate_a,trunc_e; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint
"trunc_b_a_fkey".
-HINT: Truncate table "trunc_b" at the same time.
+HINT: Truncate table "trunc_b" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_d" references "trunc_c" via foreign key constraint
"trunc_d_a_fkey".
-HINT: Truncate table "trunc_d" at the same time.
+HINT: Truncate table "trunc_d" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint
"trunc_e_b_fkey".
-HINT: Truncate table "trunc_e" at the same time.
+HINT: Truncate table "trunc_e" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint
"trunc_b_a_fkey".
-HINT: Truncate table "trunc_b" at the same time.
+HINT: Truncate table "trunc_b" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
+TRUNCATE TABLE truncate_a RESTRICT; -- fail
+ERROR: cannot truncate a table referenced in a foreign key constraint
+DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint
"trunc_b_a_fkey".
+HINT: Truncate table "trunc_b" at the same time or use TRUNCATE ... CASCADE.
+TRUNCATE TABLE truncate_a CASCADE; -- ok
+NOTICE: truncate cascades to table "trunc_b"
+NOTICE: truncate cascades to table "trunc_e"
-- circular references
ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
-- Add some data to verify that truncating actually works ...
@@ -75,19 +82,19 @@
TRUNCATE TABLE trunc_c;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_d" references "trunc_c" via foreign key constraint
"trunc_d_a_fkey".
-HINT: Truncate table "trunc_d" at the same time.
+HINT: Truncate table "trunc_d" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_e" references "trunc_c" via foreign key constraint
"trunc_e_b_fkey".
-HINT: Truncate table "trunc_e" at the same time.
+HINT: Truncate table "trunc_e" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "truncate_a" references "trunc_c" via foreign key constraint
"truncate_a_col1_fkey".
-HINT: Truncate table "truncate_a" at the same time.
+HINT: Truncate table "truncate_a" at the same time or use TRUNCATE ...
CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "trunc_b" references "truncate_a" via foreign key constraint
"trunc_b_a_fkey".
-HINT: Truncate table "trunc_b" at the same time.
+HINT: Truncate table "trunc_b" at the same time or use TRUNCATE ... CASCADE.
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
-- Verify that truncating did actually work
SELECT * FROM truncate_a
@@ -106,6 +113,33 @@
---+---
(0 rows)
+-- Add data again to test TRUNCATE ... CASCADE
+INSERT INTO trunc_c VALUES (1);
+INSERT INTO truncate_a VALUES (1);
+INSERT INTO trunc_b VALUES (1);
+INSERT INTO trunc_d VALUES (1);
+INSERT INTO trunc_e VALUES (1,1);
+TRUNCATE TABLE trunc_c CASCADE; -- ok
+NOTICE: truncate cascades to table "trunc_d"
+NOTICE: truncate cascades to table "trunc_e"
+NOTICE: truncate cascades to table "truncate_a"
+NOTICE: truncate cascades to table "trunc_b"
+SELECT * FROM truncate_a
+ UNION ALL
+ SELECT * FROM trunc_c
+ UNION ALL
+ SELECT * FROM trunc_b
+ UNION ALL
+ SELECT * FROM trunc_d;
+ col1
+------
+(0 rows)
+
+SELECT * FROM trunc_e;
+ a | b
+---+---
+(0 rows)
+
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
NOTICE: drop cascades to constraint trunc_e_a_fkey on table trunc_e
NOTICE: drop cascades to constraint trunc_b_a_fkey on table trunc_b
diff -ur cvs/pgsql/src/test/regress/sql/truncate.sql
cvs.build/pgsql/src/test/regress/sql/truncate.sql
--- cvs/pgsql/src/test/regress/sql/truncate.sql 2005-01-27 04:19:37.000000000
+0100
+++ cvs.build/pgsql/src/test/regress/sql/truncate.sql 2006-02-05
01:52:56.000000000 +0100
@@ -30,6 +30,9 @@
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
+TRUNCATE TABLE truncate_a RESTRICT; -- fail
+TRUNCATE TABLE truncate_a CASCADE; -- ok
+
-- circular references
ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
@@ -55,4 +58,22 @@
SELECT * FROM trunc_d;
SELECT * FROM trunc_e;
+-- Add data again to test TRUNCATE ... CASCADE
+INSERT INTO trunc_c VALUES (1);
+INSERT INTO truncate_a VALUES (1);
+INSERT INTO trunc_b VALUES (1);
+INSERT INTO trunc_d VALUES (1);
+INSERT INTO trunc_e VALUES (1,1);
+
+TRUNCATE TABLE trunc_c CASCADE; -- ok
+
+SELECT * FROM truncate_a
+ UNION ALL
+ SELECT * FROM trunc_c
+ UNION ALL
+ SELECT * FROM trunc_b
+ UNION ALL
+ SELECT * FROM trunc_d;
+SELECT * FROM trunc_e;
+
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
