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@xxxxxxxxxxxxx> wrote:
Willy-Bas Loos <willybas@xxxxxxxxx> 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
