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

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Donnerstag, 4. Juni 2015 15:56
> To: Marc Mamin
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Row visibility issue with consecutive triggers,
> one being DEFERRED
>
> Marc Mamin <M(dot)Mamin(at)intershop(dot)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