Re: Row visibility issue with consecutive triggers, one being DEFERRED - Mailing list pgsql-general

From Marc Mamin
Subject Re: Row visibility issue with consecutive triggers, one being DEFERRED
Date
Msg-id B6F6FD62F2624C4C9916AC0175D56D8828BE56A1@jenmbs01.ad.intershop.net
Whole thread Raw
In response to Row visibility issue with consecutive triggers, one being DEFERRED  (Marc Mamin <M.Mamin@intershop.de>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Ravi Krishna
Date:
Subject: Automatic Client routing
Next
From: Robert Haas
Date:
Subject: Re: 9.4.1 -> 9.4.2 problem: could not access status of transaction 1