INSERT ALL with DML ERROR Logging replacement in PostgreSQL

Le 21/06/2022 à 09:08, Jagmohan Kaintura a écrit :

Hi Team,
We are working on a project where we are moving from Oracle to
PostgreSQL and working on a migration tool which mostly have
statements for inserting the records which are correct and logging the
errors in error table using ORACLE inbuilt statement for INSERT ALL
with DML ERROR logging.

As part of the postgresql best practices, what approach are we taking
to move these types of statements in Postgresql as we don't have any
such equivalent mechanism to load correct data in the main table and
error record in error table with error reason.

The statements mostly used are -->
INSERT ALL INTO
target_table
(COLUMN LIST)
VALUES()
LOG ERROR INTO ...
SELECT statement considering the source tables;

)
Can anyone please help me with what could be the best approach to
convert this in the tool.

--
*Best Regards,*
Jagmohan

Hi,

Maybe what you are looking for is here
https://github.com/MigOpsRepos/pg_dbms_errlog , this is a PostgreSQL
extension that emulates the DBMS_ERRLOG Oracle package.

Best regards,

--
Gilles Darold
http://www.darold.net/

Hi Gilles,

I was going though this earlier today but didn't compiled it as I read it
may not be able to capture the errors if we have below type of statement
and most of our statements are of INSERT .. SELECT statements only.

The form INSERT INTO <tablename> SELECT ... will not have the same behavior
than in Oracle. It will not stored the successful insert and logged the
rows in error. This is not supported because it is a single transaction for
PostgreSQL and everything is rolled back in case of error.

Our all statements are of that form will it be still useful.

On Tue, Jun 21, 2022 at 1:07 PM Gilles Darold <gilles@darold.net> wrote:

Le 21/06/2022 à 09:08, Jagmohan Kaintura a écrit :

Hi Team,
We are working on a project where we are moving from Oracle to PostgreSQL
and working on a migration tool which mostly have statements for inserting
the records which are correct and logging the errors in error table using
ORACLE inbuilt statement for INSERT ALL with DML ERROR logging.

As part of the postgresql best practices, what approach are we taking to
move these types of statements in Postgresql as we don't have any such
equivalent mechanism to load correct data in the main table and error
record in error table with error reason.

The statements mostly used are -->
INSERT ALL INTO
target_table
(COLUMN LIST)
VALUES()
LOG ERROR INTO ...
SELECT statement considering the source tables;

)
Can anyone please help me with what could be the best approach to convert
this in the tool.

--
*Best Regards,*
Jagmohan

Hi,

Maybe what you are looking for is here
https://github.com/MigOpsRepos/pg_dbms_errlog , this is a PostgreSQL
extension that emulates the DBMS_ERRLOG Oracle package.

Best regards,

--
Gilles Daroldhttp://www.darold.net/

--
*Best Regards,*
Jagmohan

Le 21/06/2022 à 10:28, Jagmohan Kaintura a écrit :

Hi Gilles,

I was going though this earlier today but didn't compiled it as I read
it may not be able to capture the errors if we have below type of
statement and most of our statements are of INSERT ..
SELECT statements only.

The form |INSERT INTO <tablename> SELECT ...| will not have the same
behavior than in Oracle. It will not stored the successful insert and
logged the rows in error. This is not supported because it is a single
transaction for PostgreSQL and everything is rolled back in case of error.

Our all statements are of that form will it be still useful.

Right, this was not obvious in your post, but yes if you are using
INSERT + SELECT this is not possible with the current version of this
extension. Maybe that could be possible by rewriting internally the
query to loop over the result of the select and generate an insert per
row returned, but with performances lost of courses.

Best regards,

--
Gilles Darold
http://www.darold.net/

Hi ALl,

Any other thought on this thread.

On Tue, Jun 21, 2022 at 2:24 PM Gilles Darold <gilles@darold.net> wrote:

Le 21/06/2022 à 10:28, Jagmohan Kaintura a écrit :

Hi Gilles,

I was going though this earlier today but didn't compiled it as I read it
may not be able to capture the errors if we have below type of statement
and most of our statements are of INSERT .. SELECT statements only.

The form INSERT INTO <tablename> SELECT ... will not have the same
behavior than in Oracle. It will not stored the successful insert and
logged the rows in error. This is not supported because it is a single
transaction for PostgreSQL and everything is rolled back in case of error.

Our all statements are of that form will it be still useful.

Right, this was not obvious in your post, but yes if you are using INSERT
+ SELECT this is not possible with the current version of this extension.
Maybe that could be possible by rewriting internally the query to loop over
the result of the select and generate an insert per row returned, but with
performances lost of courses.

Best regards,

--
Gilles Daroldhttp://www.darold.net/

--
*Best Regards,*
Jagmohan