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
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@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
On Sun, Dec 21, 2008 at 11:06:09PM -0500, Tom Lane wrote:
David Fetter <david@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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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.
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?
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@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
Tom Lane wrote:
Bruce Momjian <bruce@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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@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!
On Tue, Dec 30, 2008 at 11:50:06AM -0500, Tom Lane wrote:
Bruce Momjian <bruce@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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Tue, Dec 30, 2008 at 2:00 PM, David Fetter <david@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
On Tue, Dec 30, 2008 at 04:07:33PM -0500, Jaime Casanova wrote:
On Tue, Dec 30, 2008 at 2:00 PM, David Fetter <david@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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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
COPY no
CREATE INDEX no
DELETE yes
DROP TABLE no
GRANT no
INSERT no
LOCK no
REINDEX no
REVOKE no
SELECT yes
TRUNCATE no
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.
Peter Eisentraut <peter_e@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
On Wed, Jan 07, 2009 at 11:17:46AM -0500, Tom Lane wrote:
Peter Eisentraut <peter_e@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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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.
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.
Attachments:
truncate-lock-only.patchtext/x-diff; name=truncate-lock-only.patchDownload+315-114
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
