Re: about truncate - Mailing list pgsql-hackers

Tom Lane wrote:
> +1 for making TRUNCATE and LOCK support ONLY.

Patch attached.

> I don't care much about
> ALTER TABLE SET SCHEMA, but perhaps there's a use-case for recursion
> on that.

I have added this to the Todo list for later reconsideration.

Index: doc/src/sgml/ref/lock.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/lock.sgml,v
retrieving revision 1.51
diff -u -3 -p -c -r1.51 lock.sgml
*** doc/src/sgml/ref/lock.sgml    14 Nov 2008 10:22:47 -0000    1.51
--- doc/src/sgml/ref/lock.sgml    8 Jan 2009 13:27:47 -0000
*************** PostgreSQL documentation
*** 21,27 ****

   <refsynopsisdiv>
  <synopsis>
! LOCK [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable
class="PARAMETER">lockmode</replaceable>MODE ] [ NOWAIT ] 

  where <replaceable class="PARAMETER">lockmode</replaceable> is one of:

--- 21,27 ----

   <refsynopsisdiv>
  <synopsis>
! LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable
class="PARAMETER">lockmode</replaceable>MODE ] [ NOWAIT ] 

  where <replaceable class="PARAMETER">lockmode</replaceable> is one of:

*************** where <replaceable class="PARAMETER">loc
*** 109,115 ****
      <listitem>
       <para>
        The name (optionally schema-qualified) of an existing table to
!       lock.
       </para>

       <para>
--- 109,117 ----
      <listitem>
       <para>
        The name (optionally schema-qualified) of an existing table to
!       lock.  If <literal>ONLY</> is specified, only that table is
!       locked.  If <literal>ONLY</> is not specified, the table and all
!       its descendant tables (if any) are locked.
       </para>

       <para>
Index: doc/src/sgml/ref/truncate.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/truncate.sgml,v
retrieving revision 1.31
diff -u -3 -p -c -r1.31 truncate.sgml
*** doc/src/sgml/ref/truncate.sgml    18 Dec 2008 10:45:00 -0000    1.31
--- doc/src/sgml/ref/truncate.sgml    8 Jan 2009 13:27:47 -0000
*************** PostgreSQL documentation
*** 21,27 ****

   <refsynopsisdiv>
  <synopsis>
! TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
      [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
  </synopsis>
   </refsynopsisdiv>
--- 21,27 ----

   <refsynopsisdiv>
  <synopsis>
! TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
      [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
  </synopsis>
   </refsynopsisdiv>
*************** TRUNCATE [ TABLE ] <replaceable class="P
*** 47,53 ****
      <term><replaceable class="PARAMETER">name</replaceable></term>
      <listitem>
       <para>
!       The name (optionally schema-qualified) of a table to be truncated.
       </para>
      </listitem>
     </varlistentry>
--- 47,56 ----
      <term><replaceable class="PARAMETER">name</replaceable></term>
      <listitem>
       <para>
!       The name (optionally schema-qualified) of a table to be
!       truncated.  If <literal>ONLY</> is specified, only that table is
!       truncated.  If <literal>ONLY</> is not specified, the table and
!       all its descendant tables (if any) are truncated.
       </para>
      </listitem>
     </varlistentry>
Index: src/backend/commands/lockcmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/lockcmds.c,v
retrieving revision 1.20
diff -u -3 -p -c -r1.20 lockcmds.c
*** src/backend/commands/lockcmds.c    1 Jan 2009 17:23:38 -0000    1.20
--- src/backend/commands/lockcmds.c    8 Jan 2009 13:27:47 -0000
***************
*** 18,23 ****
--- 18,25 ----
  #include "catalog/namespace.h"
  #include "commands/lockcmds.h"
  #include "miscadmin.h"
+ #include "optimizer/prep.h"
+ #include "parser/parse_clause.h"
  #include "utils/acl.h"
  #include "utils/lsyscache.h"
  #include "utils/rel.h"
*************** LockTableCommand(LockStmt *lockstmt)
*** 40,77 ****
      {
          RangeVar   *relation = lfirst(p);
          Oid            reloid;
!         AclResult    aclresult;
!         Relation    rel;

-         /*
-          * We don't want to open the relation until we've checked privilege.
-          * So, manually get the relation OID.
-          */
          reloid = RangeVarGetRelid(relation, false);

!         if (lockstmt->mode == AccessShareLock)
!             aclresult = pg_class_aclcheck(reloid, GetUserId(),
!                                           ACL_SELECT);
          else
!             aclresult = pg_class_aclcheck(reloid, GetUserId(),
!                                           ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE);

!         if (aclresult != ACLCHECK_OK)
!             aclcheck_error(aclresult, ACL_KIND_CLASS,
!                            get_rel_name(reloid));

!         if (lockstmt->nowait)
!             rel = relation_open_nowait(reloid, lockstmt->mode);
!         else
!             rel = relation_open(reloid, lockstmt->mode);
!
!         /* Currently, we only allow plain tables to be locked */
!         if (rel->rd_rel->relkind != RELKIND_RELATION)
!             ereport(ERROR,
!                     (errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                      errmsg("\"%s\" is not a table",
!                             relation->relname)));
!
!         relation_close(rel, NoLock);    /* close rel, keep lock */
      }
  }
--- 42,89 ----
      {
          RangeVar   *relation = lfirst(p);
          Oid            reloid;
!         bool        recurse = interpretInhOption(relation->inhOpt);
!         List       *children_and_self;
!         ListCell   *child;

          reloid = RangeVarGetRelid(relation, false);

!         if (recurse)
!             children_and_self = find_all_inheritors(reloid);
          else
!             children_and_self = list_make1_oid(reloid);

!         foreach(child, children_and_self)
!         {
!             Oid            childreloid = lfirst_oid(child);
!             Relation    rel;
!             AclResult    aclresult;
!
!             /* We don't want to open the relation until we've checked privilege. */
!             if (lockstmt->mode == AccessShareLock)
!                 aclresult = pg_class_aclcheck(childreloid, GetUserId(),
!                                               ACL_SELECT);
!             else
!                 aclresult = pg_class_aclcheck(childreloid, GetUserId(),
!                                               ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE);
!
!             if (aclresult != ACLCHECK_OK)
!                 aclcheck_error(aclresult, ACL_KIND_CLASS,
!                                get_rel_name(childreloid));
!
!             if (lockstmt->nowait)
!                 rel = relation_open_nowait(childreloid, lockstmt->mode);
!             else
!                 rel = relation_open(childreloid, lockstmt->mode);
!
!             /* Currently, we only allow plain tables to be locked */
!             if (rel->rd_rel->relkind != RELKIND_RELATION)
!                 ereport(ERROR,
!                         (errcode(ERRCODE_WRONG_OBJECT_TYPE),
!                          errmsg("\"%s\" is not a table",
!                                 get_rel_name(childreloid))));

!             relation_close(rel, NoLock);    /* close rel, keep lock */
!         }
      }
  }
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.276
diff -u -3 -p -c -r1.276 tablecmds.c
*** src/backend/commands/tablecmds.c    1 Jan 2009 17:23:39 -0000    1.276
--- src/backend/commands/tablecmds.c    8 Jan 2009 13:27:47 -0000
*************** ExecuteTruncate(TruncateStmt *stmt)
*** 772,788 ****
      {
          RangeVar   *rv = lfirst(cell);
          Relation    rel;

          rel = heap_openrv(rv, AccessExclusiveLock);
          /* don't throw error for "TRUNCATE foo, foo" */
!         if (list_member_oid(relids, RelationGetRelid(rel)))
          {
              heap_close(rel, AccessExclusiveLock);
              continue;
          }
          truncate_check_rel(rel);
          rels = lappend(rels, rel);
!         relids = lappend_oid(relids, RelationGetRelid(rel));
      }

      /*
--- 772,812 ----
      {
          RangeVar   *rv = lfirst(cell);
          Relation    rel;
+         bool        recurse = interpretInhOption(rv->inhOpt);
+         Oid            myrelid;

          rel = heap_openrv(rv, AccessExclusiveLock);
+         myrelid = RelationGetRelid(rel);
          /* don't throw error for "TRUNCATE foo, foo" */
!         if (list_member_oid(relids, myrelid))
          {
              heap_close(rel, AccessExclusiveLock);
              continue;
          }
          truncate_check_rel(rel);
          rels = lappend(rels, rel);
