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
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 DEFERREDHello,
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
--
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----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 DEFERREDMarc 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 an UPDATE 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general