Re: implicit transaction changes trigger behaviour

Thank you so much, the "stable" thing was it.
I'm not sure if it is underdocumented, i clearly didn't adhere to the rule
that a stable function " is guaranteed to return the same results given the
same arguments for all rows within a single statement".
BTW in my example i made a mistake too, but that was beside the point
really.

Cheers,
Willy-Bas

On Thu, Aug 29, 2019 at 3:35 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Willy-Bas Loos <willybas(at)gmail(dot)com> writes:
> > I currently have a fairly complex use case to solve and one thing i tried
> > was a deferred constraint trigger. I'm not sure if this solution is the
> way
> > to go, but anyway: As i was testing my code, i noticed that the trigger
> > behaves differently depending on whether or not i explicitly use BEGIN
> and
> > COMMIT, even though there is only 1 query in the transaction.
> > I am wondering if this is a bug in postgresql?
>
> I think the issue is that you marked the trigger as STABLE. That causes
> it to use the calling query's snapshot so it doesn't see the updates,
> if it's fired during the delete query and not during the subsequent
> COMMIT. If I remove the STABLE label then it works as you expect.
>
> This is probably under-documented but I'm not sure that it should be
> considered a bug.
>
> The trigger seems a bit broken besides that, in that the comments claim it
> has something to do with the OLD row's id field(s) but the query is not in
> fact taking that into account.
>
> regards, tom lane
>

--
Willy-Bas Loos