Обсуждение: SET TRANSACTION in PL/pgSQL
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
Вложения
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
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