Re: Statement-level triggers and inheritance

Added to TODO:

Have statement-level triggers fire for all tables in an
inheritance 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(at)turnstep(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +