The proposed patch implements TRUNCATE ... CASCADE:

* %Allow TRUNCATE ... CASCADE/RESTRICT
  This is like DELETE CASCADE, but truncates.

The patch also adds a function makeRangeVarFromRelId() to namespace.c that I
thought would be useful. I hope I didn't overlook something similar that
exists already.


Joachim
diff -cr 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-02 
08:54:29.000000000 +0100
***************
*** 20,26 ****
  
   <refsynopsisdiv>
  <synopsis>
! TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...]
  </synopsis>
   </refsynopsisdiv>
  
--- 20,26 ----
  
   <refsynopsisdiv>
  <synopsis>
! TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] 
[ CASCADE | RESTRICT ]
  </synopsis>
   </refsynopsisdiv>
  
***************
*** 59,67 ****
  
    <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.
    </para>
  
    <para>
--- 59,68 ----
  
    <para>
     <command>TRUNCATE</> cannot be used on a table that has foreign-key
!    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,87 ****
  TRUNCATE TABLE bigtable, fattable;
  </programlisting>
    </para>
   </refsect1>
!  
   <refsect1>
    <title>Compatibility</title>
  
--- 81,97 ----
  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 -cr 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-02 08:54:29.000000000 
+0100
***************
*** 2066,2072 ****
                                                                   
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))));
                }
        }
--- 2066,2072 ----
                                                                   
get_rel_name(con->conrelid),
                                                                   
get_rel_name(con->confrelid),
                                                                   
NameStr(con->conname)),
!                                                errhint("Truncate table \"%s\" 
at the same time or use TRUNCATE ... CASCADE.",
                                                                 
get_rel_name(con->conrelid))));
                }
        }
diff -cr cvs/pgsql/src/backend/catalog/namespace.c 
cvs.build/pgsql/src/backend/catalog/namespace.c
*** cvs/pgsql/src/backend/catalog/namespace.c   2005-11-22 19:17:08.000000000 
+0100
--- cvs.build/pgsql/src/backend/catalog/namespace.c     2006-02-02 
09:21:20.000000000 +0100
***************
*** 1379,1384 ****
--- 1379,1409 ----
        return rel;
  }
  
