Thread: SPI and transactions

Hello,

SPI was originally developed for execution SQL statements from C user 
defined functions in context of existed transaction.
This is why it is not possible to execute any transaction manipulation 
statement (BEGIN, COMMIT, PREPARE,...) using 
SPI_execute:SPI_ERROR_TRANSACTION is returned.

But now SPI is used not only inside UDFs. It is also used in background 
workers. For example in receiver_raw, written by Michael Paquier (I lot 
of thanks Michael, understand logical replication without them will be 
much more difficult).
Right now transactions have to be started by background worker using  
StartTransactionCommand().
So receiver_raw is not able to preserve master's transaction semantic 
(certainly it can be implemented).

I wonder whether SPI can be extended now to support transaction 
manipulation functions when been called outside transaction context? Or 
there are some principle problem with it?

Thanks in advance,
Konstantin

On Wed, Nov 18, 2015 at 5:18 AM, Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
> Hello,
>
> SPI was originally developed for execution SQL statements from C user
> defined functions in context of existed transaction.
> This is why it is not possible to execute any transaction manipulation
> statement (BEGIN, COMMIT, PREPARE,...) using
> SPI_execute:SPI_ERROR_TRANSACTION is returned.
>
> But now SPI is used not only inside UDFs. It is also used in background
> workers. For example in receiver_raw, written by Michael Paquier (I lot of
> thanks Michael, understand logical replication without them will be much
> more difficult).
> Right now transactions have to be started by background worker using
> StartTransactionCommand().
> So receiver_raw is not able to preserve master's transaction semantic
> (certainly it can be implemented).
>
> I wonder whether SPI can be extended now to support transaction manipulation
> functions when been called outside transaction context? Or there are some
> principle problem with it?

I think SPI pretty fundamentally assumes we're inside a transaction,
and that we'll still be at the same transaction nesting depth when we
get done with SPI.  For example, SPI_connect() allocates memory in
TopTransactionContext.  So I doubt that it will work out well to try
to solve the problem you're aiming to fix in this particular way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company