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

Commitf07b968

Browse files
committed
Generalize TRUNCATE to support truncating multiple tables in one
command. This is useful because we can allow truncation of tablesreferenced by foreign keys, so long as the referencing table istruncated in the same command.Alvaro Herrera
1 parent4fe2012 commitf07b968

File tree

14 files changed

+385
-167
lines changed

14 files changed

+385
-167
lines changed

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

Lines changed: 15 additions & 14 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.17 2004/03/23 13:21:41 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.18 2005/01/27 03:17:08 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -11,7 +11,7 @@ PostgreSQL documentation
1111

1212
<refnamediv>
1313
<refname>TRUNCATE</refname>
14-
<refpurpose>empty a table</refpurpose>
14+
<refpurpose>empty a table or set of tables</refpurpose>
1515
</refnamediv>
1616

1717
<indexterm zone="sql-truncate">
@@ -20,18 +20,18 @@ PostgreSQL documentation
2020

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

2727
<refsect1>
2828
<title>Description</title>
2929

3030
<para>
31-
<command>TRUNCATE</command> quickly removes all rows from a
32-
table. It has the same effect as an unqualified
33-
<command>DELETE</command> but since it does not actually scan the
34-
table it is faster. This is most useful on large tables.
31+
<command>TRUNCATE</command> quickly removes all rows from a set of
32+
tables. It has the same effect as an unqualified
33+
<command>DELETE</command>on each table,but since it does not actually
34+
scan the tables it is faster. This is most useful on large tables.
3535
</para>
3636
</refsect1>
3737

@@ -43,7 +43,7 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable>
4343
<term><replaceable class="PARAMETER">name</replaceable></term>
4444
<listitem>
4545
<para>
46-
The name (optionally schema-qualified) ofthe table to be truncated.
46+
The name (optionally schema-qualified) ofa table to be truncated.
4747
</para>
4848
</listitem>
4949
</varlistentry>
@@ -54,25 +54,26 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable>
5454
<title>Notes</title>
5555

5656
<para>
57-
<command>TRUNCATE</> cannot be used if there are foreign-key references
58-
to the table from other tables. Checking validity in such cases would
59-
require table scans, and the whole point is not to do one.
57+
<command>TRUNCATE</> cannot be used on a table that has foreign-key
58+
references from other tables, unless all such tables are also truncated
59+
in the same command. Checking validity in such cases would require table
60+
scans, and the whole point is not to do one.
6061
</para>
6162

6263
<para>
6364
<command>TRUNCATE</> will not run any user-defined <literal>ON
64-
DELETE</literal> triggers that might exist for thetable.
65+
DELETE</literal> triggers that might exist for thetables.
6566
</para>
6667
</refsect1>
6768

6869
<refsect1>
6970
<title>Examples</title>
7071

7172
<para>
72-
Truncate thetable <literal>bigtable</literal>:
73+
Truncate thetables <literal>bigtable</literal> and <literal>fattable</literal>:
7374

7475
<programlisting>
75-
TRUNCATE TABLE bigtable;
76+
TRUNCATE TABLE bigtable, fattable;
7677
</programlisting>
7778
</para>
7879
</refsect1>

‎src/backend/catalog/heap.c‎

Lines changed: 99 additions & 49 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.279 2005/01/10 20:02:19 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.280 2005/01/27 03:17:17 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -1985,99 +1985,149 @@ RelationTruncateIndexes(Oid heapId)
19851985
/*
19861986
* heap_truncate
19871987
*
1988-
* This routine deletes all data within the specifiedrelation.
1988+
* This routine deletes all data withinallthe specifiedrelations.
19891989
*
19901990
* This is not transaction-safe! There is another, transaction-safe
1991-
* implementation in commands/cluster.c. We now use this only for
1991+
* implementation in commands/tablecmds.c. We now use this only for
19921992
* ON COMMIT truncation of temporary tables, where it doesn't matter.
19931993
*/
19941994
void
1995-
heap_truncate(Oidrid)
1995+
heap_truncate(List*relids)
19961996
{
1997-
Relationrel;
1998-
Oidtoastrelid;
1997+
List*relations=NIL;
1998+
ListCell*cell;
1999+
2000+
/* Open relations for processing, and grab exclusive access on each */
2001+
foreach(cell,relids)
2002+
{
2003+
Oidrid=lfirst_oid(cell);
2004+
Relationrel;
2005+
Oidtoastrelid;
19992006

2000-
/* Open relation for processing, and grab exclusive access on it. */
2001-
rel=heap_open(rid,AccessExclusiveLock);
2007+
rel=heap_open(rid,AccessExclusiveLock);
2008+
relations=lappend(relations,rel);
2009+
2010+
/* If there is a toast table, add it to the list too */
2011+
toastrelid=rel->rd_rel->reltoastrelid;
2012+
if (OidIsValid(toastrelid))
2013+
{
2014+
rel=heap_open(toastrelid,AccessExclusiveLock);
2015+
relations=lappend(relations,rel);
2016+
}
2017+
}
20022018

20032019
/* Don't allow truncate on tables that are referenced by foreign keys */
2004-
heap_truncate_check_FKs(rel);
2020+
heap_truncate_check_FKs(relations, true);
20052021

2006-
/*
2007-
* Release any buffers associated with this relation. If they're
2008-
* dirty, they're just dropped without bothering to flush to disk.
2009-
*/
2010-
DropRelationBuffers(rel);
2022+
/* OK to do it */
2023+
foreach(cell,relations)
2024+
{
2025+
Relationrel=lfirst(cell);
20112026

2012-
/* Now truncate the actual data */
2013-
RelationTruncate(rel,0);
2027+
/*
2028+
* Release any buffers associated with this relation. If they're
2029+
* dirty, they're just dropped without bothering to flush to disk.
2030+
*/
2031+
DropRelationBuffers(rel);
20142032

2015-
/*If this relation has indexes,truncate theindexes too */
2016-
RelationTruncateIndexes(rid);
2033+
/*Nowtruncate theactual data */
2034+
RelationTruncate(rel,0);
20172035

2018-
/* If it has a toast table, recursively truncate that too */
2019-
toastrelid=rel->rd_rel->reltoastrelid;
2020-
if (OidIsValid(toastrelid))
2021-
heap_truncate(toastrelid);
2036+
/* If this relation has indexes, truncate the indexes too */
2037+
RelationTruncateIndexes(RelationGetRelid(rel));
20222038

2023-
/*
2024-
* Close the relation, but keep exclusive lock on it until commit.
2025-
*/
2026-
heap_close(rel,NoLock);
2039+
/*
2040+
* Close the relation, but keep exclusive lock on it until commit.
2041+
*/
2042+
heap_close(rel,NoLock);
2043+
}
20272044
}
20282045

