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

Commit984a6ce

Browse files
committed
Add CASCADE option to TRUNCATE. Joachim Wieland
1 parent2a0ba3f commit984a6ce

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-
/* Notfor one of our list of tables */
2046+
/* Notreferencing 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+
RelationfkeyRel;
2099+
SysScanDescfkeyScan;
2100+
HeapTupletuple;
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_constraintcon= (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+
returnresult;
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+
Oidrelid;
545+
Relationrel;
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-
Relationrel;
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+
#ifdefUSE_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-
Relationrel=lfirst(cell);
604660
Oidheap_relid;
605661
Oidtoast_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
returnnewnode;
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
* AUTHORDATEMAJOR 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
caseT_TruncateStmt:
631-
{
632-
TruncateStmt*stmt= (TruncateStmt*)parsetree;
633-
634-
ExecuteTruncate(stmt->relations);
635-
}
631+
ExecuteTruncate((TruncateStmt*)parsetree);
636632
break;
637633

638634
caseT_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
externvoidheap_truncate_check_FKs(List*relations,booltempTables);
6464

65+
externList*heap_truncate_find_FKs(List*relationIds);
66+
6567
externList*AddRelationRawConstraints(Relationrel,
6668
List*rawColDefaults,
6769
List*rawConstraints);

0 commit comments

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