!         relids = lappend_oid(relids, myrelid);
!
!         if (recurse)
!         {
!             ListCell   *child;
!             List       *children;
!
!             children = find_all_inheritors(myrelid);
!
!             foreach(child, children)
!             {
!                 Oid            childrelid = lfirst_oid(child);
!
!                 if (list_member_oid(relids, childrelid))
!                     continue;
!
!                 rel = heap_open(childrelid, AccessExclusiveLock);
!                 truncate_check_rel(rel);
!                 rels = lappend(rels, rel);
!                 relids = lappend_oid(relids, childrelid);
!             }
!         }
      }

      /*
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.652
diff -u -3 -p -c -r2.652 gram.y
*** src/backend/parser/gram.y    7 Jan 2009 22:54:45 -0000    2.652
--- src/backend/parser/gram.y    8 Jan 2009 13:27:47 -0000
*************** static TypeName *TableFuncTypeName(List
*** 284,289 ****
--- 284,290 ----
                  execute_param_clause using_clause returning_clause
                  enum_val_list table_func_column_list
                  create_generic_options alter_generic_options
+                 relation_expr_list

  %type <range>    OptTempTableName
  %type <into>    into_clause create_as_target
*************** attrs:        '.' attr_name
*** 3794,3800 ****
   *****************************************************************************/

  TruncateStmt:
