The following bug has been logged on the website:
Bug reference: 8152
Logged by: Hugo J. Curti
Email address: hcu...@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=1 ;
NOTICE: Trigger executed
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..65.40 rows=4540 width=8) (actual time=0.035..0.040
rows=1 loops=1)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual
time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual
time=0.025..0.028 rows=1 loops=1)
Trigger tb on b: time=0.869 calls=1
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=1 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..96.80 rows=6680 width=9) (actual time=0.021..0.028
rows=1 loops=1)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual
time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual
time=0.013..0.016 rows=1 loops=1)
-> Seq Scan on c a (cost=0.00..31.40 rows=2140 width=10) (actual
time=0.001..0.001 rows=0 loops=1)
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=1 ;
NOTICE: Trigger executed
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Append (cost=0.00..96.80 rows=6680 width=9) (actual time=0.041..0.050
rows=1 loops=1)
-> Seq Scan on a (cost=0.00..34.00 rows=2400 width=6) (actual
time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on b a (cost=0.00..31.40 rows=2140 width=10) (actual
time=0.032..0.036 rows=1 loops=1)
-> Seq Scan on c a (cost=0.00..31.40 rows=2140 width=10) (actual
time=0.001..0.001 rows=0 loops=1)
Trigger tc on c: time=1.002 calls=1
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
