Date: Fri, 6 Aug 2021 14:50:57 +0200 (CEST)
From: 066ce286@free.fr
To: Mukesh Rajpurohit <vivasvan1902@gmail.com>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Message-ID: 
 <59272590.782882796.1628254257380.JavaMail.root@zimbra82-e14.priv.proxad.net>
In-Reply-To: 
 <CAL+ptABeEX11HJYw_KhAYM-OPd4fVZziaz-aH8=5VsSB9UWb9A@mail.gmail.com>
Subject: Re: Oracle error log table equivalent in postgresql
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/59272590.782882796.1628254257380.JavaMail.root%40zimbra82-e14.priv.proxad.net>
Precedence: bulk


> 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.