How to save query errors in plpgsql to a table?

Question

I need to save in a table the error code (SQLSTATE) and the error message (SQLERRM) returned by an INSERT or an UPDATE. My procedure must execute an INSERT, and if an error occurs, it must be saved into an apposite table.

But the problem is that if I use an EXCEPTION block, when an error occurs the transaction is aborted and any command after cannot execute.

How can I save the error returned by a query in a table using PLPGSQL?

Answer 1

There are two possible solutions:

1. use a CSV format of PostgreSQL log. Later you can import pg log to table by \copy statement. This way is preferred if it is possible, because it has minimal negative impact on performance.

2. You can emulate autonomous transactions via more techniques

   • PostgreSQL dblink
   • PostgreSQL FDW driver

Example of dblink based emulation (by Jon Roberts and me):

CREATE OR REPLACE FUNCTION fn_log_error(_function varchar,
                                        _location int, _error varchar) 
RETURNS void AS $$
DECLARE
  _sql varchar;
  _exec_error varchar;
BEGIN
  PERFORM dblink_connect('autonom_connection', 'dbname=...'); 

  _sql := format('INSERT INTO error_log (function_name, location,
                    error_message, error_time) VALUES (%L, %s, %L, %L)',
           _function, _location, _error, clock_timestamp());

  PERFORM dblink_exec('autonom_connection', _sql, false);
  _exec_error := dblink_error_message('autonom_connection');

  IF position('ERROR' in _exec_error) > 0 
      OR position('WARNING' in _exec_error) > 0 THEN
    RAISE EXCEPTION '%', _exec_error;
  END IF;

  PERFORM dblink_disconnect('autonom_connection');
EXCEPTION
   WHEN others THEN
     PERFORM dblink_disconnect('autonom_connection');
     RAISE EXCEPTION '(%)', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Some other examples:

• http://raghavt.blogspot.cz/2012/05/autonomous-transaction-in-postgresql-91.html

• http://tapoueh.org/blog/2013/10/14-autonomous-transactions