Re: proposal: catch warnings

On Sun, 2007-01-07 at 11:20 +0100, Pavel Stehule wrote:
> >
> >On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote:
> >
> > > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires
> >that
> > > warnings are catchable too. Simply solution's is adding one callback to
> > > error's processing of errors on level WARNING.
> >
> >Exceptions are run within their own subtransaction, so the exception
> >handling code runs separately.
> >
> >Does the PSM warning error handler run in the same transaction or a
> >separate subtransaction? Can transaction execution continue afterwards?
> >
>
> It's depend. Continue and exit warning handlers run in the same transaction,
> undo handler has separate subtransaction. It works well. For patterns used
> in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not
> found).

Hmmm. SQLSTATE 02000 NO_DATA doesn't seem to be raised anywhere by the
backend, though it is listed by ECPG.

Are you thinking of the special variable FOUND, which doesn't raise an
exception in PL/pgSQL, or the PostgreSQL PL/pgSQL exception:
NO_DATA_FOUND (SQLSTATE P0002) which isn't actually an SQL ERROR at all.
(Definitely an exception in PL/SQL?)

ISTM that if we have an exception defined like this in PL/pgSQL

EXCEPTION
WHEN NO_DATA_FOUND THEN
block
END;

that we wouldn't need to wrap it in a sub-transaction, because the
earlier statements need not be rolled back when it occurs. Perhaps you
can scan for this condition in the PSM code, rather than getting the
backend to throw a different kind of error?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com