recall! this self containing case works well if I call the correct functions in the triggers :) Marc > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Marc Mamin > Sent: Donnerstag, 4. Juni 2015 10:47 > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
