Re: Statement-level triggers and inheritance - Mailing list pgsql-hackers

Added to TODO:
Have statement-level triggers fire for all tables in aninheritance hierarchy

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
> NotDashEscaped: You need GnuPG to verify this message
> 
> 
> Looks like inheritance causes a statement-level trigger to fire on
> the last evaluated table in the inheritance chain. Is this the
> desired behavior? If so, is there any way to predict or drive which
> child table will be last evaluated? Or any way to have a statement-level
> trigger fire on the parent table without using the ONLY syntax? I'm
> converting a parent table from using rules to triggers and would like
> to use a statement-level trigger to effect this rather than row-level,
> but don't want to silently prevent moving rows to the child table(s)
> because the caller forgot to specify 'ONLY'.
> 
> 
> Test case:
> 
> CREATE OR REPLACE FUNCTION trigtest()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $_$
>  BEGIN
>   RAISE NOTICE 'Trigger on table %, level is %', TG_TABLE_NAME, TG_LEVEL;
>   RETURN NULL;
>  END;
> $_$;
> 
> DROP TABLE IF EXISTS abc CASCADE;
> 
> CREATE TABLE abc AS SELECT 123::int AS id;
> 
> CREATE TRIGGER abctrig1 AFTER UPDATE ON abc FOR EACH STATEMENT EXECUTE PROCEDURE trigtest();
> CREATE TRIGGER abctrig2 AFTER UPDATE ON abc FOR EACH ROW EXECUTE PROCEDURE trigtest();
> 
> UPDATE abc SET id = id;
> 
> -- Outputs both as expected:
> -- NOTICE:  Trigger on table abc, level is ROW
> -- NOTICE:  Trigger on table abc, level is STATEMENT
> 
> CREATE TABLE abckid() INHERITS (abc);
> 
> UPDATE abc SET id = id;
> 
> -- Outputs the row-level only:
> -- NOTICE:  Trigger on table abc, level is ROW
> 
> CREATE TRIGGER abckidtrig AFTER UPDATE ON abckid FOR EACH STATEMENT EXECUTE PROCEDURE trigtest();
> 
> UPDATE abc SET id = id;
> 
> -- Outputs row-level on parent, statement-level on child:
> -- NOTICE:  Trigger on table abc, level is ROW
> -- NOTICE:  Trigger on table abckid, level is STATEMENT
> 
> CREATE TABLE abckid2() INHERITS (abc);
> 
> UPDATE abc SET id = id;
> 
> -- Outputs row-level on parent only:
> -- NOTICE:  Trigger on table abc, level is ROW
> 
> CREATE TRIGGER abckid2trig AFTER UPDATE ON abckid2 FOR EACH STATEMENT EXECUTE PROCEDURE trigtest();
> 
> UPDATE abc SET id = id;
> 
> -- Outputs row-level on parent, statement-level on one (the latest?) child only:
> -- NOTICE:  Trigger on table abc, level is ROW
> -- NOTICE:  Trigger on table abckid2, level is STATEMENT
> 
> UPDATE ONLY abc SET id = id;
> 
> -- Outputs row-level on parent, statement-level on parent:
> -- NOTICE:  Trigger on table abc, level is ROW
> -- NOTICE:  Trigger on table abc, level is STATEMENT
> 
> 
> 
> --
> Greg Sabino Mullane greg@turnstep.com
> End Point Corporation
> PGP Key: 0x14964AC8 200811281627
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
> 
> iEYEAREDAAYFAkkwY5AACgkQvJuQZxSWSsgK8gCeIeAJ1P45EOciwYOBlseezjMt
> s5EAoM01zRA41nqYJnt4YzY8cmy6SOtc
> =J1YY
> -----END PGP SIGNATURE-----
> 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +