SET TRANSACTION in PL/pgSQL
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t38287psql -h localhost -U postgresBuilt from patchset v3 (message #3), July 27, 2026 at 08:49 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t38287_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t38287_3 && git checkout t38287_3Patchset v3 (message #3) is on t38287_3
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.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
v1-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patchtext/plain; charset=UTF-8; name=v1-0001-PL-pgSQL-Add-support-for-SET-TRANSACTION.patch; x-mac-creator=0; x-mac-type=0Download+156-3
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