tpostgresqloutput after one reject all line are reject in the transact

One Star

tpostgresqloutput after one reject all line are reject in the transact

Hi
below a bug (related in 2010 by an other person) and i have the same problème (tos 4.2.2 or TOS 5.0 with Postgresql 9.0). It is very important to can update some record and have a reject fux. For this elementary task i hope talend fix the bug in 2012 or 2013 or 2014 ...
Can someone have the same problem or a solution? For me using batch with 1line don't work.
Thanks;
The bug :
Hi,
I want to insert rows in an PostgreSQL database having rejected row to be log in a special file.
I'm using a tPostgresqlOutput with "commit every" 10000, "Die on error" false.
When an error occurs on "insert", all the following rows are rejected with the following message "current transaction is aborted, commands ignored until end of transaction block"
While using tOracleOutput I don't get this kind of error.
I can counter this by setting "commit every" to 1, but is there any other way to save "commit" and go through first SQL error?
--My config :
TOS 4.0.2.r43696
PosgreSQL Database Server 8.2
One Star

Re: tpostgresqloutput after one reject all line are reject in the transact

Hi
Take it easy.
This issue is caused by PostgreSql transition.
When you set "commit every" 10000, the number of rows will be completed until committing batches of rows together into the DB.
In short, you define this 10000 rows "insert" as a transaction and all rows will be rolled back(rejected) together.
Setting "commit every" to 1 is the right resolution.
You said "For me using batch with 1line don't work."
Please show me more details about your job.
Screenshots are preferred.
Best regards!
Pedro
One Star

Re: tpostgresqloutput after one reject all line are reject in the transact

Hi,
thanks to answer.
For explain to you i make a specific job and i have a new surprise in TOS 5 : the link Reject for the tpostgresqloutput as disappeared
I don't understand how to put image in a post. Copy / paste don't work.
I have a in file with just one column and fives lines for example A,B,C,D,E
With a tmap i add a column number so the out for tmap is
2 A
2 B
2 C
...
I connect this output to a tpostgresqloutput (no end on error; using batch with value 1) : using a existing connection, no action on table and UPDATE on data. I just want to test if the different value are valid (A, B, C ...) because i use a DOMAIN on postgresql. The record with a key 2 is existing in the table. The reject in bad.csv list the bad values.
My error was the using connection was not 'automatic commit'. So in tos 4 i can solve my problem but in tos 5 i cannot have a reject??
One Star

Re: tpostgresqloutput after one reject all line are reject in the transact

Hi
You may upload images by 'Edit' your post.
Just show me your job screenshots.
Best regards!
Pedro
One Star

Re: tpostgresqloutput after one reject all line are reject in the transact

Setting "commit every" to 1 is the right resolution.

I don't agree. I have exactly the same problem and 'solved' committing at every line, but migration is taking twice the time, maybe more.
Isn't there a way to commit immediately after error?
Since I need the rejected rows, I can't use postgress' bulk component nor batch processing...Any better idea on how to solve this?
Thank you,
Alessandro
Seventeen Stars

Re: tpostgresqloutput after one reject all line are reject in the transact

Hi absig,
this is a special behavior of PostgreSQL database. Indeed if a statement within a transaction failed, the current transaction is unusable until now. This current connection can only be used after a commit or rollback.
Please try this:
Use a separate tPostgresqlConnection and set the option to auto commit.