Thread: Row visibility issue with consecutive triggers, one being DEFERRED

Hello,

The test below is running fine
but if you add the trigger push_foo_tr (uncomment)
then the exception is raised.

It seems that this additional trigger to be called at the first place
changes the deferrable status of the second one.

Is this an expected behaviour ?

regards,

Marc Mamin



DROP TABLE IF EXISTS foo;
DROP FUNCTION IF EXISTS push_foo_trf();
DROP FUNCTION IF EXISTS check_foo_trf();

CREATE TABLE foo (id int, v int);
INSERT INTO foo select 1,3;
INSERT INTO foo select 2,6;


CREATE OR REPLACE FUNCTION push_foo_trf () returns trigger AS $$
BEGIN
  UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id;
RETURN NEW;
END; $$ language plpgsql;

CREATE OR REPLACE FUNCTION check_foo_trf () returns trigger AS $$
DECLARE
  visible_sum int;
  table_view text;
BEGIN
  SELECT sum(v) from foo into visible_sum;
  IF 9 <> visible_sum THEN
     SELECT string_agg (id||', '||v ,E' | ') FROM foo INTO table_view;
     raise exception 'Check failed. Visible:  %',table_view;
  END IF;
RETURN NULL;
END; $$ language plpgsql;

--CREATE TRIGGER push_foo_tr
--  AFTER UPDATE ON foo
--      FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();

CREATE CONSTRAINT TRIGGER check_foo_tr
  AFTER UPDATE ON foo
    DEFERRABLE INITIALLY DEFERRED
      FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();

BEGIN;
  update foo set v=6 WHERE id = 1;
  update foo set v=3 WHERE id = 2;
END;

--cleanup
DROP TABLE IF EXISTS foo;
DROP FUNCTION IF EXISTS push_foo_trf();
DROP FUNCTION IF EXISTS check_foo_trf();

recall!

this self containing case works well if I call the correct functions in the triggers :)

Marc


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Marc Mamin
> Sent: Donnerstag, 4. Juni 2015 10:47
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Row visibility issue with consecutive triggers, one
> being DEFERRED
>
> Hello,
>
> The test below is running fine
> but if you add the trigger push_foo_tr (uncomment) then the exception
> is raised.
>
> It seems that this additional trigger to be called at the first place
> changes the deferrable status of the second one.
>
> Is this an expected behaviour ?
>
> regards,
>
> Marc Mamin
>
>
>
> DROP TABLE IF EXISTS foo;
> DROP FUNCTION IF EXISTS push_foo_trf();
> DROP FUNCTION IF EXISTS check_foo_trf();
>
> CREATE TABLE foo (id int, v int);
> INSERT INTO foo select 1,3;
> INSERT INTO foo select 2,6;
>
>
> CREATE OR REPLACE FUNCTION push_foo_trf () returns trigger AS $$ BEGIN
>   UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id; RETURN NEW;
> END; $$ language plpgsql;
>
> CREATE OR REPLACE FUNCTION check_foo_trf () returns trigger AS $$
> DECLARE
>   visible_sum int;
>   table_view text;
> BEGIN
>   SELECT sum(v) from foo into visible_sum;
>   IF 9 <> visible_sum THEN
>      SELECT string_agg (id||', '||v ,E' | ') FROM foo INTO table_view;
>      raise exception 'Check failed. Visible:  %',table_view;
>   END IF;
> RETURN NULL;
> END; $$ language plpgsql;
>
> --CREATE TRIGGER push_foo_tr
> --  AFTER UPDATE ON foo
> --      FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();
>
> CREATE CONSTRAINT TRIGGER check_foo_tr
>   AFTER UPDATE ON foo
>     DEFERRABLE INITIALLY DEFERRED
>       FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();
>
> BEGIN;
>   update foo set v=6 WHERE id = 1;
>   update foo set v=3 WHERE id = 2;
> END;
>
> --cleanup
> DROP TABLE IF EXISTS foo;
> DROP FUNCTION IF EXISTS push_foo_trf();
> DROP FUNCTION IF EXISTS check_foo_trf();
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Marc Mamin <M.Mamin@intershop.de> writes:
> The test below is running fine
> but if you add the trigger push_foo_tr (uncomment)
> then the exception is raised.

Doesn't that trigger result in infinite recursion?

> CREATE OR REPLACE FUNCTION push_foo_trf () returns trigger AS $$
> BEGIN
>   UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id;
> RETURN NEW;
> END; $$ language plpgsql;

> --CREATE TRIGGER push_foo_tr
> --  AFTER UPDATE ON foo
> --      FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();

AFAICS, each trigger firing would re-queue another one because of
the fresh UPDATE.

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Donnerstag, 4. Juni 2015 15:56
> To: Marc Mamin
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Row visibility issue with consecutive triggers,
> one being DEFERRED
>
> Marc Mamin <M.Mamin@intershop.de> writes:
> > The test below is running fine
> > but if you add the trigger push_foo_tr (uncomment) then the exception
> > is raised.
>
> Doesn't that trigger result in infinite recursion?


yeah,
adding a modify check fix it:
UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id
AND (id,v) IS DISTINCT FROM (NEW.id,NEW.v);
RETURN NEW;


I was trying to build a self containing case to track an issue with complex cross table validations,
but at the end it appeared, that the trigger were correct and raised an exception due to a missing WHERE Clause within
anUPDATE statement. 

I just didn't didn't trust my own triggers :)

sorry for the noise.

Marc Mamin


>
> > CREATE OR REPLACE FUNCTION push_foo_trf () returns trigger AS $$
> BEGIN
> >   UPDATE foo SET (id,v) = (NEW.id,NEW.v) WHERE id=NEW.id; RETURN NEW;
> > END; $$ language plpgsql;
>
> > --CREATE TRIGGER push_foo_tr
> > --  AFTER UPDATE ON foo
> > --      FOR EACH ROW EXECUTE PROCEDURE check_foo_trf();
>
> AFAICS, each trigger firing would re-queue another one because of the
> fresh UPDATE.
>
>             regards, tom lane