SPI and transactions

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 18 November 2015 at 18:18, Konstantin Knizhnik <k.knizhnik@postgrespro.ru

wrote:

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 doubt the raw receiver approach can ever really lead to a complete
replication solution, so I'm not completely convinced this is a problem
worth solving. That tool is a great demo and learning utility, but that's
very much what I see it as. (Then again, I would say that, wouldn't I? I
have my own work in the running in the same space. Make of that what you
will.)

I suspect you'd need a way to invoke an incomplete SQL parser that can
parse the SQL well enough to give you a TransactionStmt or tell you "I
dunno what it it, but it doesn't look like a TransactionStmt".

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 18 November 2015 at 18:18, 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?

Thanks in advance,
Konstantin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services