Обсуждение: Re: Trigger before delete does fire before, but delete doesn't not happen

----- "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> Adrian Klaver <aklaver@comcast.net> writes:
> > The problem as far as I can tell is tuple visibility.
>
> Sort of: the triggers on commandeligne fire (and update the commande
> row)
> at completion of the DELETE command inside p_commande_bd.  This means
> that by the time control returns from that trigger, the tuple version
> that was targeted for deletion is already dead, so there's nothing to
> do.  It doesn't chain up to the newer version of the row.
>

Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple
forthe row with id of 1. This means the original statement "delete from commande where id=1" runs against a version of
therow that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and
changedthe row from under the original statement. 


>             regards, tom lane


Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:
> Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple
forthe row with id of 1. This means the original statement "delete from commande where id=1" runs against a version of
therow that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and
changedthe row from under the original statement. 

Right.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane a écrit :
> Adrian Klaver <aklaver@comcast.net> writes:
>> Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple
forthe row with id of 1. This means the original statement "delete from commande where id=1" runs against a version of
therow that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and
changedthe row from under the original statement. 
>
> Right.
>
>             regards, tom lane

Thanks for having helped me understand better why it couldn't be a logical way
of acting.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr - http://www.postgresql.fr
Dalibo - http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJNRmvA+REPKWGI0ERAkeIAKCMucAjbCS8tw5kXJqyCuNWS7pMjQCgu2MU
U4rECUpyOm5rqnr0FRmBT6o=
=b7ow
-----END PGP SIGNATURE-----

On Tuesday 02 December 2008 3:19:11 am Stéphane A. Schildknecht wrote:
> Tom Lane a écrit :
> > Adrian Klaver <aklaver@comcast.net> writes:
> >> Thanks for the explanation. Just so I am clear,the act of updating the
> >> row in p_commandeligne_ad creates a new tuple for the row with id of 1.
> >> This means the original statement "delete from commande where id=1" runs
> >> against a version of the row that no longer exists and becomes a no-op
> >> statement. This happens because the trigger was run as BEFORE and
> >> changed the row from under the original statement.
> >
> > Right.
> >
> >             regards, tom lane
>
> Thanks for having helped me understand better why it couldn't be a logical
> way of acting.
>
> Best regards,

Actually there is a logic to it once you realize that an UPDATE in Postgres is
really a DELETE and INSERT operation. It is a concept that still catches me
on a regular basis.  To see what is going on substitute ctid for oid in your
test case. This will show that the ctid(current tuple id) is changing for the
row you are deleting in commande.

--
Adrian Klaver
aklaver@comcast.net