Thread: 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.
Thank you in advance,
Mukesh
> 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; ??
On Fri, Aug 6, 2021, 5:53 PM <066ce286@free.fr> wrote:
> 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.
If I understand correctly, you want to log exceptions to a table, or assume it is logged to some system table.
I am not sure if there is inbuilt setup that logs the exceptions or any messages to some table.
Everything gets logged to logfile based on how logging is configured.
There are ways where you can enable csv logging to file and use file_fdw to make it queryable via sql query.
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.
On Fri, Aug 6, 2021, 5:52 PM <066ce286@free.fr> wrote:
> 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;
??
On Fri, Aug 6, 2021, 6:01 PM Mukesh Rajpurohit <vivasvan1902@gmail.com> wrote:
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.
Logging verbosity is configurable.
It can log a lot of detail of a particular query,
If you look at log line prefix, it has everything you would want imho wrt logging the exception.
But yes, I have not seen this stuff getting logged to any table.
Ofcourse, you can manage your custom table to log messages as partitions and drop periodically etc, but I think I do not know more on this if there has been a better way to do it via inbuilt solutions.
On Fri, Aug 6, 2021, 6:07 PM Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
On Fri, Aug 6, 2021, 6:01 PM Mukesh Rajpurohit <vivasvan1902@gmail.com> wrote: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.
OK, maybe what you are referring to is something like a dead letter queue.
Nope, I think I am not aware how oracle does it, so if someone has done oracle to postgresql migration, hopefully they would know more.
> 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 youcan 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 anothertransaction, 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.
Thanks. I got how to capture errmsg, errcode etc into error log table however my aim is to insert rejected rows too in error log table with all its column data. If you know error log table feature in oracle then you could relate what I am trying to achieve here.
On Fri, Aug 6, 2021, 6:20 PM <066ce286@free.fr> wrote:
> 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.
On Fri, 6 Aug 2021 at 18:27, Mukesh Rajpurohit <vivasvan1902@gmail.com> wrote:
Thanks. I got how to capture errmsg, errcode etc into error log table however my aim is to insert rejected rows too in error log table with all its column data. If you know error log table feature in oracle then you could relate what I am trying to achieve here.
PS: this may not be helpful, but just in case you want to implement something custom.
reading this,
I have not seen anyone implement something like that here, maybe the experts would have more detail.
but, i think this would require a custom function,
something like this, a small try catch, setup where you can log all the relevant fields required for your error log.
i have used jsonb for now.
Hi, Maybe this implementation [1] of Oracle DBMS_ERRLOG for PostgreSQL could correspond to what you are looking for. [1] https://github.com/MigOpsRepos/pg_dbms_errlog Best regards, -- Gilles Darold MigOps Inc http://www.migops.com/ Le 06/08/2021 à 13:35, Mukesh Rajpurohit a écrit : > 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. > > Thank you in advance, > Mukesh -- Gilles Darold
Thanks Vijay/ Gilles for your help.
I would check how I can include it for my requirement.
Thanks,
Mukesh
On Fri, Aug 6, 2021, 8:02 PM Gilles Darold <gilles@migops.com> wrote:
Hi,
Maybe this implementation [1] of Oracle DBMS_ERRLOG for PostgreSQL could
correspond to what you are looking for.
[1] https://github.com/MigOpsRepos/pg_dbms_errlog
Best regards,
--
Gilles Darold
MigOps Inc
http://www.migops.com/
Le 06/08/2021 à 13:35, Mukesh Rajpurohit a écrit :
> 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.
>
> Thank you in advance,
> Mukesh
--
Gilles Darold
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;BEGINPERFORM 1 / 0;EXCEPTIONWHEN OTHERS THENGET 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 hereSee https://rextester.com/BQPG27732Context 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
Attachment
Thanks Pepe/ Farooq.
Thanks,
Mukesh
On Fri, Aug 6, 2021, 11:22 PM FAROOQ SIDDIQUI <fas65@yahoo.com> wrote:
Please find below code, looks like, relate to your issue:Refrence: https://stackoverflow.com/questions/53504234/pgsql-trigger-function-write-exception-to-log-tableHere'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;BEGINPERFORM 1 / 0;EXCEPTIONWHEN OTHERS THENGET 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 hereContext 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