Table rewrites vs. pending AFTER triggers
Some thought about bug #3847 led me to the following test case:
create table t1(f1 int);
create or replace function t1trig() returns trigger as $$
begin
raise notice 'f1 = %', new.f1;
return new;
end$$ language plpgsql;
create constraint trigger t1t after insert on t1
initially deferred for each row
execute procedure t1trig();
insert into t1 values('42');
insert into t1 values('43');
delete from t1;
begin;
insert into t1 values('44');
alter table t1 alter column f1 type text;
commit;
which fails at the COMMIT with
ERROR: failed to fetch new tuple for AFTER trigger
the reason being of course that the ALTER has rewritten the table and
put the f1=44 tuple at a different TID than what is recorded in the
pending-trigger-events list.
I don't think that this is exactly the problem that the bug reporter
is complaining of, since he wasn't using a DEFERRED trigger, but it
seems like a real hazard anyway.
We have already noted a related problem with respect to TRUNCATE,
and fixed it by forbidding TRUNCATE when there are any pending
trigger events on the target relation. (We only need to consider
local pending events, since locking will prevent this type of
problem between two different backends.)
I think that we need to put in a similar restriction for CLUSTER and
at least the table-rewriting forms of ALTER TABLE. Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.
Comments?
regards, tom lane
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.
That works for me. Such a combination makes no sense, so banning it is
the right thing to do.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.That works for me. Such a combination makes no sense, so banning it is
the right thing to do.
+1. Doesn't make much sense to me either.
cheers
andrew
Is there why we allow DDLs inside a transaction and allow it to be rolled
back? If we commit the previous transaction, as soon as we encounter a DDL,
and commit the DDL too (without waiting for commit) will it be affecting
some use cases?
I actually mean to say that DDLs can be declared as self-committing. That
would get rid of these exceptions.
Am i missing something?
Thanks,
Gokul.
On Jan 3, 2008 12:02 AM, Andrew Dunstan <adunstan@postgresql.org> wrote:
Show quoted text
Simon Riggs wrote:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.That works for me. Such a combination makes no sense, so banning it is
the right thing to do.+1. Doesn't make much sense to me either.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
"Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
I actually mean to say that DDLs can be declared as self-committing.
Egad, an Oracle lover in our midst.
Most of us think that roll-back-able DDL is one of the best features of
Postgres, and certainly one of our best selling points vis-a-vis Oracle.
Don't expect us to give it up.
regards, tom lane
On Jan 3, 2008 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
I actually mean to say that DDLs can be declared as self-committing.
Egad, an Oracle lover in our midst.
:). True, its an impact of working more with Oracle. I made the suggestion
here, because it might reduce some if conditions.
Most of us think that roll-back-able DDL is one of the best features of
Postgres, and certainly one of our best selling points vis-a-vis Oracle.
Don't expect us to give it up.
Can you please explain, any specific use-case where DDLs are necessary
within a transaction?
--
Thanks,
Gokul.
On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote:
On Jan 3, 2008 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
I actually mean to say that DDLs can be declared as
self-committing.Egad, an Oracle lover in our midst.
:). True, its an impact of working more with Oracle. I made the
suggestion here, because it might reduce some if conditions.Most of us think that roll-back-able DDL is one of the best
features of Postgres, and certainly one of our best selling points
vis-a-vis Oracle. Don't expect us to give it up.Can you please explain, any specific use-case where DDLs are
necessary within a transaction?
Let's imagine that you want to do a DDL change to a production
database. You've tested the change script on a test database, but you
want to be sure you get *exactly* from the place you were to the place
you want to be. With transactional DDL, you know absolutely for sure
that you've done either the whole change or none of it, i.e. not
half-way in between :)
Cheers,
David (a giant fan of transactional DDL)
--
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 Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote:
Can you please explain, any specific use-case where DDLs are necessary
within a transaction?
I don't think they are ever necessary, they're just very very nice. For
example:
- You want a new column to appear populated on a table atomically. You
do a BEGIN; add column; update set column=foo; add foreign key; COMMIT
- Installation of external modules can be done atomically, so you don't
end up with half installed contrib modules.
- Principle of Least Surprise. Automatic commit for any reason seems
wrong.
- Temporarily disabling triggers/indexes/constraints, if the system
aborts/crashes, the triggers are reinstated automatically.
- Just general niceity of being able to test schema changes without
immediatly changing the system.
There are many more...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
On Jan 3, 2008 3:53 PM, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote:
Can you please explain, any specific use-case where DDLs are necessary
within a transaction?I don't think they are ever necessary, they're just very very nice. For
example:- You want a new column to appear populated on a table atomically. You
do a BEGIN; add column; update set column=foo; add foreign key; COMMIT
- Installation of external modules can be done atomically, so you don't
end up with half installed contrib modules.
- Principle of Least Surprise. Automatic commit for any reason seems
wrong.
- Temporarily disabling triggers/indexes/constraints, if the system
aborts/crashes, the triggers are reinstated automatically.
- Just general niceity of being able to test schema changes without
immediatly changing the system.There are many more...
Thanks.. it looks like a good feature...
Gokul.
Gokulakannan Somasundaram wrote:
Can you please explain, any specific use-case where DDLs are
necessary within a transaction?
SQL-Scripts that create database objects and should either
succeed or have no effect.
Yours,
Laurenz Albe
Simon Riggs wrote:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.That works for me. Such a combination makes no sense, so banning it is
the right thing to do.
I disagree. This is an implementation limitation, so it makes sense to
try to restrict the user as least as possible.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.
I disagree. This is an implementation limitation, so it makes sense to
try to restrict the user as least as possible.
There's a tradeoff here between security, flexibility, and the amount of
work we want to put into it. At the moment it's not clear to me that
it's worth spending the amount of work that would be needed to determine
which forms of ALTER TABLE are "safe" in this connection. If you're
feeling hot about it, feel free to do the legwork.
(A precedent is that all forms of ALTER TABLE take exclusive lock,
which is more or less the same thing for the cross-backend case.
There's been occasional discussion of whether some forms could
take lesser locks, but never enough interest to make it happen.)
regards, tom lane
Tom Lane wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.I disagree. This is an implementation limitation, so it makes sense to
try to restrict the user as least as possible.There's a tradeoff here between security, flexibility, and the amount of
work we want to put into it. At the moment it's not clear to me that
it's worth spending the amount of work that would be needed to determine
which forms of ALTER TABLE are "safe" in this connection. If you're
feeling hot about it, feel free to do the legwork.(A precedent is that all forms of ALTER TABLE take exclusive lock,
which is more or less the same thing for the cross-backend case.
There's been occasional discussion of whether some forms could
take lesser locks, but never enough interest to make it happen.)
I'd still like to see a sane use case. The other thing being traded off
is possibly simplicity.
cheers
andrew
Hi,
I'm with David on this one. Transactional DDL also turns out to be
incredibly helpful for tools that generate and load DDL to extend the
database, for example triggers and control tables to implement reliable
messaging. You can put the setup in a single transaction, which vastly
simplifies tool implementation.
We have an application at Continuent that depends on exactly this behavior.
I was investigating PostgreSQL semantics just last week and was delighted to
find they appear to be exactly right. Oracle on the other hand is going to
be a pain...
Cheers,
Robert Hodges
On 1/3/08 12:11 AM, "David Fetter" <david@fetter.org> wrote:
Show quoted text
On Thu, Jan 03, 2008 at 01:08:47PM +0530, Gokulakannan Somasundaram wrote:
On Jan 3, 2008 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Gokulakannan Somasundaram" <gokul007@gmail.com> writes:
I actually mean to say that DDLs can be declared as
self-committing.Egad, an Oracle lover in our midst.
:). True, its an impact of working more with Oracle. I made the
suggestion here, because it might reduce some if conditions.Most of us think that roll-back-able DDL is one of the best
features of Postgres, and certainly one of our best selling points
vis-a-vis Oracle. Don't expect us to give it up.Can you please explain, any specific use-case where DDLs are
necessary within a transaction?Let's imagine that you want to do a DDL change to a production
database. You've tested the change script on a test database, but you
want to be sure you get *exactly* from the place you were to the place
you want to be. With transactional DDL, you know absolutely for sure
that you've done either the whole change or none of it, i.e. not
half-way in between :)Cheers,
David (a giant fan of transactional DDL)
Hi Gokul,
If you are saying that DDL should be auto-commit, yes, this really does
limit some use cases.
Transactional DDL is quite helpful for SQL generators, which need to avoid
leaving schema half-changed if the application crashes or there¹s a problem
with the database that causes a command to fail. SLONY is an example of
such a generator where transactional DDL would be helpful though I don¹t
know for a fact that SLONY uses it. We have used it in the past for
building queues in SQL, which required multiple schema changes for a single
queue.
In sum, it¹s much easier to implement such tools if you can do a set of
schema changes atomically. There are no doubt other use cases as well.
Cheers, Robert
On 1/2/08 11:04 PM, "Gokulakannan Somasundaram" <gokul007@gmail.com> wrote:
Is there why we allow DDLs inside a transaction and allow it to be rolled
back? If we commit the previous transaction, as soon as we encounter a DDL,
and commit the DDL too (without waiting for commit) will it be affecting some
use cases?I actually mean to say that DDLs can be declared as self-committing. That
would get rid of these exceptions.Am i missing something?
Thanks,
Gokul.On Jan 3, 2008 12:02 AM, Andrew Dunstan < adunstan@postgresql.org> wrote:
Simon Riggs wrote:
On Tue, 2008-01-01 at 16:09 -0500, Tom Lane wrote:
Paranoia would
suggest forbidding *any* form of ALTER TABLE when there are pending
trigger events, but maybe that's unnecessarily strong.That works for me. Such a combination makes no sense, so banning it is
the right thing to do.+1. Doesn't make much sense to me either.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?http://archives.postgresql.org
<http://archives.postgresql.org>
--
Robert Hodges, CTO, Continuent, Inc.
Email: robert.hodges@continuent.com
Mobile: +1-510-501-3728 Skype: hodgesrm
On Sun, Jun 01, 2008 at 11:51:29PM -0700, Robert Hodges wrote:
If you are saying that DDL should be auto-commit, yes, this really does
limit some use cases.
I agree. Transactional DDL is a big feature I'd hate to see go away.
Oracle DBAs I know look with envy on this feature of Postgres.
with the database that causes a command to fail. SLONY is an example of
such a generator where transactional DDL would be helpful though I don�t
know for a fact that SLONY uses it.
It sort of does, in that all work under Slony is performed in a
transaction. But Slony attempts to isolate the DDL in a sync at just
the right point, and not mix schema and data changes in syncs.
Still, one of the important best practices in preparing your DDL
changing scripts for Slony is to try running that script inside a
transaction (and then rolling back) on every node, to ensure that it
will in fact work on every node.
A
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/