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

> -----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 DEFERRED
>
> 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?


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
anUPDATE 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