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 <t...@sss.pgh.pa.us> wrote: > Willy-Bas Loos <willy...@gmail.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
