I'm using SPI_prepare to do some dynamic SQL stuff in the backend. Some
errors result in a call to the elog routines with ERROR level, which in turn
causes log printout and a longjmp. Since I want to trap those errors and try
an alternative I tried the following:

    sigjmp_buf saveRestart;
    memcpy(&saveRestart, &Warn_restart, sizeof(saveRestart));
    if(sigsetjmp(Warn_restart, 1) != 0)
    {
        memcpy(&Warn_restart, &saveRestart, sizeof(Warn_restart));
        /* Handle error here */
        return NULL;
    }
    void* plan = SPI_prepare(...);
    memcpy(&Warn_restart, &saveRestart, sizeof(Warn_restart));
    return plan;

My question is, at the point of /* Handle error here */, how do I get hold
of the error information? Judging from the code in elog.c, this info is sent
do the server and/or client log and then lost before the longjmp is made. Is
that the way it's supposed to be?

Regards,

Thomas Hallgren



Mon, 19 Jan 2004 23:21:21 +0100
"Thomas Hallgren" <thhal@mailblocks.com>
SPI_prepare and error recovery
"Thomas Hallgren" <thhal@mailblocks.com> writes:
> My question is, at the point of /* Handle error here */, how do I get hold
> of the error information?

You don't.  The above coding technique is entirely unsafe anyway,
because it relies on the assumption that the system is still in a good
state to continue the transaction, which is in general not true.

If we had nested-transaction support you could arrange for an inner
transaction around the thing you want to retry; but we don't, and this
problem of cleaning up after an error is one of the biggest reasons
why not.

			regards, tom lane

Mon, 19 Jan 2004 18:14:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: SPI_prepare and error recovery 
As you already know, I'm working on a backend Java mapping. I'm using the
SPI routines to implement java.sql interfaces such as Connection,
PreparedStatement, and ResultSet, that will utilize the current transaction.
Using them, you will work with a try/catch/finally metaphor and all
exceptions, short of OutOfMemory errors and alike, are trappable. To just
bypass the catch/finally using a longjmp is a somewhat serious violation of
rules.

If the statement indeed does invalidate the transaction, then I can
understand that further database access during that particular would be
futile. But what if it doesn't (a prepare really shouldn't)? Or what if I
want some error recovery that doesn't access the database at all? Perhaps I
just want to send a message on a socket, write something to a file, or
whatever, before the error is propagated.

But OK, the postgresql SPI and backend code was not written with try/catch
etc. in mind (nor multithreading :-) ) so I guess I have to live with the
limitations and try to make the best of it.

Meanwhile, perhaps someone else should consider a solution where:
a) It is possible to trap an error and retrieve the cause of it (analog with
try/catch)
b) The catching of an error will inhibit that the error is propagated to the
client.
c) An error that was caught, can be reactivated (re-thrown).
d) Errors that invalidates the current transaction sets a flag that makes
further calls impossible (generates a new error) until the transaction has
ended.

IMO, that should be doable without nested transactions.

Regards,

Thomas Hallgren


"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:19071.1074554092@sss.pgh.pa.us...
> "Thomas Hallgren" <thhal@mailblocks.com> writes:
> > My question is, at the point of /* Handle error here */, how do I get
hold
> > of the error information?
>
> You don't.  The above coding technique is entirely unsafe anyway,
> because it relies on the assumption that the system is still in a good
> state to continue the transaction, which is in general not true.
>
> If we had nested-transaction support you could arrange for an inner
> transaction around the thing you want to retry; but we don't, and this
> problem of cleaning up after an error is one of the biggest reasons
> why not.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>



Tue, 20 Jan 2004 01:20:51 +0100
"Thomas Hallgren" <thhal@mailblocks.com>
Re: SPI_prepare and error recovery