Re: SPI_execute error handling

2015-09-07 11:04 GMT+02:00 James Harper <james(at)meadowcourt(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>