SPI_execute error handling

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

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

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