Thread: Statement-level triggers and inheritance
-----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-----
"Greg Sabino Mullane" <greg@turnstep.com> writes: > Looks like inheritance causes a statement-level trigger to fire on > the last evaluated table in the inheritance chain. Is this the > desired behavior? Hm, I think whoever wrote the statement-level trigger code completely forgot to consider the possibility of multiple target relations. It's not even consistent between BEFORE and AFTER triggers for this case. My feeling is that it ought to fire such triggers on *each* target. regards, tom lane
On Friday 28 November 2008 16:59:19 Tom Lane wrote: > "Greg Sabino Mullane" <greg@turnstep.com> writes: > > Looks like inheritance causes a statement-level trigger to fire on > > the last evaluated table in the inheritance chain. Is this the > > desired behavior? > > Hm, I think whoever wrote the statement-level trigger code completely > forgot to consider the possibility of multiple target relations. It's > not even consistent between BEFORE and AFTER triggers for this case. > > My feeling is that it ought to fire such triggers on *each* target. > This would amount to statement level triggers firing multiple times per statement wouldn't it? That behavior might be rather surprising for folks. I guess the alternative is to have it fire only on the parent in an inheritance stack. I'm not sure that's much more defensible, but maybe it's more practical? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com
Robert Treat <xzilla@users.sourceforge.net> writes: > On Friday 28 November 2008 16:59:19 Tom Lane wrote: >> My feeling is that it ought to fire such triggers on *each* target. > This would amount to statement level triggers firing multiple times per > statement wouldn't it? No, because they'd be different triggers. A trigger on a parent table has got nothing to do with triggers on children. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 >>> My feeling is that it ought to fire such triggers on *each* target. >> This would amount to statement level triggers firing multiple times per >> statement wouldn't it? > No, because they'd be different triggers. A trigger on a parent table > has got nothing to do with triggers on children. I think Robert's point is that, even though they are different triggers, we would be firing more than one "statement-level" trigger per statement. Which feels a little odd, but is perfectly valid. However, I strongly lean towards the behavior in this case being to only fire the parent statement-level trigger. I could support the other way as well: I'm not going to add any triggers to the children anyway, so as long as the parent fires, I'll be happy. Right now, this is a serious bug for my app, as there is no INSERT INTO ONLY syntax, and thus there is no way to effect a statement-level trigger using an insert on a table that is inherited from. My workaround is to insert and then update all the rows. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200812011030 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkk0AzUACgkQvJuQZxSWSsgFMQCg1aqkiX4CdEZL7+jXEDof8/xm lMQAoOYyoW2gp/7UGqq9kXul9FOqxk4o =m6ua -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes: > However, I strongly lean towards the behavior in this case being to only > fire the parent statement-level trigger. I could support the other way as > well: I'm not going to add any triggers to the children anyway, so as long > as the parent fires, I'll be happy. Right now, this is a serious bug for > my app, as there is no INSERT INTO ONLY syntax, and thus there is no way > to effect a statement-level trigger using an insert on a table that is > inherited from. My workaround is to insert and then update all the rows. You're not making a lot of sense here, because INSERT always affects exactly the named table. It's UPDATE and DELETE where the behavior is debatable. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > You're not making a lot of sense here, because INSERT always affects > exactly the named table. It's UPDATE and DELETE where the behavior > is debatable. *blink* Ah, right you are, had a typo in my testing script. Excellent news, I'm now officially okay with either solution then. Thanks! - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200812011431 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkk0O5YACgkQvJuQZxSWSsg20QCcCyWXQiIb8AwBJ0DixdJHXce5 2IAAoN1lNij5Oqz0ay4kDnvyJ72xVheR =sW9/ -----END PGP SIGNATURE-----
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. +
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@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. +
Peter Eisentraut <peter_e@gmx.net> writes: > 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. I think the consensus was that each table should have its own statement triggers (if any) fire. Which is one possible reading of Bruce's TODO item, but it's surely not clearly worded. regards, tom lane
On Sunday 18 January 2009 08:24:47 Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > 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. > > I think the consensus was that each table should have its own statement > triggers (if any) fire. Which is one possible reading of Bruce's TODO > item, but it's surely not clearly worded. We should also consult the SQL standard. Its language regarding inheritance is sometimes not in line with our implementation (see recent discussion about GRANT).
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > 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. > > I think the consensus was that each table should have its own statement > triggers (if any) fire. Which is one possible reading of Bruce's TODO > item, but it's surely not clearly worded. Sorry I had that wording wrong; TODO updated to: When statement-level triggers are defined on a parent table, have themfire only on the parent table, and fire child tabletriggers only whereappropriate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +