I have two separate ideas, but they are kind of connected,
(1) Make the detailed error message available in SPs and not only the short error message (SQLERRM)
When debugging errors in stored procedures, I often add an exception handler and print the values of declared variables to the log.
Unfortunately, the original detailed error message is then lost, since the SQLERRM only contains the short message.
The detailed error message contains valuable information and it would be good if it could be made accessible within the exception handler code.
Example of detailed error message: "Process 28420 waits for ShareLock on transaction 1421227628; blocked by process 20718."
The SQLERRM in this case only contains "deadlock detected".
If you would add a "EXCEPTION WHEN deadlock_detected" to catch this error, it would be nice if this detailed error message could still be written to the log, in addition to your own customized message, containing the values of the declared variables you need to view.
The detailed error message is available in edata->detail, while SQLERRM is in edata->message.
Perhaps we could name it SQLERRD?
(2) New log field showing current values of all declared variables
Instead of using RAISE DEBUG or customizing error messages using exception handlers, such as,
EXCEPTION WHEN deadlock_detected
RAISE '% var_foo % var_bar %', SQLERRM, var_foo, var_bar USING ERRCODE = 'deadlock_detected';
It would be very convenient if you could enable a log setting to write all declared variables current values directly to the CSV log, for all errors, to avoid the need to manually edit stored procedures to write variable values to the log, which also means you have to wait again for the same error to occur again, which might never happen if you have unlucky.
Instead of a new CSV log field, perhaps the setting when switch on could append the info to the already existing "hint" field?
Example: hint: "var_foo=12345 var_bar=67890"
This would be of great help to faster track down errors.
Thoughts?
Best regards,
Joel Jacobson