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

"Greg Sabino Mullane" <greg(at)turnstep(dot)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(at)turnstep(dot)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(at)users(dot)sourceforge(dot)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(at)turnstep(dot)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(at)turnstep(dot)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(at)turnstep(dot)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 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. +

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

Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)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(at)gmx(dot)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 them
fire only on the parent table, and fire child table triggers only where
appropriate

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