Re: [GENERAL] SPI_execute error handling

[Pavel Stehule]

2015-09-07 11:04 GMT+02:00 James Harper :

> 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: [GENERAL] SPI_execute error handling

[James Harper]

> 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