!             TRUNCATE opt_table qualified_name_list opt_restart_seqs opt_drop_behavior
                  {
                      TruncateStmt *n = makeNode(TruncateStmt);
                      n->relations = $3;
--- 3795,3801 ----
   *****************************************************************************/

  TruncateStmt:
!             TRUNCATE opt_table relation_expr_list opt_restart_seqs opt_drop_behavior
                  {
                      TruncateStmt *n = makeNode(TruncateStmt);
                      n->relations = $3;
*************** using_clause:
*** 6558,6564 ****
              | /*EMPTY*/                                { $$ = NIL; }
          ;

! LockStmt:    LOCK_P opt_table qualified_name_list opt_lock opt_nowait
                  {
                      LockStmt *n = makeNode(LockStmt);

--- 6559,6573 ----
              | /*EMPTY*/                                { $$ = NIL; }
          ;

!
! /*****************************************************************************
!  *
!  *        QUERY:
!  *                LOCK TABLE
!  *
!  *****************************************************************************/
!
! LockStmt:    LOCK_P opt_table relation_expr_list opt_lock opt_nowait
                  {
                      LockStmt *n = makeNode(LockStmt);

*************** relation_expr:
*** 7487,7492 ****
--- 7496,7507 ----
          ;


+ relation_expr_list:
+             relation_expr                            { $$ = list_make1($1); }
+             | relation_expr_list ',' relation_expr    { $$ = lappend($1, $3); }
+         ;
+
+
  /*
   * Given "UPDATE foo set set ...", we have to decide without looking any
   * further ahead whether the first "set" is an alias or the UPDATE's SET
Index: src/test/regress/expected/truncate.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/truncate.out,v
retrieving revision 1.18
diff -u -3 -p -c -r1.18 truncate.out
*** src/test/regress/expected/truncate.out    1 Sep 2008 20:42:46 -0000    1.18
--- src/test/regress/expected/truncate.out    8 Jan 2009 13:27:48 -0000
*************** SELECT * FROM trunc_e;
*** 141,146 ****
--- 141,290 ----
  (0 rows)

  DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
+ -- Test TRUNCATE with inheritance
+ CREATE TABLE trunc_f (col1 integer primary key);
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trunc_f_pkey" for table "trunc_f"
+ INSERT INTO trunc_f VALUES (1);
+ INSERT INTO trunc_f VALUES (2);
+ CREATE TABLE trunc_fa (col2a text) INHERITS (trunc_f);
+ INSERT INTO trunc_fa VALUES (3, 'three');
+ CREATE TABLE trunc_fb (col2b int) INHERITS (trunc_f);
+ INSERT INTO trunc_fb VALUES (4, 444);
+ CREATE TABLE trunc_faa (col3 text) INHERITS (trunc_fa);
+ INSERT INTO trunc_faa VALUES (5, 'five', 'FIVE');
+ BEGIN;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+     3
+     4
+     5
+ (5 rows)
+
+ TRUNCATE trunc_f;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+ (0 rows)
+
+ ROLLBACK;
+ BEGIN;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+     3
+     4
+     5
+ (5 rows)
+
+ TRUNCATE ONLY trunc_f;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     3
+     4
+     5
+ (3 rows)
+
+ ROLLBACK;
+ BEGIN;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+     3
+     4
+     5
+ (5 rows)
+
+ SELECT * FROM trunc_fa;
+  col1 | col2a
+ ------+-------
+     3 | three
+     5 | five
+ (2 rows)
+
+ SELECT * FROM trunc_faa;
+  col1 | col2a | col3
+ ------+-------+------
+     5 | five  | FIVE
+ (1 row)
+
+ TRUNCATE ONLY trunc_fb, ONLY trunc_fa;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+     5
+ (3 rows)
+
+ SELECT * FROM trunc_fa;
+  col1 | col2a
+ ------+-------
+     5 | five
+ (1 row)
+
+ SELECT * FROM trunc_faa;
+  col1 | col2a | col3
+ ------+-------+------
+     5 | five  | FIVE
+ (1 row)
+
+ ROLLBACK;
+ BEGIN;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+     3
+     4
+     5
+ (5 rows)
+
+ SELECT * FROM trunc_fa;
+  col1 | col2a
+ ------+-------
+     3 | three
+     5 | five
+ (2 rows)
+
+ SELECT * FROM trunc_faa;
+  col1 | col2a | col3
+ ------+-------+------
+     5 | five  | FIVE
+ (1 row)
+
+ TRUNCATE ONLY trunc_fb, trunc_fa;
+ SELECT * FROM trunc_f;
+  col1
+ ------
+     1
+     2
+ (2 rows)
+
+ SELECT * FROM trunc_fa;
+  col1 | col2a
+ ------+-------
+ (0 rows)
+
+ SELECT * FROM trunc_faa;
+  col1 | col2a | col3
+ ------+-------+------
+ (0 rows)
+
+ ROLLBACK;
+ DROP TABLE trunc_f CASCADE;
+ NOTICE:  drop cascades to 3 other objects
+ DETAIL:  drop cascades to table trunc_fa
+ drop cascades to table trunc_faa
+ drop cascades to table trunc_fb
  -- Test ON TRUNCATE triggers
  CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);
  CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text,
Index: src/test/regress/sql/truncate.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/truncate.sql,v
retrieving revision 1.7
diff -u -3 -p -c -r1.7 truncate.sql
*** src/test/regress/sql/truncate.sql    16 May 2008 23:36:05 -0000    1.7
--- src/test/regress/sql/truncate.sql    8 Jan 2009 13:27:48 -0000
*************** SELECT * FROM trunc_e;
*** 78,83 ****
--- 78,132 ----

  DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;

+ -- Test TRUNCATE with inheritance
+
+ CREATE TABLE trunc_f (col1 integer primary key);
+ INSERT INTO trunc_f VALUES (1);
+ INSERT INTO trunc_f VALUES (2);
+
+ CREATE TABLE trunc_fa (col2a text) INHERITS (trunc_f);
+ INSERT INTO trunc_fa VALUES (3, 'three');
+
+ CREATE TABLE trunc_fb (col2b int) INHERITS (trunc_f);
+ INSERT INTO trunc_fb VALUES (4, 444);
+
+ CREATE TABLE trunc_faa (col3 text) INHERITS (trunc_fa);
+ INSERT INTO trunc_faa VALUES (5, 'five', 'FIVE');
+
+ BEGIN;
+ SELECT * FROM trunc_f;
+ TRUNCATE trunc_f;
+ SELECT * FROM trunc_f;
+ ROLLBACK;
+
+ BEGIN;
+ SELECT * FROM trunc_f;
+ TRUNCATE ONLY trunc_f;
+ SELECT * FROM trunc_f;
+ ROLLBACK;
+
+ BEGIN;
+ SELECT * FROM trunc_f;
+ SELECT * FROM trunc_fa;
+ SELECT * FROM trunc_faa;
+ TRUNCATE ONLY trunc_fb, ONLY trunc_fa;
+ SELECT * FROM trunc_f;
+ SELECT * FROM trunc_fa;
+ SELECT * FROM trunc_faa;
+ ROLLBACK;
+
+ BEGIN;
+ SELECT * FROM trunc_f;
+ SELECT * FROM trunc_fa;
+ SELECT * FROM trunc_faa;
+ TRUNCATE ONLY trunc_fb, trunc_fa;
+ SELECT * FROM trunc_f;
+ SELECT * FROM trunc_fa;
+ SELECT * FROM trunc_faa;
+ ROLLBACK;
+
+ DROP TABLE trunc_f CASCADE;
+
  -- Test ON TRUNCATE triggers

  CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);