Обсуждение: Oracle error log table equivalent in postgresql

> Hi all,
> I am porting oracle procedures into postgresql code and some oracle
> procedure contain error log table and save exception code. I could
> not find any equivalent of it in postgresql.
>
>
> Please help if any pointer or lead to its solution.

Something like :

<..>
EXCEPTION
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS l_errno=RETURNED_SQLSTATE, l_errtext=MESSAGE_TEXT, l_errctx=PG_EXCEPTION_CONTEXT;
    PERFORM TRACE_ERROR(l_errno,l_errtext,l_errctx );
    RAISE EXCEPTION 'ERREUR %\n%\n%\nStackTrace : %',l_errno,l_errtext,l_errctx;
END;
$$ LANGUAGE PLPGSQL;



??

> Thanks, but it solve just part of the problem. In oracle insert
> statement executes and rejected rows are inserted in error log table
> in same transaction. But, its similar feature or workaround seems
> hard in postgresql.

It's not hard.

You may either intercept the exception( EXCEPTION WHEN OTHER ...) so that the transaction will not be rollbacked, so you can easily insert into another table what you want.


But, if you want to rollback the transaction with a RAISE EXCEPTION, you just have to insert in your log table using another transaction, for example with a loopback dblink.

Something like :

select setting INTO l_port FROM pg_settings WHERE name = 'port';
PERFORM * FROM dblink(
                FORMAT('dbname=%s user=%s port=%s',current_database(), current_user, l_port),
                FORMAT('INSERT INTO TRACE_ERROR(ernno,errmsg,errcontext) VALUES (%L,%L,%L);',l_errno,l_errmsg,l_errctx)
                ) AS p (ret text);

Otherwise, when you rollback, the INSERT into the error log table will be also rollbacked.

Please find below code, looks like, relate to your issue:

Refrence: https://stackoverflow.com/questions/53504234/pgsql-trigger-function-write-exception-to-log-table

Here's an example, using a normal function rather than a trigger, though it's really the same thing in as far as how to log:

Table to store errors:

CREATE TABLE errors (id SERIAL, sql_state TEXT, message TEXT, detail TEXT, hint TEXT, context TEXT);

Function which does work and has the exception handling/logging:

CREATE OR REPLACE FUNCTION my_func()

    RETURNS VOID AS

$BODY$

DECLARE

    _sql_state TEXT;

    _message TEXT;

    _detail TEXT;

    _hint TEXT;

    _context TEXT;

BEGIN

    PERFORM 1 / 0;

EXCEPTION

    WHEN OTHERS THEN

        GET STACKED DIAGNOSTICS

            _sql_state := RETURNED_SQLSTATE,

            _message := MESSAGE_TEXT,

            _detail := PG_EXCEPTION_DETAIL,

            _hint := PG_EXCEPTION_HINT,

            _context := PG_EXCEPTION_CONTEXT;

        INSERT INTO errors (sql_state, message, detail, hint, context)

        VALUES (_sql_state, _message, _detail, _hint, _context);

END

$BODY$

    LANGUAGE plpgsql;

After calling the function, the errors table contains:

enter image description here

See https://rextester.com/BQPG27732

Context shows a call stack of sorts. You could add more error-related fields of course, I only chose a handful of those available in GET STACKED DIAGNOSTICS