Обсуждение: 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,
assoon as we encounter a DDL, and commit the DDL too (without waiting for commit) will it be affecting some use cases?
<br/><br />I actually mean to say that DDLs can be declared as self-committing. That would get rid of these
exceptions.<br/><br />Am i missing something?<br /><br />Thanks,<br />Gokul.<br /><br /><div class="gmail_quote">On Jan
3,2008 12:02 AM, Andrew Dunstan < <a href="mailto:adunstan@postgresql.org">adunstan@postgresql.org</a>> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><div class="Ih2E3d"><br /><br />Simon Riggs wrote:<br />> On Tue, 2008-01-01 at 16:09 -0500, Tom
Lanewrote:<br />><br />><br />>> Paranoia would<br />>> suggest forbidding *any* form of ALTER TABLE
whenthere are pending<br />>> trigger events, but maybe that's unnecessarily strong. <br />>><br />><br
/>>That works for me. Such a combination makes no sense, so banning it is<br />> the right thing to do.<br
/>><br/>><br /><br /></div>+1. Doesn't make much sense to me either.<br /><br />cheers<br /><br />andrew<br /><br
/>---------------------------(endof broadcast)---------------------------<br />TIP 4: Have you searched our list
archives?<br/><br />               <a href="http://archives.postgresql.org"
target="_blank">http://archives.postgresql.org</a><br /></blockquote></div><br /><br clear="all" /> 

"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 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/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

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:

> 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/