+ /* makeRangeVarFromRelId
+  *            Utility routine to get a RangeVar variable from a RelId Oid
+  *
+  *            If the relation is not found, return NULL if failOK = true,
+  *            otherwise raise an error.
+  */
+ RangeVar *
+ makeRangeVarFromRelId(Oid relId, bool failOK)
+ {
+       char   *namespaceName;
+       char   *relName;
+       Oid             namespaceId = get_rel_namespace(relId);
+ 
+       if (!OidIsValid(namespaceId))
+               if (failOK)
+                       return NULL;
+               else
+                       ereport(ERROR,
+                                       (errcode(ERRCODE_UNDEFINED_TABLE),
+                                        errmsg("relation with OID %d does not 
exist", relId)));
+       namespaceName = get_namespace_name(namespaceId);
+       relName = get_rel_name(relId);
+       return makeRangeVar(namespaceName, relName);
+ }
+ 
  /*
   * NameListToString
   *            Utility routine to convert a qualified-name list into a string.
diff -cr 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-02 
09:40:15.000000000 +0100
***************
*** 523,528 ****
--- 523,590 ----
        performDeletion(&object, behavior);
  }
  
+ /* This function is essentially copied from heap_truncate_check_FKs but I
+  * decided not to make them share the code because heap_truncate_check_FKs
+  * needs quite a lot of information for the error message and this function
+  * just returns an OID list.
+  *
+  * We look here for Relations referencing one of the Relations in the
+  * oids list. We also pass the list of Relations we have already as
+  * found_earlier. This function will be called until no more new OID is
+  * found.
+  *
+  * What gets found in one run will appear in oids in the next call.
+  */
+ static
+ List* BuildReferencingRelationList(List* oids, List* found_earlier)
+ {
+       List       *referencingRels = NIL;
+       Relation        fkeyRel;
+       SysScanDesc fkeyScan;
+       HeapTuple       tuple;
+ 
+       /*
+        * 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? We also check for the 
list
+                * we're constructing here as a kind of optimization. If we see 
the
+                * constraint for b referencing c first during the seqscan and 
then
+                * later the one for a referencing b, we can both it in the 
same scan
+                */
+               if (!list_member_oid(oids, con->confrelid) &&
+                       !list_member_oid(referencingRels, con->confrelid))
+                       continue;
+ 
+               /*
+                * Found a referencer that has to be truncated as well, add it 
to the
+                * list
+                */
+               if (!list_member_oid(oids, con->conrelid) &&
+                       !list_member_oid(found_earlier, con->conrelid))
+                       referencingRels = lappend_oid(referencingRels, 
con->conrelid);
+       }
+ 
+       systable_endscan(fkeyScan);
+       heap_close(fkeyRel, AccessShareLock);
+ 
+       return referencingRels;
+ }
+ 
+ 
  /*
   * ExecuteTruncate
   *            Executes a TRUNCATE command.
***************
*** 534,550 ****
   * 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);
  
--- 596,670 ----
   * are all internal to the group that's being truncated.  Finally all
   * relations are truncated and reindexed.
   */
+ 
  void
! ExecuteTruncate(List *relations, DropBehavior behavior)
  {
        List       *rels = NIL;
        ListCell   *cell;
+       RangeVar   *rv;
+       List       *directRels = NIL;
+       Oid                     relid;
+ 
+       if (behavior == DROP_CASCADE)
+       {
+               List       *cascadedRels = NIL;
+               List       *newCascadedRels = NIL;
+ 
+               /* build list of OIDs from RangeVars first */
+               foreach(cell, relations)
+               {
+                       /* false = failure is not ok */
+                       relid = RangeVarGetRelid(lfirst(cell), false);
+                       cascadedRels = lappend_oid(cascadedRels, relid);
+                       directRels = lappend_oid(directRels, relid);
+               }
+ 
+               /*
+                * - cascadedRels 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.
+                */
+               newCascadedRels = cascadedRels;
+               do {
+                       /*
+                        * Only extend the list further with respect to what 
we've found in
+                        * the last run (newCascadedRels). Pass the complete 
list of
+                        * affected OIDs as well such that the function can 
check what we
+                        * already have.
+                        */
+                       newCascadedRels = 
BuildReferencingRelationList(newCascadedRels,
+                                                                               
                                   cascadedRels);
+                       cascadedRels = list_union_oid(cascadedRels, 
newCascadedRels);
+               } while (list_length(newCascadedRels) > 0);
+ 
+               /* add newly found relation OIDs to the relation list as 
RangeVars */
+               foreach(cell, cascadedRels)
+               {
+                       relid = lfirst_oid(cell);
+                       rv = makeRangeVarFromRelId(relid, false);
+                       relations = lappend(relations, rv);
+               }
+       }
  
        foreach(cell, relations)
        {
                Relation        rel;
  
+               rv = lfirst(cell);
+               relid = RangeVarGetRelid(rv, false);
+               if (behavior == DROP_CASCADE && !list_member_oid(directRels, 
relid))
+                       /*
+                        * We are about to execute a cascaded truncate, display 
the message
+                        * 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 beeing truncated which could be 
confusing
+                        */
+                       ereport(NOTICE,
+                                       (errmsg("truncate cascades to table 
\"%s\"",
+                                                       rv->relname)));
+ 
                /* Grab exclusive lock in preparation for truncate */
                rel = heap_openrv(rv, AccessExclusiveLock);
  
***************
*** 591,599 ****
        }
  
        /*
!        * Check foreign key references.
         */
!       heap_truncate_check_FKs(rels, false);
  
        /*
         * OK, truncate each table.
--- 711,720 ----
        }
  
        /*
!        * Check foreign key references if not in cascade mode.
         */
!       if (behavior == DROP_RESTRICT)
!               heap_truncate_check_FKs(rels, false);
  
        /*
         * OK, truncate each table.
diff -cr cvs/pgsql/src/backend/parser/gram.y 
cvs.build/pgsql/src/backend/parser/gram.y
*** cvs/pgsql/src/backend/parser/gram.y 2006-02-02 08:52:31.000000000 +0100
--- cvs.build/pgsql/src/backend/parser/gram.y   2006-02-02 08:54:29.000000000 
+0100
***************
*** 2914,2923 ****
   
*****************************************************************************/
  
  TruncateStmt:
!                       TRUNCATE opt_table qualified_name_list
                                {
                                        TruncateStmt *n = 
makeNode(TruncateStmt);
                                        n->relations = $3;
                                        $$ = (Node *)n;
                                }
                ;
--- 2914,2924 ----
   
*****************************************************************************/
  
  TruncateStmt:
!                       TRUNCATE opt_table qualified_name_list opt_drop_behavior
                                {
                                        TruncateStmt *n = 
makeNode(TruncateStmt);
                                        n->relations = $3;
+                                       n->behavior = $4;
                                        $$ = (Node *)n;
                                }
                ;
diff -cr 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-02 08:54:29.000000000 
+0100
***************
*** 631,637 ****
                        {
                                TruncateStmt *stmt = (TruncateStmt *) parsetree;
  
!                               ExecuteTruncate(stmt->relations);
                        }
                        break;
  
--- 631,637 ----
                        {
                                TruncateStmt *stmt = (TruncateStmt *) parsetree;
  
!                               ExecuteTruncate(stmt->relations, 
stmt->behavior);
                        }
                        break;
  
diff -cr cvs/pgsql/src/bin/psql/tab-complete.c 
cvs.build/pgsql/src/bin/psql/tab-complete.c
*** cvs/pgsql/src/bin/psql/tab-complete.c       2006-01-11 10:46:08.000000000 
+0100
--- cvs.build/pgsql/src/bin/psql/tab-complete.c 2006-02-02 08:54:29.000000000 
+0100
***************
*** 1686,1692 ****
  /* TRUNCATE */
        else if (pg_strcasecmp(prev_wd, "TRUNCATE") == 0)
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
! 
  /* UNLISTEN */
        else if (pg_strcasecmp(prev_wd, "UNLISTEN") == 0)
                COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(relname) 
FROM pg_catalog.pg_listener WHERE 
substring(pg_catalog.quote_ident(relname),1,%d)='%s' UNION SELECT '*'");
--- 1686,1699 ----
  /* TRUNCATE */
        else if (pg_strcasecmp(prev_wd, "TRUNCATE") == 0)
                COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
!       else if (pg_strcasecmp(prev2_wd, "TRUNCATE") == 0 ||
!                        (pg_strcasecmp(prev3_wd, "TRUNCATE") == 0 &&
!                         pg_strcasecmp(prev2_wd, "TABLE") == 0))
!       {
!               static const char *const list_DROPCR[] =
!                       {"CASCADE", "RESTRICT", NULL};
!               COMPLETE_WITH_LIST(list_DROPCR);
!       }
  /* UNLISTEN */
        else if (pg_strcasecmp(prev_wd, "UNLISTEN") == 0)
                COMPLETE_WITH_QUERY("SELECT pg_catalog.quote_ident(relname) 
FROM pg_catalog.pg_listener WHERE 
substring(pg_catalog.quote_ident(relname),1,%d)='%s' UNION SELECT '*'");
diff -cr cvs/pgsql/src/include/catalog/namespace.h 
cvs.build/pgsql/src/include/catalog/namespace.h
*** cvs/pgsql/src/include/catalog/namespace.h   2005-10-15 04:49:42.000000000 
+0200
--- cvs.build/pgsql/src/include/catalog/namespace.h     2006-02-02 
09:09:41.000000000 +0100
***************
*** 77,82 ****
--- 77,83 ----
  extern Oid    LookupCreationNamespace(const char *nspname);
  extern Oid    QualifiedNameGetCreationNamespace(List *names, char 
**objname_p);
  extern RangeVar *makeRangeVarFromNameList(List *names);
+ extern RangeVar *makeRangeVarFromRelId(Oid relId, bool failOK);
  extern char *NameListToString(List *names);
  extern char *NameListToQuotedString(List *names);
  
diff -cr 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-02 
08:54:29.000000000 +0100
***************
*** 36,42 ****
                                                           Oid oldNspOid, Oid 
newNspOid,
                                                           bool hasDependEntry);
  
! extern void ExecuteTruncate(List *relations);
  
  extern void renameatt(Oid myrelid,
                  const char *oldattname,
--- 36,42 ----
                                                           Oid oldNspOid, Oid 
newNspOid,
                                                           bool hasDependEntry);
  
! extern void ExecuteTruncate(List *relations, DropBehavior behavior);
  
  extern void renameatt(Oid myrelid,
                  const char *oldattname,
diff -cr cvs/pgsql/src/include/nodes/parsenodes.h 
cvs.build/pgsql/src/include/nodes/parsenodes.h
*** cvs/pgsql/src/include/nodes/parsenodes.h    2006-01-23 23:52:37.000000000 
+0100
--- cvs.build/pgsql/src/include/nodes/parsenodes.h      2006-02-02 
08:54:29.000000000 +0100
***************
*** 1308,1313 ****
--- 1308,1314 ----
  {
        NodeTag         type;
        List       *relations;          /* relations (RangeVars) to be 
truncated */
+       DropBehavior behavior;          /* RESTRICT or CASCADE behavior */
  } TruncateStmt;
  
  /* ----------------------
diff -cr 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-02 
08:54:29.000000000 +0100
***************
*** 40,69 ****
  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 ...
--- 40,76 ----
  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 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 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 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 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 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 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,93 ****
  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
--- 82,100 ----
  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 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 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 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 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,111 ****
--- 113,145 ----
  ---+---
  (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 -cr 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-02 
08:54:29.000000000 +0100
***************
*** 30,35 ****
--- 30,38 ----
  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,58 ****
--- 58,79 ----
   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

Reply via email to