Re: Statement-level triggers and inheritance

On Thursday 15 January 2009 02:08:42 Bruce Momjian wrote:
> Added to TODO:
>
> Have statement-level triggers fire for all tables in an
> inheritance hierarchy

I don't think that was really the conclusion from the thread.

As far as I can interpret the opinions, statement level triggers should fire
on the parent table only, rather than on some child, as it currently does.

>
> ---------------------------------------------------------------------------
>
> 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. +