Row visibility issue with consecutive triggers, one being DEFERRED

[Marc Mamin <M.Mamin@xxxxxxxxxxxx>]

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