Hello 2011/4/25 Joel Jacobson <j...@gluefinance.com>: > 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? >
A magic variables like SQLERRM or SQLSTATE are little bit expensive - and isn't important if this variables are used or not. In this moment I am thinking so best methotd is enhancing GET DIAGNOSTICS statement - you can access to more informations than detail - hint, line, > (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. I realised similar tool in PSM0 https://github.com/okbob/plpsm0 see on executor.c It is little bit more harder then is looking - inside unrollbacked exception you can't access to system directories, and you can't to search a out functions. I found a workaround, but it is little bit strange. I am for this feature - just add note - inside executor is zero infrastructure for this. Regards Pavel Stehule > Thoughts? > > Best regards, > Joel Jacobson > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
