SET TRANSACTION in PL/pgSQL

Hi!

On Wed, Feb 28, 2018 at 11:45 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:

Currently, you can't run SET TRANSACTION in PL/pgSQL. A normal SQL
command run inside PL/pgSQL acquires a snapshot, but SET
TRANSACTION does not work anymore if a snapshot is set. Here is a patch
to work around that by handling this command separately. I have coded
this here bypassing SPI entirely. But there is some overlap with the
no_snapshot option in the patch "PL/pgSQL nested CALL with
transactions", so maybe a better solution will arise. This will also
inform how to tackle this in other PLs.

I didn't dig deeply into this subject. But should we rather teach SPI to
execute
utility statements without taking snapshot when not necessary. That seems
like what executor do for client provided queries. And that seems a bit
unlogical
that SPI behaves differently.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 3/15/18 17:49, Alexander Korotkov wrote:

I didn't dig deeply into this subject.  But should we rather teach SPI
to execute
utility statements without taking snapshot when not necessary.  That seems
like what executor do for client provided queries.  And that seems a bit
unlogical
that SPI behaves differently.

Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.

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

On 03/29/2018 06:30 PM, Peter Eisentraut wrote:

On 3/15/18 17:49, Alexander Korotkov wrote:

I didn't dig deeply into this subject.  But should we rather teach SPI
to execute
utility statements without taking snapshot when not necessary.  That seems
like what executor do for client provided queries.  And that seems a bit
unlogical
that SPI behaves differently.

Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.

Yeah, doing that using SPI seems much cleaner and more like the rest of
the commands. Most of the patch is boilerplate to support the grammar,
and the one interesting piece exec_stmt_set seems fine to me.

Barring any objections, I'll mark it as RFC tomorrow morning.

regards

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

On 4/4/18 13:53, Tomas Vondra wrote:

Here is the same patch rewritten using SPI, using the new no_snapshots
facility recently introduced.

Yeah, doing that using SPI seems much cleaner and more like the rest of
the commands. Most of the patch is boilerplate to support the grammar,
and the one interesting piece exec_stmt_set seems fine to me.

Barring any objections, I'll mark it as RFC tomorrow morning.

You apparently didn't, but I committed it anyway. ;-)

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