如何将plpgsql中的查询错误保存到表中?

问题描述

我需要在表中保存由INSERT或UPDATE返回的错误代码(SQLSTATE)和错误消息(SQLERRM).我的过程必须执行INSERT，并且如果发生错误，则必须将其保存到适当的表中.

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.

但是问题是，如果我使用EXCEPTION块，则当发生错误时，事务将中止并且之后的任何命令都无法执行.

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.

如何使用PLPGSQL将查询返回的错误保存在表中?

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

推荐答案

有两种可能的解决方案:

There are two possible solutions:

1. 使用PostgreSQL日志的CSV格式.稍后，您可以通过\ copy语句将pg日志导入到表中.如果可能的话，最好使用这种方式，因为它对性能的负面影响最小.

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.

您可以通过更多技术来模拟自主交易

You can emulate autonomous transactions via more techniques

• PostgreSQL dblink
• PostgreSQL FDW驱动程序

基于 dblink 的示例(乔恩·罗伯茨和我) :

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;

其他一些例子:

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

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

这篇关于如何将plpgsql中的查询错误保存到表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！