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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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
>

"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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHfLejIB7bNG8LQkwRAkDGAJ4ty2qu6EGbvTdF1ePyty ffXoMOgQCfXGBG
DAC7qb/pzQrf5fEyIkwvjvY=
=2O+I
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly