Обсуждение: 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
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".
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