Re: Unexpected results from CALL and AUTOCOMMIT=off

[ redirecting to pgsql-hackers ]

I wrote:
> I agree that this looks like a bug, since your example shows that the
> same function works as-expected in an ordinary expression but not in
> a CALL. The dependency on AUTOCOMMIT (that is, being within an outer
> transaction block) seems even odder. I've not dug into it yet, but
> I suppose we're passing the wrong snapshot to the CALL arguments.

I poked into this and found that the source of the problem is that
plpgsql's exec_stmt_call passes allow_nonatomic = true even when
it's running in an atomic context. So we can fix it with basically
a one-line change:

- options.allow_nonatomic = true;
+ options.allow_nonatomic = !estate->atomic;

I'm worried about whether external callers might've made a comparable
mistake, but I think all we can do is document it a little better.
AFAICS there isn't any good way for spi.c to realize that this mistake
has been made, else we could have it patch up the mistake centrally.
I've not attempted to make those doc updates in the attached draft
patch though, nor have I added a test case yet.

Before realizing that this was the issue, I spent a fair amount of
time on the idea that _SPI_execute_plan() was doing things wrong,
and that led me to notice that its comment about having four modes
of snapshot operation has been falsified in multiple ways. So this
draft does include fixes for that comment.

Thoughts?

regards, tom lane