CODEWITHSUNDEEP
databasepostgresqlexceptionlibpq
nib0
nib0

Reputation: 61

Returning the error details to the calling function after catching an exception in Postgresql

I'm adding exception handling to PostgreSQL stored procedures in order to automatically rollback the transactions after an error occurs.

My problem is that once I catch the exception, then I cannot return the details of the error to the calling C program which uses libpq.

The Severity, SQLSTATE, Primary, Detail and Hint are all null. Is there a way to return these after catching the exception?

The libpq function I use to collect these values is PQresultErrorField().

Upvotes: 1

Views: 1502

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

I have recently posted a complete solution how to add a CONTEXT to error messages on dba.SE. The trick is to call a function that raises the error / warning / notice/ etc.

I realize now that your case may be different. My workaround is for adding a CONTEXT to exceptions that you raise yourself.

If you catch an exception to do stuff before the transaction is rolled back, you may want to add a RAISE without parameters at the end of your exception block:

RAISE;

The manual about RAISE:

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's EXCEPTION clause; it causes the error currently being handled to be re-thrown.

However, as @araqnid pointed out, there is not use in an exception block if you are going to propagate the error anyway and everything is rolled back. This solution is only useful for the rare cases where certain changes are persistent and cannot be rolled back, like dblink calls ...

Upvotes: 1

araqnid
araqnid

Reputation: 133482

Given than an exception will automatically make a postgresql transaction roll back, why catch it at all? Catching exceptions is usually only useful if you want to usefully recover from the error, not propagate it.

Upvotes: 3

Related Questions