Re: [HACKERS] about truncate
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: The SQL standard uses a recursive-by-default language. For example, the rules for the DELETE command state: Actually, I'm not convinced. Take a look at the SELECT WITH HIERARCHY OPTION stuff in SQL99 and later, in particular this from SQL99 12.2 grant privilege statement: Ah, the mysterious HIERARCHY OPTION comes into play. That appears to be the ticket. 7) Let SWH be the set of privilege descriptors in CPD whose action is SELECT WITH HIERARCHY OPTION, and let ST be the set of subtables of O, then for every grantee G in SWH and for every table T in ST, the following grant statement is effectively executed without further Access Rule checking: GRANT SELECT ON T TO G GRANTED BY A It's difficult to read that any other way than that privileges are *not* auto-recursive, and they have chosen to spell * in GRANT as WITH HIERARCHY OPTION (gackk). Er, well, I see this piece from SQL:2008 on table reference: 1) Case: [...] B) [...], the current privileges shall include SELECT on at least one column of T. 2) If TP simply contains only spec and TN identifies a typed table, then Case: [...] B) [...], the current privileges shall include SELECT WITH HIERARCHY OPTION on at least one supertable of T. (The omitted phrases deal with SECURITY INVOKER situations.) I read that as that privileges are auto-recursive, and that you need the hierarchy option to be permitted to use ONLY. (So the hierarchy option is an additional privilege on top of SELECT that allows you to break the encapsulation of the inheritance setup.) On the other hand, it's hard to square that reading with the lack of any UPDATE or DELETE WITH HIERARCHY OPTION syntax. What am I missing here? You need SELECT with or without HIERARCHY, as the case may be, to locate the row. Once you have located it, you can UPDATE or DELETE it depending on privilege, but then it doesn't matter anymore how you got it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Andrew Dunstan wrote: It's just occurred to me that if TRUNCATE no longer means TRUNCATE ONLY, parallel restore will need to detect which server version is being used so that for version 8.3 it issues TRUNCATE ONLY. The pg_dump output was never backward compatible. (The input is.) So the output of parallel restore need not be backward compatible either. (Unless this mandate has changed dramatically while I was not looking?) So always issue TRUNCATE ONLY, if that is what the logic requires. The additional benefit is that this will fail safely on older versions. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut wrote: Andrew Dunstan wrote: It's just occurred to me that if TRUNCATE no longer means TRUNCATE ONLY, parallel restore will need to detect which server version is being used so that for version 8.3 it issues TRUNCATE ONLY. The pg_dump output was never backward compatible. (The input is.) So the output of parallel restore need not be backward compatible either. (Unless this mandate has changed dramatically while I was not looking?) So always issue TRUNCATE ONLY, if that is what the logic requires. The additional benefit is that this will fail safely on older versions. No it won't fail safely on older versions, because the truncate is part of a transaction, and thus the data member(s) will all fail. I'd like to be able to use 8.4 pg_restore to run parallel restores on older servers, and the fix for this is utterly trivial. I'll be posting a new patch with it in today. (If we can't or don't want to make it work with older servers, I will create an out-of-tree patch for 8.3 that does, and put it on pgFoundry. But that would be a pity.) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Andrew Dunstan wrote: The pg_dump output was never backward compatible. (The input is.) So the output of parallel restore need not be backward compatible either. (Unless this mandate has changed dramatically while I was not looking?) So always issue TRUNCATE ONLY, if that is what the logic requires. The additional benefit is that this will fail safely on older versions. No it won't fail safely on older versions, because the truncate is part of a transaction, and thus the data member(s) will all fail. I meant safe as in, it won't randomly delete more data than you intended. I didn't mean in as in do-what-I-mean. :-) I'd like to be able to use 8.4 pg_restore to run parallel restores on older servers, and the fix for this is utterly trivial. I'll be posting a new patch with it in today. Works for me. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut pete...@gmx.net writes: The SQL standard uses a recursive-by-default language. For example, the rules for the DELETE command state: Actually, I'm not convinced. Take a look at the SELECT WITH HIERARCHY OPTION stuff in SQL99 and later, in particular this from SQL99 12.2 grant privilege statement: 7) Let SWH be the set of privilege descriptors in CPD whose action is SELECT WITH HIERARCHY OPTION, and let ST be the set of subtables of O, then for every grantee G in SWH and for every table T in ST, the following grant statement is effectively executed without further Access Rule checking: GRANT SELECT ON T TO G GRANTED BY A It's difficult to read that any other way than that privileges are *not* auto-recursive, and they have chosen to spell * in GRANT as WITH HIERARCHY OPTION (gackk). On the other hand, it's hard to square that reading with the lack of any UPDATE or DELETE WITH HIERARCHY OPTION syntax. What am I missing here? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: The SQL standard uses a recursive-by-default language. For example, the rules for the DELETE command state: Actually, I'm not convinced. Take a look at the SELECT WITH HIERARCHY OPTION stuff in SQL99 and later, in particular this from SQL99 12.2 grant privilege statement: 7) Let SWH be the set of privilege descriptors in CPD whose action is SELECT WITH HIERARCHY OPTION, and let ST be the set of subtables of O, then for every grantee G in SWH and for every table T in ST, the following grant statement is effectively executed without further Access Rule checking: GRANT SELECT ON T TO G GRANTED BY A It's difficult to read that any other way than that privileges are *not* auto-recursive, and they have chosen to spell * in GRANT as WITH HIERARCHY OPTION (gackk). On the other hand, it's hard to square that reading with the lack of any UPDATE or DELETE WITH HIERARCHY OPTION syntax. What am I missing here? It's just occurred to me that if TRUNCATE no longer means TRUNCATE ONLY, parallel restore will need to detect which server version is being used so that for version 8.3 it issues TRUNCATE ONLY. Otherwise there would be a danger of a collision between a table and its children. The only alternative would be to create a dependency between the data of a table and the data of its children, which would be undesirable as well as more complicated - in general the data should only depend on the table creation (at most). cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut wrote: Tom Lane wrote: +1 for making TRUNCATE and LOCK support ONLY. Patch attached. This was committed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
I wrote: Here is the current line-up: command supports ONLY ALTER TABLE all other actions yes ALTER TABLE RENAME COLUMN yes ALTER TABLE RENAME no ALTER TABLE SET SCHEMA documented no, but accepted and ignored This is actually a bit worse: All variants of ALTER TABLE accept ONLY, but only about half of them are potentially recursive and about half of them never recurse, and this is not documented in an obvious place (or anywhere). I have added a Todo list item to sort this out. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: This area is under SQL standard control, so we can't really invent our own behavior. What *would* do the right thing here, or would anything? I think we don't need GRANT to be recursive, but instead the permission checks at runtime should allow SELECT * FROM persons; to succeed even if there are no permissions on employees. Hmm, if we are supposing that the spec should control this, then surely we can find chapter and verse spelling out what should happen. The SQL standard uses a recursive-by-default language. For example, the rules for the DELETE command state: 6) Case: a) If target table contains ONLY, then the rows for which the result of the search condition is True and for which there is no subrow in a proper subtable of T are identified for deletion from T. b) Otherwise, the rows for which the result of the search condition is True are identified for deletion from T. So when the SQL standard says, privileges are granted on this table, or $action is done on that table, it means, in PostgreSQL terms, the table and its children. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Mon, 2009-01-12 at 11:43 +0200, Peter Eisentraut wrote: Peter Eisentraut wrote: Tom Lane wrote: +1 for making TRUNCATE and LOCK support ONLY. Patch attached. This was committed. Please could we put in a GUC to allow that to be toggled in this release and warning issued for non-optional behaviour change in following release? This seems like a dangerous behaviour change for some apps and may be a blocker to upgrade, as the changes in casting behaviour have proved. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-01-12 at 11:43 +0200, Peter Eisentraut wrote: Peter Eisentraut wrote: Tom Lane wrote: +1 for making TRUNCATE and LOCK support ONLY. Patch attached. This was committed. Please could we put in a GUC to allow that to be toggled in this release That seems like it would just be putting off the pain. It doesn't make it any easier to migrate in the end. and warning issued for non-optional behaviour change in following release? We do print INFO messages when drops cascade. We could print similar messages when DDL applies recursively by default. (We can't do DML since it would fill logs quickly). That seems reasonable to me. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Gregory Stark st...@enterprisedb.com writes: Simon Riggs si...@2ndquadrant.com writes: Please could we put in a GUC to allow that to be toggled in this release That seems like it would just be putting off the pain. Yes, we already had exactly this discussion and concluded that a GUC wasn't going to improve matters. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
David Fetter wrote: On Thu, Jan 08, 2009 at 02:39:52PM +0200, Peter Eisentraut wrote: David Fetter wrote: +1 for adding recursion to GRANT/REVOKE :) This area is under SQL standard control, so we can't really invent our own behavior. Consider the following: CREATE TABLE persons (name, email); CREATE TABLE employees (grade, salary) INHERITS (persons); GRANT SELECT ON persons TO allstaff; -- ??? GRANT SELECT ON employees TO managers; What you want in practice is that allstaff can read only those columns of employees that come from the persons table. Both recursive and nonrecursive GRANT do the wrong thing here. What *would* do the right thing here, or would anything? I think we don't need GRANT to be recursive, but instead the permission checks at runtime should allow SELECT * FROM persons; to succeed even if there are no permissions on employees. But only on the columns of persons and only if actually queried through persons. Needs a more detailed analysis, but that is how I imagine it ought to work. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut pete...@gmx.net writes: This area is under SQL standard control, so we can't really invent our own behavior. What *would* do the right thing here, or would anything? I think we don't need GRANT to be recursive, but instead the permission checks at runtime should allow SELECT * FROM persons; to succeed even if there are no permissions on employees. Hmm, if we are supposing that the spec should control this, then surely we can find chapter and verse spelling out what should happen. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
David Fetter wrote: +1 for adding recursion to GRANT/REVOKE :) This area is under SQL standard control, so we can't really invent our own behavior. Consider the following: CREATE TABLE persons (name, email); CREATE TABLE employees (grade, salary) INHERITS (persons); GRANT SELECT ON persons TO allstaff; -- ??? GRANT SELECT ON employees TO managers; What you want in practice is that allstaff can read only those columns of employees that come from the persons table. Both recursive and nonrecursive GRANT do the wrong thing here. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
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 - 1.51
--- doc/src/sgml/ref/lock.sgml 8 Jan 2009 13:27:47 -
*** PostgreSQL documentation
*** 21,27
refsynopsisdiv
synopsis
! LOCK [ TABLE ] replaceable class=PARAMETERname/replaceable [, ...] [ IN replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
--- 21,27
refsynopsisdiv
synopsis
! LOCK [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [, ...] [ IN replaceable class=PARAMETERlockmode/replaceable MODE ] [ NOWAIT ]
where replaceable class=PARAMETERlockmode/replaceable is one of:
*** where replaceable class=PARAMETERloc
*** 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 literalONLY/ is specified, only that table is
! locked. If literalONLY/ 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 - 1.31
--- doc/src/sgml/ref/truncate.sgml 8 Jan 2009 13:27:47 -
*** PostgreSQL documentation
*** 21,27
refsynopsisdiv
synopsis
! TRUNCATE [ TABLE ] replaceable class=PARAMETERname/replaceable [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
/synopsis
/refsynopsisdiv
--- 21,27
refsynopsisdiv
synopsis
! TRUNCATE [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
/synopsis
/refsynopsisdiv
*** TRUNCATE [ TABLE ] replaceable class=P
*** 47,53
termreplaceable class=PARAMETERname/replaceable/term
listitem
para
! The name (optionally schema-qualified) of a table to be truncated.
/para
/listitem
/varlistentry
--- 47,56
termreplaceable class=PARAMETERname/replaceable/term
listitem
para
! The name (optionally schema-qualified) of a table to be
! truncated. If literalONLY/ is specified, only that table is
! truncated. If literalONLY/ 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 - 1.20
--- src/backend/commands/lockcmds.c 8 Jan 2009 13:27:47 -
***
*** 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
Re: [HACKERS] about truncate
On Thu, Jan 08, 2009 at 02:39:52PM +0200, Peter Eisentraut wrote: David Fetter wrote: +1 for adding recursion to GRANT/REVOKE :) This area is under SQL standard control, so we can't really invent our own behavior. Consider the following: CREATE TABLE persons (name, email); CREATE TABLE employees (grade, salary) INHERITS (persons); GRANT SELECT ON persons TO allstaff; -- ??? GRANT SELECT ON employees TO managers; What you want in practice is that allstaff can read only those columns of employees that come from the persons table. Both recursive and nonrecursive GRANT do the wrong thing here. What *would* do the right thing here, or would anything? Cheers, David (not getting into the design decisions implicit in the above tables, which IMHO is not right) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Tom Lane wrote: I note though that we have a lot of other non-recursive maintenance operations (CLUSTER, some variants of ALTER TABLE, etc) ... are we going to try to make them all recursive? Here is the current line-up: command supports ONLY ALTER TABLE all other actions yes ALTER TABLE RENAME COLUMN yes ALTER TABLE RENAME no ALTER TABLE SET SCHEMA documented no, but accepted and ignored ANALYZE no CLUSTER no COMMENT no COPYno CREATE INDEXno DELETE yes DROP TABLE no GRANT no INSERT no LOCKno REINDEX no REVOKE no SELECT yes TRUNCATEno UPDATE yes VACUUM no Obviously, there is no practical sense in making them all behave the same, because ALTER TABLE RENAME not-ONLY for example would be nonsense. So there are always going to be two kinds of commands: logical ones that operate try to give the illusion that inheriting tables are included in the parent table, and physical ones that operate on a in single table only. About the current situation: Most people seemed to agree that TRUNCATE should support ONLY, to behave like DELETE. ALTER TABLE SET SCHEMA appears to be an omission. There could be some rare use cases for recursive versions of ANALYZE, CLUSTER, REINDEX, and VACUUM, but those would only be for convenience and would have no logical effect. A recursive version of CREATE INDEX could be quite useful, but that might belong into the whole inheritance vs. indexes bag of a mess. LOCK got me thinking. If you have a situation where an explicit lock is necessary because serializable transaction isolation does not give you the necessary guarantees, you would really want LOCK to be recursive. If you happen to write your application properly following one of the few obscure practical examples about explicit locking, and then the DBA partitions the table under you, you lose quite badly. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut pete...@gmx.net writes: [ good summary ] +1 for making TRUNCATE and LOCK support ONLY. I don't care much about ALTER TABLE SET SCHEMA, but perhaps there's a use-case for recursion on that. We should stay away from recursive CREATE INDEX for the moment --- for one thing, you'd have to invent names for the additional indexes. I wonder whether GRANT/REVOKE shouldn't be made to support recursion too. We have a standard warning don't forget to grant rights on the child tables ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Wed, Jan 07, 2009 at 11:17:46AM -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: [ good summary ] +1 for making TRUNCATE and LOCK support ONLY. I don't care much about ALTER TABLE SET SCHEMA, but perhaps there's a use-case for recursion on that. We should stay away from recursive CREATE INDEX for the moment --- for one thing, you'd have to invent names for the additional indexes. I wonder whether GRANT/REVOKE shouldn't be made to support recursion too. We have a standard warning don't forget to grant rights on the child tables ... +1 for adding recursion to GRANT/REVOKE :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut wrote: On Monday 22 December 2008 05:09:54 Jaime Casanova wrote: just out of curiosity, why TRUNCATE doesn't support ONLY? It was probably just an omission. Note that TRUNCATE currently does not act on inheriting tables. In other words, the behavior is already like ONLY. FWIW, the SQL standard says that TRUNCATE should support ONLY, just like DELETE. Something should probably be fixed or at least documented about this. Before I or someone goes to write code for this, note that a proper fix would introduce a backward incompatibility when TRUNCATE is used on inheritance hierarchies. Currently, TRUNCATE only acts on the named table itself, not on any children. The behavior required by the SQL standard (and by consistency with pretty much all other commands in PostgreSQL) is that TRUNCATE operate on all child tables, unless ONLY is specified. Note that there is currently no way to get a TRUNCATE not-ONLY without writing manual loops, which is a significant gap of functionality. Considering that TRUNCATE is a pretty dangerous operation, how can we make adjustments to the behavior without upsetting lots of users? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Peter Eisentraut wrote: Peter Eisentraut wrote: On Monday 22 December 2008 05:09:54 Jaime Casanova wrote: just out of curiosity, why TRUNCATE doesn't support ONLY? It was probably just an omission. Note that TRUNCATE currently does not act on inheriting tables. In other words, the behavior is already like ONLY. FWIW, the SQL standard says that TRUNCATE should support ONLY, just like DELETE. Something should probably be fixed or at least documented about this. Before I or someone goes to write code for this, note that a proper fix would introduce a backward incompatibility when TRUNCATE is used on inheritance hierarchies. Currently, TRUNCATE only acts on the named table itself, not on any children. The behavior required by the SQL standard (and by consistency with pretty much all other commands in PostgreSQL) is that TRUNCATE operate on all child tables, unless ONLY is specified. Note that there is currently no way to get a TRUNCATE not-ONLY without writing manual loops, which is a significant gap of functionality. Considering that TRUNCATE is a pretty dangerous operation, how can we make adjustments to the behavior without upsetting lots of users? Well, it is one of those, Either we fix it or live with the inconsistency forever. Historically we have opted to fix it with a clear warning in the major release notes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: Considering that TRUNCATE is a pretty dangerous operation, how can we make adjustments to the behavior without upsetting lots of users? Well, it is one of those, Either we fix it or live with the inconsistency forever. Historically we have opted to fix it with a clear warning in the major release notes. The only alternatives I can see are (1) go ahead and change it. (2) invent a separate truncate_inheritance GUC that is just like sql_inheritance except it applies only for TRUNCATE. Ugly as (2) is, I think it just puts off the pain. Sooner or later we'd want to flip the factory default from false to true, and the release that does that is *still* going to burn anyone who's not paying attention to the release notes. My vote is to just go ahead and change it. I don't really see much of a use-case for truncating only the parent of an inheritance hierarchy anyway, so I doubt that many people would be affected. I note though that we have a lot of other non-recursive maintenance operations (CLUSTER, some variants of ALTER TABLE, etc) ... are we going to try to make them all recursive? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: Considering that TRUNCATE is a pretty dangerous operation, how can we make adjustments to the behavior without upsetting lots of users? Well, it is one of those, Either we fix it or live with the inconsistency forever. Historically we have opted to fix it with a clear warning in the major release notes. The only alternatives I can see are (1) go ahead and change it. (2) invent a separate truncate_inheritance GUC that is just like sql_inheritance except it applies only for TRUNCATE. Ugly as (2) is, I think it just puts off the pain. Sooner or later we'd want to flip the factory default from false to true, and the release that does that is *still* going to burn anyone who's not paying attention to the release notes. The only way I think #2 works is if we say the GUC will disappear in the next major release, but it hardly seems worth adding the GUC because few people have even noticed the current behavior is a problem, meaning they are probably not using it for parent truncation often. My vote is to just go ahead and change it. I don't really see much of a use-case for truncating only the parent of an inheritance hierarchy anyway, so I doubt that many people would be affected. I note though that we have a lot of other non-recursive maintenance operations (CLUSTER, some variants of ALTER TABLE, etc) ... are we going to try to make them all recursive? Uh, good question. ;-) I think fixing TRUNCATE makes sense because it is similar to DELETE (it operates on the data), but I see ALTER TABLE and CLUSTER as per-table operations that people would not expect to ever recurse, i.e. TRUNCATE is like DELETE without a WHERE clause, but CLUSTER or ALTER TABLE have no DML equivalents. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: My vote is to just go ahead and change it. I don't really see much of a use-case for truncating only the parent of an inheritance hierarchy anyway, so I doubt that many people would be affected. agreed. I note though that we have a lot of other non-recursive maintenance operations (CLUSTER, some variants of ALTER TABLE, etc) ... are we going to try to make them all recursive? Uh, good question. ;-) I think fixing TRUNCATE makes sense because it is similar to DELETE (it operates on the data), but I see ALTER TABLE and CLUSTER as per-table operations that people would not expect to ever recurse, i.e. TRUNCATE is like DELETE without a WHERE clause, but CLUSTER or ALTER TABLE have no DML equivalents. What does the standard say about ALTER TABLE and inheritance? It seems like it would be hard to make ALTER TABLE recursive since, while some operations might make sense, others will depend on the current state of the table and that might be very different for different children. Likewise CLUSTER ON/USING doesn't make much sense to be recursive since the index names will be different. It might be handy to have a recursive version of the command to recluster an already clustered table though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Tue, Dec 30, 2008 at 11:50:06AM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: Considering that TRUNCATE is a pretty dangerous operation, how can we make adjustments to the behavior without upsetting lots of users? Well, it is one of those, Either we fix it or live with the inconsistency forever. Historically we have opted to fix it with a clear warning in the major release notes. The only alternatives I can see are (1) go ahead and change it. (2) invent a separate truncate_inheritance GUC that is just like sql_inheritance except it applies only for TRUNCATE. Ugly as (2) is, I think it just puts off the pain. Sooner or later we'd want to flip the factory default from false to true, and the release that does that is *still* going to burn anyone who's not paying attention to the release notes. My vote is to just go ahead and change it. I don't really see much of a use-case for truncating only the parent of an inheritance hierarchy anyway, so I doubt that many people would be affected. Here's one such use-case. Let's say a table has gotten large and you've decided to partition it. You add child tables, add one or more triggers to the parent table to make sure it never gets a row, populate the child tables from the parent table, then you want to remove all the rows from the parent table. TRUNCATE ONLY handles this case just fine, so long as there's a clear message in the release notes. :) I note though that we have a lot of other non-recursive maintenance operations (CLUSTER, some variants of ALTER TABLE, etc) ... are we going to try to make them all recursive? We probably should. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
David Fetter wrote: My vote is to just go ahead and change it. I don't really see much of a use-case for truncating only the parent of an inheritance hierarchy anyway, so I doubt that many people would be affected. Here's one such use-case. Let's say a table has gotten large and you've decided to partition it. You add child tables, add one or more triggers to the parent table to make sure it never gets a row, populate the child tables from the parent table, then you want to remove all the rows from the parent table. TRUNCATE ONLY handles this case just fine, so long as there's a clear message in the release notes. :) Agreed. The good thing is that I don't imagine what you have described above would be scripted so someone would be typing that and hopefully know the current behavior. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Tue, Dec 30, 2008 at 2:00 PM, David Fetter da...@fetter.org wrote: Here's one such use-case. Let's say a table has gotten large and you've decided to partition it. You add child tables, add one or more triggers to the parent table to make sure it never gets a row, populate the child tables from the parent table, then you want to remove all the rows from the parent table. you're spying me? exactly that happen to me... ;) my first attempt was to execute TRUNCATE ONLY... and gives me an error and the thread begun... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Tue, Dec 30, 2008 at 04:07:33PM -0500, Jaime Casanova wrote: On Tue, Dec 30, 2008 at 2:00 PM, David Fetter da...@fetter.org wrote: Here's one such use-case. Let's say a table has gotten large and you've decided to partition it. You add child tables, add one or more triggers to the parent table to make sure it never gets a row, populate the child tables from the parent table, then you want to remove all the rows from the parent table. you're spying me? D'oh! You've found out. Now that you know... ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Monday 22 December 2008 05:09:54 Jaime Casanova wrote: just out of curiosity, why TRUNCATE doesn't support ONLY? It was probably just an omission. Note that TRUNCATE currently does not act on inheriting tables. In other words, the behavior is already like ONLY. FWIW, the SQL standard says that TRUNCATE should support ONLY, just like DELETE. Something should probably be fixed or at least documented about this. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] about truncate
Hi, just out of curiosity, why TRUNCATE doesn't support ONLY? audit=# TRUNCATE only postgres_log; ERROR: syntax error at or near only LINE 1: TRUNCATE only postgres_log; -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Sun, Dec 21, 2008 at 10:09:54PM -0500, Jaime Casanova wrote: Hi, just out of curiosity, why TRUNCATE doesn't support ONLY? audit=# TRUNCATE only postgres_log; ERROR: syntax error at or near only LINE 1: TRUNCATE only postgres_log; Given that the main (and only sane, IMHO) use for table inheritance is in table partitioning, can we see about deprecating ONLY (in the table inheritance sense) for the next couple of development cycles and then removing it? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
David Fetter da...@fetter.org writes: Given that the main (and only sane, IMHO) use for table inheritance is in table partitioning, can we see about deprecating ONLY (in the table inheritance sense) for the next couple of development cycles and then removing it? No. 1. It's required by SQL standard. 2. Just because you don't have a use for it doesn't mean no one does. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about truncate
On Sun, Dec 21, 2008 at 11:06:09PM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: Given that the main (and only sane, IMHO) use for table inheritance is in table partitioning, can we see about deprecating ONLY (in the table inheritance sense) for the next couple of development cycles and then removing it? No. 1. It's required by SQL standard. Well blow me down! I had no idea the SQL standard had this wart in it. 2. Just because you don't have a use for it doesn't mean no one does. Clearly the SQL standards committee does, and their usage controls ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
