From: FAROOQ SIDDIQUI <fas65@yahoo.com>
To: Gilles Darold <gilles@migops.com>
To: Mukesh Rajpurohit <vivasvan1902@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Cc: FAROOQ AHMED SIDDIQUI <fas65@yahoo.com>
Subject: Re: Oracle error log table equivalent in postgresql
Date: Fri, 6 Aug 2021 17:52:54 +0000 (UTC)
Message-ID: <191228527.376180.1628272374223@mail.yahoo.com> (raw)
In-Reply-To: <CAL+ptADmnMP=rA=L=PMyPFirTnO9P5XjkUcO7Xmg7cdZeJcabg@mail.gmail.com>
References: <CAL+ptAAXpddCv1HR90sest4RpD2AHhfoC-5y5eRyZJEr=U9O0g@mail.gmail.com>
<9e116fec-7756-5fd5-f537-a4fc771d7603@migops.com>
<CAL+ptADmnMP=rA=L=PMyPFirTnO9P5XjkUcO7Xmg7cdZeJcabg@mail.gmail.com>
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
Attachments:1628272351388blob.jpg (image/png, 7.3K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: fas65@yahoo.com, gilles@migops.com, vivasvan1902@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Oracle error log table equivalent in postgresql
In-Reply-To: <191228527.376180.1628272374223@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox