BUG #8152: strange behavior regarding after triggers and inheritance - Mailing list pgsql-bugs

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