1

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().

nib0
  • 61
  • 1
  • 5

2 Answers2

3

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.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • Exactly. Probably the OP has some application with more than one connection, needing a two phase commit? – wildplasser Mar 23 '12 at 13:38
  • Could you please elaborate on that? I was assuming exactly the same however when I call a procedure which returns a cursor I want to iterate over, and at some point I trigger an exception, this is what I get: "ERROR: current transaction is aborted, commands ignored until end of transaction block". So from what I understand I have to catch the exception in my procedure in order to rollback. Am I wrong? – nib0 Mar 23 '12 at 13:54
  • Good point. Everything is rolled back, no use in trying to clean anything up. However, there are rare exceptions like the effects of dblink calls that are never rolled back. What the OP would need in such a case is to call `RAISE;` at the end of the exception block, I amended my answer accordingly. – Erwin Brandstetter Mar 23 '12 at 14:06
  • Could you explain what exactly dblink calls are? – nib0 Mar 23 '12 at 14:16
  • @NikosBogiatzoglou: dblink is an extension that lets you execute SQL in other Postgres databases. I added a link to the manual in my answer. – Erwin Brandstetter Mar 23 '12 at 14:58
  • @NikosBogiatzoglou: when you are getting the "commands ignored until end of transaction block" error, this indicates that the current transaction has been marked for rollback. any further statements will produce this error, except for either "commit" or "rollback", either of which will "roll back" the transaction (finish and mark as not committed, in postgresql terms). – araqnid Mar 23 '12 at 16:51
  • @araqnid: I just noticed your last reply, hope I can still ask a question about it. In my understanding what you describe is the reason I need exception handling. If I iterate in my program using a PostgreSQL cursor, and I trigger an exception, shouldn't I catch it and rollback? Maybe I misunderstand something? – nib0 Mar 26 '12 at 11:05
  • Never mind the last question, I've found the problem and you were right. I was having a transaction within a transaction so when the inner transaction failed, the outer one had to be explicitly rolled back. – nib0 Mar 26 '12 at 12:58
1

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 ...

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228