20292046
/*
20302047
* heap_truncate_check_FKs
2031-
*Check for foreign keys referencing a relation that's to be truncated
2048+
*Check for foreign keys referencing a list of relations that
2049+
*are to be truncated
20322050
*
20332051
* We disallow such FKs (except self-referential ones) since the whole point
20342052
* of TRUNCATE is to not scan the individual rows to be thrown away.
20352053
*
20362054
* This is split out so it can be shared by both implementations of truncate.
2037-
* Caller should already hold a suitable lock on the relation.
2055+
* Caller should already hold a suitable lock on the relations.
2056+
*
2057+
* tempTables is only used to select an appropriate error message.
20382058
*/
20392059
void
2040-
heap_truncate_check_FKs(Relationrel)
2060+
heap_truncate_check_FKs(List*relations,booltempTables)
20412061
{
2042-
Oidrelid=RelationGetRelid(rel);
2043-
ScanKeyDatakey;
2062+
List*oids=NIL;
2063+
ListCell*cell;
20442064
RelationfkeyRel;
20452065
SysScanDescfkeyScan;
20462066
HeapTupletuple;
20472067

20482068
/*
2049-
* Fast path: if the relation has no triggers, it surely has no FKs
2050-
* either.
2069+
* Build a list of OIDs of the interesting relations.
2070+
*
2071+
* If a relation has no triggers, then it can neither have FKs nor be
2072+
* referenced by a FK from another table, so we can ignore it.
20512073
*/
2052-
if (rel->rd_rel->reltriggers==0)
2074+
foreach(cell,relations)
2075+
{
2076+
Relationrel=lfirst(cell);
2077+
2078+
if (rel->rd_rel->reltriggers!=0)
2079+
oids=lappend_oid(oids,RelationGetRelid(rel));
2080+
}
2081+
2082+
/*
2083+
* Fast path: if no relation has triggers, none has FKs either.
2084+
*/
2085+
if (oids==NIL)
20532086
return;
20542087

20552088
/*
2056-
* Otherwise, must scan pg_constraint.Right now,this is a seqscan
2089+
* Otherwise, must scan pg_constraint.Right now,it is a seqscan
20572090
* because there is no available index on confrelid.
20582091
*/
20592092
fkeyRel=heap_openr(ConstraintRelationName,AccessShareLock);
20602093

2061-
ScanKeyInit(&key,
2062-
Anum_pg_constraint_confrelid,
2063-
BTEqualStrategyNumber,F_OIDEQ,
2064-
ObjectIdGetDatum(relid));
2065-
20662094
fkeyScan=systable_beginscan(fkeyRel,NULL, false,
2067-
SnapshotNow,1,&key);
2095+
SnapshotNow,0,NULL);
20682096

20692097
while (HeapTupleIsValid(tuple=systable_getnext(fkeyScan)))
20702098
{
20712099
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tuple);
20722100

2073-
if (con->contype==CONSTRAINT_FOREIGN&&con->conrelid!=relid)
2074-
ereport(ERROR,
2075-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2076-
errmsg("cannot truncate a table referenced in a foreign key constraint"),
2077-
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\".",
2078-
get_rel_name(con->conrelid),
2079-
RelationGetRelationName(rel),
2080-
NameStr(con->conname))));
2101+
/* Not a foreign key */
2102+
if (con->contype!=CONSTRAINT_FOREIGN)
2103+
continue;
2104+
2105+
/* Not for one of our list of tables */
2106+
if (!list_member_oid(oids,con->confrelid))
2107+
continue;
2108+
2109+
/* The referencer should be in our list too */
2110+
if (!list_member_oid(oids,con->conrelid))
2111+
{
2112+
if (tempTables)
2113+
ereport(ERROR,
2114+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2115+
errmsg("unsupported ON COMMIT and foreign key combination"),
2116+
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\", but they do not have the same ON COMMIT setting.",
2117+
get_rel_name(con->conrelid),
2118+
get_rel_name(con->confrelid),
2119+
NameStr(con->conname))));
2120+
else
2121+
ereport(ERROR,
2122+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2123+
errmsg("cannot truncate a table referenced in a foreign key constraint"),
2124+
errdetail("Table \"%s\" references \"%s\" via foreign key constraint \"%s\".",
2125+
get_rel_name(con->conrelid),
2126+
get_rel_name(con->confrelid),
2127+
NameStr(con->conname)),
2128+
errhint("Truncate table \"%s\" at the same time.",
2129+
get_rel_name(con->conrelid))));
2130+
}
20812131
}
20822132

20832133
systable_endscan(fkeyScan);

0 commit comments

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