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

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