Thread: SPI_execute error handling

SPI_execute error handling

From
James Harper
Date:
The docs for SPI_execute at http://www.postgresql.org/docs/9.4/static/spi.html say:

"
Note that if a command invoked via SPI fails, then control will not be returned to your procedure. Rather, the
transactionor subtransaction in which your procedure executes will be rolled back. (This might seem surprising given
thatthe SPI functions mostly have documented error-return conventions. Those conventions only apply for errors detected
withinthe SPI functions themselves, however.) It is possible to recover control after an error by establishing your own
subtransactionsurrounding SPI calls that might fail. This is not currently documented because the mechanisms required
arestill in flux. 
"

so when my background worker executes "SELECT * FROM blah" and "blah" doesn't exist, i get:

2015-09-07 18:14:41 AEST [1958-44] ERROR:  relation "blah" does not exist at character 15
2015-09-07 18:14:41 AEST [1958-45] QUERY:  SELECT * FROM "blah"
2015-09-07 18:14:41 AEST [1889-18] LOG:  worker process: tds handler (PID 1958) exited with exit code 1
2015-09-07 18:14:41 AEST [1889-19] LOG:  unregistering background worker "tds handler"

And control is never returned to my worker, as expected.

How can I get control returned back to my worker so that I can give the client program a sensible error? I already
createa transaction like StartTransactionCommand(), but then maybe that isn't the subtransation that the cryptic
documentationabove refers to?? 

thanks

James


Re: SPI_execute error handling

From
Pavel Stehule
Date:


2015-09-07 11:04 GMT+02:00 James Harper <james@meadowcourt.org>:
The docs for SPI_execute at http://www.postgresql.org/docs/9.4/static/spi.html say:

"
Note that if a command invoked via SPI fails, then control will not be returned to your procedure. Rather, the transaction or subtransaction in which your procedure executes will be rolled back. (This might seem surprising given that the SPI functions mostly have documented error-return conventions. Those conventions only apply for errors detected within the SPI functions themselves, however.) It is possible to recover control after an error by establishing your own subtransaction surrounding SPI calls that might fail. This is not currently documented because the mechanisms required are still in flux.
"

so when my background worker executes "SELECT * FROM blah" and "blah" doesn't exist, i get:

2015-09-07 18:14:41 AEST [1958-44] ERROR:  relation "blah" does not exist at character 15
2015-09-07 18:14:41 AEST [1958-45] QUERY:  SELECT * FROM "blah"
2015-09-07 18:14:41 AEST [1889-18] LOG:  worker process: tds handler (PID 1958) exited with exit code 1
2015-09-07 18:14:41 AEST [1889-19] LOG:  unregistering background worker "tds handler"

And control is never returned to my worker, as expected.

How can I get control returned back to my worker so that I can give the client program a sensible error? I already create a transaction like StartTransactionCommand(), but then maybe that isn't the subtransation that the cryptic documentation above refers to??

Hi

When I need to solve similar situation, I take a code from plpgsql. look on function exec_stmt_block. Similar code is in plpgsql_check https://github.com/okbob/plpgsql_check/blob/master/plpgsql_check.c

Regards

Pavel


 

thanks

James


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

Re: SPI_execute error handling

From
James Harper
Date:
> Hi
>
>
> When I need to solve similar situation, I take a code from plpgsql.
> look on function exec_stmt_block. Similar code is in plpgsql_check
> https://github.com/okbob/plpgsql_check/blob/master/plpgsql_check.c
>

Thanks. Got it sorted!

James