Kettle 4.0 error management

[gjamila]

Hi everybody,
I'm trying to do error handling with PDI 4.0.
I have a postgres database.
If it finds violations of key rightly puts the discarded records in the table of errors, but then discards all subsequent records, even if correct.
The error (for correct records) is:

org.pentaho.di.core.exception.KettleDatabaseException:
Error inserting/updating row
ERROR: current transaction is aborted, commands ignored until end of transaction block

Can you help me?

Thank you very much!
gjamila

[MattCasters]

Sorry, we tried a few times to find a solution for this very specific PostgreSQL problem (all other databases are fine) but the best you can do is take a serious performance hit.

That being said, feel free to explain to us what you are doing exactly, which steps you are using, and so on.

[gjamila]

Thanks a lot for reply.
I'm trying to do error management, i'm using two output tables.
The first is the classical output table, the second one is where discarded records are placed.
There are several reasons why the records could be discarded, such as violations of keys.
I don't want to stop loading, so I want to handle errors in this way (there are other ways?). You can see the attachment.

Cattura.jpg

With Pentaho Kettle 3.2 it works, I have no problems.
With Pentaho 4.0, when a record is unwrapped correctly puts it in the table of errors. After that any other record is discarted by Pentaho 4.0, even if it's a "correct" record (without violation of keys...).

Log loading for corrects record is:

org.pentaho.di.core.exception.KettleDatabaseException:
Error inserting/updating row
ERROR: current transaction is aborted, commands ignored until end of transaction block

How can I resolve this problem?
Thank you very much!

[desmodemone]

Hi
I have got the same problem. I'm using PostgreSQL 8.4.5 as repository database and as data wharehouse (two distinct database in the same db cluster). In PDI 3.2 I never got the problem, but in PDI 4.0 and 4.0.1 RC I got this problem if I enable error handling. I try to analyze the problem and it seems to depend on the lack of a "rollback" command when the error succeded!

To explain better, if you done a dml and it failed when you are in transaction mode (autocommit off) , you have to rollback the wrong operation,
if you not do , it's like this :

test=# \d tab2
Table "public.tab2"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"tab2_pkey" PRIMARY KEY, btree (a)


wrong mode :
test=# truncate table tab2 ;
TRUNCATE TABLE
test=# begin transaction ;
BEGIN
test=# insert into tab2 values (2);
INSERT 0 1
test=# insert into tab2 values (4);
INSERT 0 1
test=# insert into tab2 values (2);
ERROR: duplicate key value violates unique constraint "tab2_pkey"
test=# insert into tab2 values (5);
ERROR: current transaction is aborted, commands ignored until end of transaction block

right mode :
test=# truncate table tab2 ;
TRUNCATE TABLE
test=# begin transaction ;
BEGIN
test=# insert into tab2 values (2);
INSERT 0 1
test=# insert into tab2 values (4);
INSERT 0 1
test=# insert into tab2 values (2);
ERROR: duplicate key value violates unique constraint "tab2_pkey"
test=# rollback ;
ROLLBACK
test=# insert into tab2 values (5);
INSERT 0 1


So I imagine that somewhere in the code of PDI , if an insert on "table output" step failed, it's not done a rollback command.
I not know enough the code to analyze it, but I think it is in one of these three files under "src/org/pentaho/di/trans/steps/tableoutput" :

TableOutputData.java
TableOutput.java
TableOutputMeta.java

Could someone tell me if these are the correct files ?

Kind Regards

Mat