Forum OpenACS Q&A: Re: Obtaining the real DB error

Posted by Randy O'Meara on
For example, there is a table defined with a unique constraint on the name column, which is text. A form is filled out with ad_form and submitted. A record insert is attempted with a duplicate name. An error is generated at the pl/pgsql level  when the insert is performed since the unique constraint is violated. The insert query actually utilizes the xql mechanism and is performed by a call to a defined __new pl/pgsql function, so I do have a function where I can react to errors very close to the DB and possibly pass the error details back to the caller.

In this simple example, without knowing the actual DB error, I might just blindly guess that this particular constraint has been violated and inform the user to change the name. My guess could definitely be wrong. Bad Solution...

Add another constraint to the same table and the guessing approach is not valid at all. Even with just the single constraint, the cause of a DB error could be completely unrelated to it.

I could retrieve all the names of the objects (or cache all the names) and verify the constraint is honored before attempting the __new call. Another (really) Bad Solution...

I know that the cause of a DB error is known at the pl/pgsql level. I'm just looking for a general way to retrieve this information so that I can deal with the cause programatically, in my tcl program... for error control and recovery. Is there no interface or mechanism available to retrieve these details? I have seen no examples of this if it exists.

For what it's worth, the table is a subtyped acs_object and I've followed the standard convention of defining *__{new,delete,name} pl/pgsql functions and used define_function_args on each.

Thanks for taking the time to help.