Re: assert in nested SQL procedure call in current HEAD

On 6/10/18 15:06, Dmitry Dolgov wrote:
>> I added it to the open items list since nobody else seems to have taken
>> notice; from Tom's linked message it seems this should be Peter E's bag?
> I've taken a look at this - indeed, the situation looks similar to what
> described in the linked message, namely after a transaction rollback and
> creation of a new one no active snapshot was pushed. But in this particular
> case the timeframe without an active snapshot is actually limited and includes
> only some initialization and planning activity (after that a new one is
> pushed). The commentary says that "Planner must have a snapshot in case it
> calls user-defined functions." - I tried to simulate this in order to see what
> would happen, but got no errors. Is there a chance that it's an outdated
> Assert?

The problem with these nested procedure calls is that if the nested call
does a commit, you would get the dreaded "snapshot %p still active"
warning. So PL/pgSQL in combination with SPI is going out of its way to
make sure that there is no active snapshot when we call out to
ProcessUtility() for the nested CALL. However, if whatever the nested
CALL executes requires an active snapshot to be set, then there is this
problem. Apparently, the LANGUAGE SQL procedure requires a snapshot to
be set previously. This is not a problem, for example, if you replace
the example procedure with LANGUAGE plpgsql, which does its own snapshot
management. So perhaps a fix here is to teach the LANGUAGE SQL handler
to set a snapshot if there isn't one already. That would make the
different language handlers behave consistently. (The alternative is to
get rid of the warning in snapmgr.c.)

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services