Обсуждение: BUG #8152: strange behavior regarding after triggers and inheritance

The following bug has been logged on the website:

Bug reference:      8152
Logged by:          Hugo J. Curti
Email address:      hcurti@exa.unicen.edu.ar
PostgreSQL version: 8.4.17
Operating system:   Debian GNU/Linux 6.0.7 (squeeze)
Description:        =


I don't know if this is really a bug, but it is at least a strange /
undocumented behavior.

After statments triggers on child tables are sometimes executed and
sometimes are not. As I far as I could see, when the after trigger is
defined in only one of the tables it may not get executed, wether when it is
defined in every inherited table it does, but
which one is undetermined.

this is a simple example:

> CREATE FUNCTION test() returns trigger as $$ BEGIN RAISE NOTICE 'Trigger
executed' ; RETURN NULL ; END $$ LANGUAGE 'plpgsql' ;
CREATE FUNCTION

> CREATE TABLE a ( a integer ) ;
CREATE TABLE

>CREATE TABLE b ( b integer ) INHERITS( a ) ;
CREATE TABLE

> CREATE TRIGGER ta AFTER UPDATE ON a FOR EACH STATEMENT EXECUTE PROCEDURE
test() ;
CREATE TRIGGER

> CREATE TRIGGER tb AFTER UPDATE ON b FOR EACH STATEMENT EXECUTE PROCEDURE
test() ;
CREATE TRIGGER

INSERT INTO b VALUES( 1 , 2 ) ;
INSERT 0 1

EXPLAIN ANALYZE update a set a=3D1 ;
NOTICE:  Trigger executed
                                        QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------
 Append  (cost=3D0.00..65.40 rows=3D4540 width=3D8) (actual time=3D0.035..0=
.040
rows=3D1 loops=3D1)
   ->  Seq Scan on a  (cost=3D0.00..34.00 rows=3D2400 width=3D6) (actual
time=3D0.005..0.005 rows=3D0 loops=3D1)
   ->  Seq Scan on b a  (cost=3D0.00..31.40 rows=3D2140 width=3D10) (actual
time=3D0.025..0.028 rows=3D1 loops=3D1)
 Trigger tb on b: time=3D0.869 calls=3D1
 Total runtime: 1.117 ms
(5 filas)

-- Here trigger tb gets executed. That
-- might be correct, but since it is a
-- an 'AFTER STATEMENT' trigger I think,
-- ta would be a better candidate...

-- The strange behavior starts here:

> CREATE TABLE c ( c integer ) INHERITS( a ) ;
CREATE TABLE

> EXPLAIN ANALYZE update a set a=3D1 ;
                                        QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------
 Append  (cost=3D0.00..96.80 rows=3D6680 width=3D9) (actual time=3D0.021..0=
.028
rows=3D1 loops=3D1)
   ->  Seq Scan on a  (cost=3D0.00..34.00 rows=3D2400 width=3D6) (actual
time=3D0.005..0.005 rows=3D0 loops=3D1)
   ->  Seq Scan on b a  (cost=3D0.00..31.40 rows=3D2140 width=3D10) (actual
time=3D0.013..0.016 rows=3D1 loops=3D1)
   ->  Seq Scan on c a  (cost=3D0.00..31.40 rows=3D2140 width=3D10) (actual
time=3D0.001..0.001 rows=3D0 loops=3D1)
 Total runtime: 0.188 ms
(5 filas)

-- The trigger does not get executed
-- any more!

-- Now, adding the trigger to table c:

> CREATE TRIGGER tc AFTER UPDATE ON c FOR EACH STATEMENT EXECUTE PROCEDURE
test() ;
CREATE TRIGGER

> EXPLAIN ANALYZE update a set a=3D1 ;
NOTICE:  Trigger executed
                                        QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------
 Append  (cost=3D0.00..96.80 rows=3D6680 width=3D9) (actual time=3D0.041..0=
.050
rows=3D1 loops=3D1)
   ->  Seq Scan on a  (cost=3D0.00..34.00 rows=3D2400 width=3D6) (actual
time=3D0.004..0.004 rows=3D0 loops=3D1)
   ->  Seq Scan on b a  (cost=3D0.00..31.40 rows=3D2140 width=3D10) (actual
time=3D0.032..0.036 rows=3D1 loops=3D1)
   ->  Seq Scan on c a  (cost=3D0.00..31.40 rows=3D2140 width=3D10) (actual
time=3D0.001..0.001 rows=3D0 loops=3D1)
 Trigger tc on c: time=3D1.002 calls=3D1
 Total runtime: 1.314 ms
(6 filas)

-- Now the trigger tc gets executed.
-- This is strange. I might expect ta
-- because it is an 'AFTER STATEMENT'
-- trigger, or tb because the affected
-- rows are on table b, but why tc?

The workarround I found is to define the after statement trigger in EVERY
child table.

I hope this helps.

Regards,

    Hugo J. Curti

hcurti@exa.unicen.edu.ar writes:
> PostgreSQL version: 8.4.17

> After statments triggers on child tables are sometimes executed and
> sometimes are not. As I far as I could see, when the after trigger is
> defined in only one of the tables it may not get executed, wether when it is
> defined in every inherited table it does, but
> which one is undetermined.

This test case appears to work the way you're expecting in 9.0 and
later.  I didn't immediately find a mention of such a change in the
commit logs; perhaps it got fixed as a side-effect of the changes that
moved trigger processing into ModifyTable plan nodes.  Anyway, I doubt
we'd consider changing trigger behavior in 8.4.x at this late date.
You should update to a newer release series if this is a problem for you.

            regards, tom lane

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

El 13/05/13 17:06, Tom Lane escribi=F3:
> hcurti@exa.unicen.edu.ar writes:
>> PostgreSQL version: 8.4.17
>=20
>> After statments triggers on child tables are sometimes executed and
>> sometimes are not. As I far as I could see, when the after trigger is
>> defined in only one of the tables it may not get executed, wether when=
 it is
>> defined in every inherited table it does, but
>> which one is undetermined.
>=20
> This test case appears to work the way you're expecting in 9.0 and
> later.  I didn't immediately find a mention of such a change in the
> commit logs; perhaps it got fixed as a side-effect of the changes that
> moved trigger processing into ModifyTable plan nodes.  Anyway, I doubt
> we'd consider changing trigger behavior in 8.4.x at this late date.
> You should update to a newer release series if this is a problem for yo=
u.
>=20
>             regards, tom lane

Thank you Tom,

   The update to 9.x is already planned, and the workaround I mentioned
in the post works for me.

   regards,

        Hugo

- --=20
mailto:hcurti@exa.unicen.edu.ar
http://www.exa.unicen.edu.ar/~hcurti
Firma digital GPG/PGP: http://www.exa.unicen.edu.ar/~hcurti/firma_hcurti.=
asc

Puede hacer una donaci=F3n indirecta (gratis) de un plato de comida
para un ni=F1o de la Argentina ahora mismo ingresando a
http://www.porloschicos.com/servlet/PorLosChicos?comando=3Ddonar
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlGRZLEACgkQy9s+D7eSFvn2uwCeL3v4sQpkLgvN3xmHIVYN/2Ic
NEcAoOrR+NXMzfarmgKxjDSun2docaC5
=3D6buB
-----END PGP SIGNATURE-----