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

recall!

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

Marc

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Marc Mamin
> Sent: Donnerstag, 4. Juni 2015 10:47
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general