Hackers,
This is my multi-table truncate patch. Includes doc changes and couple
new regression tests.
Note the following excerpt from the temp regression test:
+ BEGIN;
+ CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"temptest3_pkey" for table "temptest3"
+ CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
+ COMMIT;
+ ERROR: invalid ON COMMIT and foreign key combination
+ DETAIL: Table "temptest4" references "temptest3", but they don't have the
same ON COMMIT setting
I haven't investigated whether we can reject the foreign-key/on-commit
combination at create table time, rather than commit time. Maybe it's
worth doing, but then apparently on-commit-actions is not used a lot
(nobody saw the bug I previously reported).
Please review and consider for application.
Thanks,
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Las mujeres son como hondas: mientras más resistencia tienen,
más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
Index: doc/src/sgml/ref/truncate.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/ref/truncate.sgml,v
retrieving revision 1.17
diff -c -r1.17 truncate.sgml
*** doc/src/sgml/ref/truncate.sgml 23 Mar 2004 13:21:41 -0000 1.17
--- doc/src/sgml/ref/truncate.sgml 6 Nov 2004 01:20:19 -0000
***************
*** 11,17 ****
<refnamediv>
<refname>TRUNCATE</refname>
! <refpurpose>empty a table</refpurpose>
</refnamediv>
<indexterm zone="sql-truncate">
--- 11,17 ----
<refnamediv>
<refname>TRUNCATE</refname>
! <refpurpose>empty a set of tables</refpurpose>
</refnamediv>
<indexterm zone="sql-truncate">
***************
*** 20,26 ****
<refsynopsisdiv>
<synopsis>
! TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 20,26 ----
<refsynopsisdiv>
<synopsis>
! TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
</synopsis>
</refsynopsisdiv>
***************
*** 28,37 ****
<title>Description</title>
<para>
! <command>TRUNCATE</command> quickly removes all rows from a
! table. It has the same effect as an unqualified
! <command>DELETE</command> but since it does not actually scan the
! table it is faster. This is most useful on large tables.
</para>
</refsect1>
--- 28,37 ----
<title>Description</title>
<para>
! <command>TRUNCATE</command> quickly removes all rows from a set of
! tables. It has the same effect as an unqualified
! <command>DELETE</command> on each of them, but since it does not actually
! scan the tables it is faster. This is most useful on large tables.
</para>
</refsect1>
***************
*** 55,67 ****
<para>
<command>TRUNCATE</> cannot be used if there are foreign-key references
! to the table from other tables. Checking validity in such cases would
! require table scans, and the whole point is not to do one.
</para>
<para>
<command>TRUNCATE</> will not run any user-defined <literal>ON
! DELETE</literal> triggers that might exist for the table.
</para>
</refsect1>
--- 55,68 ----
<para>
<command>TRUNCATE</> cannot be used if there are foreign-key references
! to the table 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.
</para>
<para>
<command>TRUNCATE</> will not run any user-defined <literal>ON
! DELETE</literal> triggers that might exist for the tables.
</para>
</refsect1>
***************
*** 69,78 ****
<title>Examples</title>
<para>
! Truncate the table <literal>bigtable</literal>:
<programlisting>
! TRUNCATE TABLE bigtable;
</programlisting>
</para>
</refsect1>
--- 70,79 ----
<title>Examples</title>
<para>
! Truncate the tables <literal>bigtable</literal> and
<literal>fattable</literal>:
<programlisting>
! TRUNCATE TABLE bigtable, fattable;
</programlisting>
</para>
</refsect1>
Index: src/backend/catalog/heap.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.279
diff -c -r1.279 heap.c
*** src/backend/catalog/heap.c 10 Jan 2005 20:02:19 -0000 1.279
--- src/backend/catalog/heap.c 20 Jan 2005 19:30:51 -0000
***************
*** 1985,2083 ****
/*
* heap_truncate
*
! * This routine deletes all data within the specified relation.
*
* This is not transaction-safe! There is another, transaction-safe
! * implementation in commands/cluster.c. We now use this only for
* ON COMMIT truncation of temporary tables, where it doesn't matter.
*/
void
! heap_truncate(Oid rid)
{
! Relation rel;
! Oid toastrelid;
!
! /* Open relation for processing, and grab exclusive access on it. */
! rel = heap_open(rid, AccessExclusiveLock);
!
! /* Don't allow truncate on tables that are referenced by foreign keys */
! heap_truncate_check_FKs(rel);
/*
! * Release any buffers associated with this relation. If they're
! * dirty, they're just dropped without bothering to flush to disk.
*/
! DropRelationBuffers(rel);
! /* Now truncate the actual data */
! RelationTruncate(rel, 0);
! /* If this relation has indexes, truncate the indexes too */
! RelationTruncateIndexes(rid);
! /* If it has a toast table, recursively truncate that too */
! toastrelid = rel->rd_rel->reltoastrelid;
! if (OidIsValid(toastrelid))
! heap_truncate(toastrelid);
! /*
! * Close the relation, but keep exclusive lock on it until commit.
! */
! heap_close(rel, NoLock);
}
/*
* heap_truncate_check_FKs
! * Check for foreign keys referencing a relation that's to be
truncated
*
* We disallow such FKs (except self-referential ones) since the whole point
* of TRUNCATE is to not scan the individual rows to be thrown away.
*
* This is split out so it can be shared by both implementations of truncate.
! * Caller should already hold a suitable lock on the relation.
*/
void
! heap_truncate_check_FKs(Relation rel)
{
! Oid relid = RelationGetRelid(rel);
! ScanKeyData key;
Relation fkeyRel;
SysScanDesc fkeyScan;
HeapTuple tuple;
/*
! * Fast path: if the relation has no triggers, it surely has no FKs
! * either.
*/
! if (rel->rd_rel->reltriggers == 0)
return;
/*
! * Otherwise, must scan pg_constraint. Right now, this is a seqscan
* because there is no available index on confrelid.
*/
fkeyRel = heap_openr(ConstraintRelationName, AccessShareLock);
- ScanKeyInit(&key,
- Anum_pg_constraint_confrelid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(relid));
-
fkeyScan = systable_beginscan(fkeyRel, NULL, false,
! SnapshotNow,
1, &key);
while (HeapTupleIsValid(tuple = systable_getnext(fkeyScan)))
{
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
! if (con->contype == CONSTRAINT_FOREIGN && con->conrelid !=
relid)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot truncate a table
referenced in a foreign key constraint"),
! errdetail("Table \"%s\" references
\"%s\" via foreign key constraint \"%s\".",
!
get_rel_name(con->conrelid),
!
RelationGetRelationName(rel),
!
NameStr(con->conname))));
}
systable_endscan(fkeyScan);
--- 1985,2150 ----
/*
* heap_truncate
*
! * This routine deletes all data within all the specified relations.
*
* This is not transaction-safe! There is another, transaction-safe
! * implementation in commands/tablecmds.c. We now use this only for
* ON COMMIT truncation of temporary tables, where it doesn't matter.
*/
void
! heap_truncate(List *relids)
{
! List *relations = NIL;
! List *toasttables = NIL;
! ListCell *cell;
/*
! * Fast path when there's nothing to truncate
*/
! if (list_length(relids) == 0)
! return;
! foreach (cell, relids)
! {
! Oid rid = lfirst_oid(cell);
! Relation rel;
! /* Open relation for processing, and grab exclusive access on
it. */
! rel = heap_open(rid, AccessExclusiveLock);
! relations = lappend(relations, rel);
! }
! /* Don't allow truncate on tables that are referenced by foreign keys */
! heap_truncate_check_FKs(relations, true);
!
! foreach (cell, relations)
! {
! Relation rel = lfirst(cell);
! Oid toastrelid;
!
! /*
! * Release any buffers associated with this relation. If
they're
! * dirty, they're just dropped without bothering to flush to
disk.
! */
! DropRelationBuffers(rel);
!
! /* Now truncate the actual data */
! RelationTruncate(rel, 0);
!
! /* If this relation has indexes, truncate the indexes too */
! RelationTruncateIndexes(RelationGetRelid(rel));
!
! /*
! * If it has a toast table, schedule it for later truncation.
! * Note that we cannot just append it to the list being
processed,
! * because it's not open nor locked.
! */
! toastrelid = rel->rd_rel->reltoastrelid;
! if (OidIsValid(toastrelid))
! toasttables = lappend_oid(toasttables, toastrelid);
!
! /*
! * Close the relation, but keep exclusive lock on it until
commit.
! */
! heap_close(rel, NoLock);
! }
!
! /* now truncate TOAST tables */
! if (list_length(toasttables) > 0)
! heap_truncate(toasttables);
}
/*
* heap_truncate_check_FKs
! * Check for foreign keys referencing a list of relations that
! * have to be truncated
*
* We disallow such FKs (except self-referential ones) since the whole point
* of TRUNCATE is to not scan the individual rows to be thrown away.
*
* This is split out so it can be shared by both implementations of truncate.
! * Caller should already hold a suitable lock on the relations.
! *
! * tempTables is only used to produce the correct error message.
*/
void
! heap_truncate_check_FKs(List *relations, bool tempTables)
{
! List *rels = NIL;
! List *oids = NIL;
! ListCell *cell;
Relation fkeyRel;
SysScanDesc fkeyScan;
HeapTuple tuple;
/*
! * Get the list of involved relations and their Oids.
*/
! foreach(cell, relations)
! {
! Relation rel = lfirst(cell);
!
! /*
! * If a relation has no triggers, then it can't neither
! * have FKs nor be referenced by a FK from another table,
! * so skip it.
! */
! if (rel->rd_rel->reltriggers == 0)
! continue;
!
! rels = lappend(rels, rel);
! oids = lappend_oid(oids, RelationGetRelid(rel));
! }
!
! /*
! * Fast path: if no relation has triggers, none has FKs either.
! */
! if (list_length(rels) == 0)
return;
/*
! * Otherwise, must scan pg_constraint. Right now, it is a seqscan
* because there is no available index on confrelid.
*/
fkeyRel = heap_openr(ConstraintRelationName, AccessShareLock);
fkeyScan = systable_beginscan(fkeyRel, NULL, 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;
!
! /* not in our list of tables */
! if (! list_member_oid(oids, con->confrelid))
! continue;
!
! /* The referencer should be in our list too */
! if (! list_member_oid(oids, con->conrelid))
! {
! if (tempTables)
! ereport(ERROR,
!
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("invalid ON COMMIT and
foreign key combination"),
! errdetail("Table \"%s\"
references \"%s\", but they don't have the same ON COMMIT setting",
!
get_rel_name(con->conrelid),
!
get_rel_name(con->confrelid))));
! else
! ereport(ERROR,
!
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot truncate a
table referenced in a foreign key constraint"),
! errdetail("Table \"%s\"
references \"%s\" via foreign key constraint \"%s\".",
!
get_rel_name(con->conrelid),
!
get_rel_name(con->confrelid),
! NameStr(con->conname)),
! errhint("Truncate table \"%s\"
at the same time",
!
get_rel_name(con->conrelid))));
! }
}
systable_endscan(fkeyScan);
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.142
diff -c -r1.142 tablecmds.c
*** src/backend/commands/tablecmds.c 10 Jan 2005 20:02:20 -0000 1.142
--- src/backend/commands/tablecmds.c 20 Jan 2005 19:31:35 -0000
***************
*** 524,611 ****
}
/*
! * TruncateRelation
! * Removes all the rows from a relation.
*/
void
! TruncateRelation(const RangeVar *relation)
{
! Relation rel;
! Oid heap_relid;
! Oid toast_relid;
! /* Grab exclusive lock in preparation for truncate */
! rel = heap_openrv(relation, AccessExclusiveLock);
!
! /* Only allow truncate on regular tables */
! if (rel->rd_rel->relkind != RELKIND_RELATION)
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("\"%s\" is not a table",
! RelationGetRelationName(rel))));
! /* Permissions checks */
! if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
! RelationGetRelationName(rel));
! if (!allowSystemTableMods && IsSystemRelation(rel))
! ereport(ERROR,
! (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("permission denied: \"%s\" is a system
catalog",
! RelationGetRelationName(rel))));
!
! /*
! * We can never allow truncation of shared or nailed-in-cache
! * relations, because we can't support changing their relfilenode
! * values.
! */
! if (rel->rd_rel->relisshared || rel->rd_isnailed)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot truncate system relation
\"%s\"",
! RelationGetRelationName(rel))));
! /*
! * Don't allow truncate on temp tables of other backends ... their
! * local buffer manager is not going to cope.
! */
! if (isOtherTempNamespace(RelationGetNamespace(rel)))
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot truncate temporary tables of other
sessions")));
! /*
! * Don't allow truncate on tables which are referenced by foreign keys
! */
! heap_truncate_check_FKs(rel);
! /*
! * Okay, here we go: create a new empty storage file for the relation,
! * and assign it as the relfilenode value. The old storage file is
! * scheduled for deletion at commit.
! */
! setNewRelfilenode(rel);
! heap_relid = RelationGetRelid(rel);
! toast_relid = rel->rd_rel->reltoastrelid;
! heap_close(rel, NoLock);
/*
! * The same for the toast table, if any.
*/
! if (OidIsValid(toast_relid))
{
! rel = relation_open(toast_relid, AccessExclusiveLock);
setNewRelfilenode(rel);
heap_close(rel, NoLock);
- }
! /*
! * Reconstruct the indexes to match, and we're done.
! */
! reindex_relation(heap_relid, true);
}
/*----------
--- 524,633 ----
}
/*
! * 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.
*/
void
! ExecuteTruncate(List *relations)
{
! List *rels = NIL;
! ListCell *cell;
! foreach (cell, relations)
! {
! RangeVar *rv = lfirst(cell);
! Relation rel;
! /* Grab exclusive lock in preparation for truncate */
! rel = heap_openrv(rv, AccessExclusiveLock);
! /* Only allow truncate on regular tables */
! if (rel->rd_rel->relkind != RELKIND_RELATION)
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("\"%s\" is not a table",
!
RelationGetRelationName(rel))));
! /* Permissions checks */
! if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
! aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
! RelationGetRelationName(rel));
! if (!allowSystemTableMods && IsSystemRelation(rel))
! ereport(ERROR,
!
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("permission denied: \"%s\" is a
system catalog",
!
RelationGetRelationName(rel))));
! /*
! * We can never allow truncation of shared or nailed-in-cache
! * relations, because we can't support changing their
relfilenode
! * values.
! */
! if (rel->rd_rel->relisshared || rel->rd_isnailed)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot truncate system
relation \"%s\"",
!
RelationGetRelationName(rel))));
! /*
! * Don't allow truncate on temp tables of other backends ...
their
! * local buffer manager is not going to cope.
! */
! if (isOtherTempNamespace(RelationGetNamespace(rel)))
! 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.
*/
! heap_truncate_check_FKs(rels, false);
!
! foreach (cell, rels)
{
! Relation rel = lfirst(cell);
! Oid heap_relid;
! Oid toast_relid;
!
! /*
! * Create a new empty storage file for the relation, and assign
it as
! * the relfilenode value. The old storage file is
scheduled for
! * deletion at commit.
! */
setNewRelfilenode(rel);
+
+ heap_relid = RelationGetRelid(rel);
+ toast_relid = rel->rd_rel->reltoastrelid;
+
heap_close(rel, NoLock);
! /*
! * The same for the toast table, if any.
! */
! if (OidIsValid(toast_relid))
! {
! rel = relation_open(toast_relid, AccessExclusiveLock);
! setNewRelfilenode(rel);
! heap_close(rel, NoLock);
! }
!
! /*
! * Reconstruct the indexes to match, and we're done.
! */
! reindex_relation(heap_relid, true);
! }
}
/*----------
***************
*** 5984,5989 ****
--- 6006,6012 ----
PreCommit_on_commit_actions(void)
{
ListCell *l;
+ List *oids_to_truncate = NIL;
foreach(l, on_commits)
{
***************
*** 6000,6007 ****
/* Do nothing (there shouldn't be such entries,
actually) */
break;
case ONCOMMIT_DELETE_ROWS:
! heap_truncate(oc->relid);
! CommandCounterIncrement(); /* XXX
needed? */
break;
case ONCOMMIT_DROP:
{
--- 6023,6029 ----
/* Do nothing (there shouldn't be such entries,
actually) */
break;
case ONCOMMIT_DELETE_ROWS:
! oids_to_truncate =
lappend_oid(oids_to_truncate, oc->relid);
break;
case ONCOMMIT_DROP:
{
***************
*** 6022,6027 ****
--- 6044,6052 ----
}
}
}
+ if (list_length(oids_to_truncate) > 0)
+ heap_truncate(oids_to_truncate);
+ CommandCounterIncrement(); /* XXX needed?
*/
}
/*
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.295
diff -c -r1.295 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 31 Dec 2004 21:59:55 -0000 1.295
--- src/backend/nodes/copyfuncs.c 10 Jan 2005 23:29:34 -0000
***************
*** 1815,1821 ****
{
TruncateStmt *newnode = makeNode(TruncateStmt);
! COPY_NODE_FIELD(relation);
return newnode;
}
--- 1815,1821 ----
{
TruncateStmt *newnode = makeNode(TruncateStmt);
! COPY_NODE_FIELD(relations);
return newnode;
}
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.234
diff -c -r1.234 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 31 Dec 2004 21:59:55 -0000 1.234
--- src/backend/nodes/equalfuncs.c 10 Jan 2005 23:29:35 -0000
***************
*** 891,897 ****
static bool
_equalTruncateStmt(TruncateStmt *a, TruncateStmt *b)
{
! COMPARE_NODE_FIELD(relation);
return true;
}
--- 891,897 ----
static bool
_equalTruncateStmt(TruncateStmt *a, TruncateStmt *b)
{
! COMPARE_NODE_FIELD(relations);
return true;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.481
diff -c -r2.481 gram.y
*** src/backend/parser/gram.y 31 Dec 2004 22:00:27 -0000 2.481
--- src/backend/parser/gram.y 10 Jan 2005 23:29:35 -0000
***************
*** 2681,2695 ****
/*****************************************************************************
*
* QUERY:
! * truncate table relname
*
*****************************************************************************/
TruncateStmt:
! TRUNCATE opt_table qualified_name
{
TruncateStmt *n =
makeNode(TruncateStmt);
! n->relation = $3;
$$ = (Node *)n;
}
;
--- 2681,2695 ----
/*****************************************************************************
*
* QUERY:
! * truncate table relname1, relname2, ...
*
*****************************************************************************/
TruncateStmt:
! TRUNCATE opt_table qualified_name_list
{
TruncateStmt *n =
makeNode(TruncateStmt);
! n->relations = $3;
$$ = (Node *)n;
}
;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.231
diff -c -r1.231 utility.c
*** src/backend/tcop/utility.c 31 Dec 2004 22:01:16 -0000 1.231
--- src/backend/tcop/utility.c 10 Jan 2005 23:29:38 -0000
***************
*** 575,581 ****
{
TruncateStmt *stmt = (TruncateStmt *) parsetree;
! TruncateRelation(stmt->relation);
}
break;
--- 575,581 ----
{
TruncateStmt *stmt = (TruncateStmt *) parsetree;
! ExecuteTruncate(stmt->relations);
}
break;
Index: src/include/catalog/heap.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/heap.h,v
retrieving revision 1.72
diff -c -r1.72 heap.h
*** src/include/catalog/heap.h 31 Dec 2004 22:03:24 -0000 1.72
--- src/include/catalog/heap.h 20 Jan 2005 19:31:10 -0000
***************
*** 56,64 ****
extern void heap_drop_with_catalog(Oid relid);
! extern void heap_truncate(Oid rid);
! extern void heap_truncate_check_FKs(Relation rel);
extern List *AddRelationRawConstraints(Relation rel,
List *rawColDefaults,
--- 56,64 ----
extern void heap_drop_with_catalog(Oid relid);
! extern void heap_truncate(List *relids);
! extern void heap_truncate_check_FKs(List *relations, bool tempTables);
extern List *AddRelationRawConstraints(Relation rel,
List *rawColDefaults,
Index: src/include/commands/tablecmds.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/commands/tablecmds.h,v
retrieving revision 1.21
diff -c -r1.21 tablecmds.h
*** src/include/commands/tablecmds.h 31 Dec 2004 22:03:28 -0000 1.21
--- src/include/commands/tablecmds.h 10 Jan 2005 23:29:46 -0000
***************
*** 27,33 ****
extern void AlterTableCreateToastTable(Oid relOid, bool silent);
! extern void TruncateRelation(const RangeVar *relation);
extern void renameatt(Oid myrelid,
const char *oldattname,
--- 27,33 ----
extern void AlterTableCreateToastTable(Oid relOid, bool silent);
! extern void ExecuteTruncate(List *relations);
extern void renameatt(Oid myrelid,
const char *oldattname,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.271
diff -c -r1.271 parsenodes.h
*** src/include/nodes/parsenodes.h 31 Dec 2004 22:03:34 -0000 1.271
--- src/include/nodes/parsenodes.h 10 Jan 2005 23:29:47 -0000
***************
*** 1283,1289 ****
typedef struct TruncateStmt
{
NodeTag type;
! RangeVar *relation; /* relation to be truncated */
} TruncateStmt;
/* ----------------------
--- 1283,1289 ----
typedef struct TruncateStmt
{
NodeTag type;
! List *relations; /* relations (RangeVars) to be
truncated */
} TruncateStmt;
/* ----------------------
Index: src/test/regress/expected/temp.out
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/expected/temp.out,v
retrieving revision 1.9
diff -c -r1.9 temp.out
*** src/test/regress/expected/temp.out 25 Sep 2003 06:58:06 -0000 1.9
--- src/test/regress/expected/temp.out 23 Jan 2005 21:42:34 -0000
***************
*** 82,84 ****
--- 82,111 ----
-- ON COMMIT is only allowed for TEMP
CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
ERROR: ON COMMIT can only be used on temporary tables
+ -- Test foreign keys
+ BEGIN;
+ CREATE TEMP TABLE temptest1(col int PRIMARY KEY);
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"temptest1_pkey" for table "temptest1"
+ CREATE TEMP TABLE temptest2(col int REFERENCES temptest1)
+ ON COMMIT DELETE ROWS;
+ INSERT INTO temptest1 VALUES (1);
+ INSERT INTO temptest2 VALUES (1);
+ COMMIT;
+ SELECT * FROM temptest1;
+ col
+ -----
+ 1
+ (1 row)
+
+ SELECT * FROM temptest2;
+ col
+ -----
+ (0 rows)
+
+ BEGIN;
+ CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"temptest3_pkey" for table "temptest3"
+ CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
+ COMMIT;
+ ERROR: invalid ON COMMIT and foreign key combination
+ DETAIL: Table "temptest4" references "temptest3", but they don't have the
same ON COMMIT setting
Index: src/test/regress/expected/truncate.out
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/expected/truncate.out,v
retrieving revision 1.9
diff -c -r1.9 truncate.out
*** src/test/regress/expected/truncate.out 10 Jun 2004 17:56:01 -0000
1.9
--- src/test/regress/expected/truncate.out 6 Nov 2004 04:43:24 -0000
***************
*** 30,52 ****
------
(0 rows)
! -- Test foreign constraint check
! CREATE TABLE truncate_b(col1 integer references truncate_a);
INSERT INTO truncate_a VALUES (1);
! SELECT * FROM truncate_a;
! col1
! ------
! 1
! (1 row)
!
! TRUNCATE truncate_a;
ERROR: cannot truncate a table referenced in a foreign key constraint
! DETAIL: Table "truncate_b" references "truncate_a" via foreign key
constraint "truncate_b_col1_fkey".
! SELECT * FROM truncate_a;
col1
------
! 1
! (1 row)
! DROP TABLE truncate_b;
! DROP TABLE truncate_a;
--- 30,113 ----
------
(0 rows)
! -- Test foreign-key checks
! CREATE TABLE trunc_b (a int REFERENCES truncate_a);
! CREATE TABLE trunc_c (a serial PRIMARY KEY);
! NOTICE: CREATE TABLE will create implicit sequence "trunc_c_a_seq" for
serial column "trunc_c.a"
! NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "trunc_c_pkey"
for table "trunc_c"
! CREATE TABLE trunc_d (a int REFERENCES trunc_c);
! CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
! 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
! 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
! 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
! 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
! 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
! 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
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
! -- circular references
! ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
! -- Add some data to verify that truncating actually works ...
! 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;
! 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
! 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
! 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
! 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
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
! -- Verify that truncating did actually work
! 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
! NOTICE: drop cascades to constraint trunc_e_b_fkey on table trunc_e
! NOTICE: drop cascades to constraint trunc_d_a_fkey on table trunc_d
Index: src/test/regress/sql/temp.sql
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/sql/temp.sql,v
retrieving revision 1.5
diff -c -r1.5 temp.sql
*** src/test/regress/sql/temp.sql 14 May 2003 03:26:03 -0000 1.5
--- src/test/regress/sql/temp.sql 23 Jan 2005 21:40:28 -0000
***************
*** 83,85 ****
--- 83,101 ----
-- ON COMMIT is only allowed for TEMP
CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
+
+ -- Test foreign keys
+ BEGIN;
+ CREATE TEMP TABLE temptest1(col int PRIMARY KEY);
+ CREATE TEMP TABLE temptest2(col int REFERENCES temptest1)
+ ON COMMIT DELETE ROWS;
+ INSERT INTO temptest1 VALUES (1);
+ INSERT INTO temptest2 VALUES (1);
+ COMMIT;
+ SELECT * FROM temptest1;
+ SELECT * FROM temptest2;
+
+ BEGIN;
+ CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS;
+ CREATE TEMP TABLE temptest4(col int REFERENCES temptest3);
+ COMMIT;
Index: src/test/regress/sql/truncate.sql
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/test/regress/sql/truncate.sql,v
retrieving revision 1.2
diff -c -r1.2 truncate.sql
*** src/test/regress/sql/truncate.sql 23 Nov 2002 04:05:52 -0000 1.2
--- src/test/regress/sql/truncate.sql 6 Nov 2004 04:42:24 -0000
***************
*** 14,25 ****
COMMIT;
SELECT * FROM truncate_a;
! -- Test foreign constraint check
! CREATE TABLE truncate_b(col1 integer references truncate_a);
INSERT INTO truncate_a VALUES (1);
! SELECT * FROM truncate_a;
! TRUNCATE truncate_a;
! SELECT * FROM truncate_a;
! DROP TABLE truncate_b;
! DROP TABLE truncate_a;
--- 14,58 ----
COMMIT;
SELECT * FROM truncate_a;
! -- Test foreign-key checks
! CREATE TABLE trunc_b (a int REFERENCES truncate_a);
! CREATE TABLE trunc_c (a serial PRIMARY KEY);
! CREATE TABLE trunc_d (a int REFERENCES trunc_c);
! CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
!
! TRUNCATE TABLE truncate_a; -- fail
! TRUNCATE TABLE truncate_a,trunc_b; -- fail
! TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok
! TRUNCATE TABLE truncate_a,trunc_e; -- fail
! TRUNCATE TABLE trunc_c; -- fail
! TRUNCATE TABLE trunc_c,trunc_d; -- fail
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok
!
! -- circular references
! ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
!
! -- Add some data to verify that truncating actually works ...
! 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;
! TRUNCATE TABLE trunc_c,trunc_d;
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e;
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;
! TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;
!
! -- Verify that truncating did actually work
! 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 6: Have you searched our list archives?
http://archives.postgresql.org
