From: 066ce286@free.fr
To: Mukesh Rajpurohit <vivasvan1902@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Oracle error log table equivalent in postgresql
Date: Fri, 6 Aug 2021 14:50:57 +0200 (CEST)
Message-ID: <59272590.782882796.1628254257380.JavaMail.root@zimbra82-e14.priv.proxad.net> (raw)
In-Reply-To: <CAL+ptABeEX11HJYw_KhAYM-OPd4fVZziaz-aH8=5VsSB9UWb9A@mail.gmail.com>
> 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.
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: 066ce286@free.fr, vivasvan1902@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Oracle error log table equivalent in postgresql
In-Reply-To: <59272590.782882796.1628254257380.JavaMail.root@zimbra82-e14.priv.proxad.net>
* 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