Hi,
Myself Jayesh Masand and I am working in Hughes Software Systems. We are using
Postgres database in one of our project.
In some specific implementation, I require to ignore exceptions with in single
transaction. For e.g. if in a single transaction, i want
to do say 10 operations, then if one of the operation fails and gives some SQL
exception, then i want to ignore that exception and
continue the transaction. Can i do this?
In my specific scenarios, it shows following Log messages
ERROR: Cannot insert a duplicate key into unique index conf_em110_37_pkey
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
NOTICE: current transaction is aborted, queries ignored until end of
transaction block
In documentation for Postgres, we have following material
PostgreSQL does not have a very smart exception handling model. Whenever the
parser, planner/optimizer or executor decide
that a statement cannot be processed any longer, the whole transaction gets
aborted and the system jumps back into the main
loop to get the next query from the client application. It is possible to hook
into the error mechanism to notice that this happens.
But currently it is impossible to tell what really caused the abort
(input/output conversion error, floating-point error, parse error).
And it is possible that the database backend is in an inconsistent state at this
point so returning to the upper executor or issuing
more commands might corrupt the whole database.
Thus, the only thing PL/pgSQL currently does when it encounters an abort during
execution of a function or trigger procedure is to
write some additional NOTICE level log messages telling in which function and
where (line number and type of statement) this happened.
The error always stops execution of the function.
Please reply ASAP that can we ignore the exception and continue with other
operations of transaction?
Regards
Jayesh
