Thread: SPI_execute error handling

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

> 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