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);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
