BUG #1864: Strange behavoir of batches

The following bug has been logged online:

Bug reference: 1864
Logged by: Angelo Neuschitzer
Email address: an(at)jenomics(dot)de
PostgreSQL version: 7.4.7
Operating system: Debian Linux (+ Win 2k)
Description: Strange behavoir of batches
Details:

Good greetings,

I had a strange behaiviour while I was working with the postgres, I have to
admit that I used it wrong in the first place but the 'trouble' was still
there, you don't have to fix this (cause it does not happen in 'common'
behaivour) but you should know.

some more information you might want to have:
Programming Language: Java (jdk 1.5.0_04)
postgres driver: pg74.216.jdbc3.jar
Postgres lives on Debian Linux, Server on Win 2k

I used Batches to write some Information into the db.
for I understood the ussage of addBatch false I called it everytime, but the
last one.

In my Program there were 3 blocks of inserting done in a row. 5 blocks of
insterting per call.

first block insterted 93 rows (in table A)

second instered 82 rows (in table B)
third 2 (in table C)
fourth 9 (in table D)
[whereas Tables B,C and D have a reference on Table A]

fith entered a reference to all 93 rows of (table A) in table (E).

in the fith block at row 82 the batch failed because It did not match the
foreign key constraint of table A TO table D

In the debugging process I changed the order of inserting and it worked
(means I inserted into A, C, D, B and then the 93 references in Table E).

this way it worked out, I got no trouble and everything was where it
belonged to.

(now I'm calling addBatch in every row and it works in every order)

If this was not understandable or you want to have some more information (or
some sample code) don't hestiate to mail me, but as I have lots of work It
may take a day or two till I anser.

Please notice that I may not give you the original code or database
structure cause my company does not develop this project open source.

(and please forgive my bad english)

Zo Phar

Angelo Neuschitzer

Angelo Neuschitzer wrote:
> The following bug has been logged online:
>
> Bug reference: 1864
> Logged by: Angelo Neuschitzer
> Email address: an(at)jenomics(dot)de
> PostgreSQL version: 7.4.7
> Operating system: Debian Linux (+ Win 2k)
> Description: Strange behavoir of batches
> Details:
>
> Good greetings,
>
> I had a strange behaiviour while I was working with the postgres, I have to
> admit that I used it wrong in the first place but the 'trouble' was still
> there, you don't have to fix this (cause it does not happen in 'common'
> behaivour) but you should know.
>
> some more information you might want to have:
> Programming Language: Java (jdk 1.5.0_04)
> postgres driver: pg74.216.jdbc3.jar
> Postgres lives on Debian Linux, Server on Win 2k
>
> I used Batches to write some Information into the db.
> for I understood the ussage of addBatch false I called it everytime, but the
> last one.

OK - this seems to be a Java thing, yes? stmt.addBatch()

> In my Program there were 3 blocks of inserting done in a row. 5 blocks of
> insterting per call.
>
> first block insterted 93 rows (in table A)
>
> second instered 82 rows (in table B)
> third 2 (in table C)
> fourth 9 (in table D)
> [whereas Tables B,C and D have a reference on Table A]
>
> fith entered a reference to all 93 rows of (table A) in table (E).
>
> in the fith block at row 82 the batch failed because It did not match the
> foreign key constraint of table A TO table D

And was this correct or not? Did row 82 reference a valid row in table A
or not?

> In the debugging process I changed the order of inserting and it worked
> (means I inserted into A, C, D, B and then the 93 references in Table E).
>
> this way it worked out, I got no trouble and everything was where it
> belonged to.
>
> (now I'm calling addBatch in every row and it works in every order)

Not sure what you mean by this.

My understanding is that you do something like:
stmt.addBatch('INSERT INTO ... VALUES (1,...)');
stmt.addBatch('INSERT INTO ... VALUES (2,...)');
stmt.addBatch('INSERT INTO ... VALUES (3,...)');
stmt.addBatch('INSERT INTO ... VALUES (4,...)');
resultCodes = stmt.executeBatch();
Which should execute four insert statements.

> If this was not understandable or you want to have some more information (or
> some sample code) don't hestiate to mail me, but as I have lots of work It
> may take a day or two till I anser.
>
> Please notice that I may not give you the original code or database
> structure cause my company does not develop this project open source.

If this is a bug, it sounds like it is in the JDBC driver. In which
case, you should probably talk to them (http://jdbc.postgresql.org/),
but they'll probably need a short example of how to reproduce it.

> (and please forgive my bad english)

Your English is fine sir.

--
Richard Huxton
Archonet Ltd

Angelo Neuschitzer wrote:
>>> In my Program there were 3 blocks of inserting done in a row. 5
>>> blocks of
>>> insterting per call.
>>>
>>> first block insterted 93 rows (in table A)
>>>
>>> second instered 82 rows (in table B)
>>> third 2 (in table C)
>>> fourth 9 (in table D)
>>> [whereas Tables B,C and D have a reference on Table A]
>>>
>>> fith entered a reference to all 93 rows of (table A) in table (E).
>>>
>>> in the fith block at row 82 the batch failed because It did not match
>>> the
>>> foreign key constraint of table A TO table D
>>
>>
>>
>> And was this correct or not? Did row 82 reference a valid row in table
>> A or not?
>
>
> Was corrrect. the row was not exsistent (afaik. Its very hard to
> relocate a row in Table A without a reference in the tables B,C or D)
> I also should mention that the first 93 INSERTs had no explainable
> order, but were always in the _same_ order.

If the foreign-key from table E couldn't find the corresponding row in
table A, then it's supposed to raise an error. So - if there is a
problem it must have happened when inserting the data into table A.

I'm not a Java expert I'm afraid, but there are a couple of things that
seem worth checking:
1. Make sure you check the result-codes when inserting these batches.
Put some inadmissible data in and check you get an error back.
2. Does the problem go away when you just use single statements within a
transaction rather than addBatch() - that would narrow down where the
problem is.

--
Richard Huxton
Archonet Ltd

Greetings. I was told to send this bug report to this mailinglist as
well, cause it may come from the jdbc driver.

the original Thread startet at:
http://archives.postgresql.org/pgsql-bugs/2005-09/msg00039.php

This mail includes the discussion as had been done till now. Some
phrases were cut out for better overview.

Zo Phar

Angelo Neuschitzer

> Greetings,
>
>>>
>>> I had a strange behaiviour while I was working with the postgres, I
>>> have to
>>> admit that I used it wrong in the first place but the 'trouble' was
>>> still
>>> there, you don't have to fix this (cause it does not happen in 'common'
>>> behaivour) but you should know.
>>>
>>> some more information you might want to have:
>>> Programming Language: Java (jdk 1.5.0_04)
>>> postgres driver: pg74.216.jdbc3.jar
>>> Postgres lives on Debian Linux, Server on Win 2k
>>>
>>> I used Batches to write some Information into the db.
>>> for I understood the ussage of addBatch false I called it everytime,
>>> but the
>>> last one.
>>
>>
>>
>> OK - this seems to be a Java thing, yes? stmt.addBatch()
>>
> Correct.
>
>>> In my Program there were 3 blocks of inserting done in a row. 5
>>> blocks of
>>> insterting per call.
>>>
>>> first block insterted 93 rows (in table A)
>>>
>>> second instered 82 rows (in table B)
>>> third 2 (in table C)
>>> fourth 9 (in table D)
>>> [whereas Tables B,C and D have a reference on Table A]
>>>
>>> fith entered a reference to all 93 rows of (table A) in table (E).
>>>
>>> in the fith block at row 82 the batch failed because It did not
>>> match the
>>> foreign key constraint of table A TO table D
>>
>>
>>
>> And was this correct or not? Did row 82 reference a valid row in
>> table A or not?
>
>
> Was corrrect. the row was not exsistent (afaik. Its very hard to
> relocate a row in Table A without a reference in the tables B,C or D)
> I also should mention that the first 93 INSERTs had no explainable
> order, but were always in the _same_ order.
>
>>
>>> In the debugging process I changed the order of inserting and it worked
>>> (means I inserted into A, C, D, B and then the 93 references in
>>> Table E).
>>>
>>> this way it worked out, I got no trouble and everything was where it
>>> belonged to.
>>>
>>> (now I'm calling addBatch in every row and it works in every order)
>>
>>
>>
>> Not sure what you mean by this.
>>
>> My understanding is that you do something like:
>> stmt.addBatch('INSERT INTO ... VALUES (1,...)');
>> stmt.addBatch('INSERT INTO ... VALUES (2,...)');
>> stmt.addBatch('INSERT INTO ... VALUES (3,...)');
>> stmt.addBatch('INSERT INTO ... VALUES (4,...)');
>> resultCodes = stmt.executeBatch();
>> Which should execute four insert statements.
>
>
> Unfortunatly it is far more difficult. I try to simplicice it a bit
> for beeter understanding:
> (Fortunatly we changed the structure till now. Now its technie
> readeable!)
> PreparedStatemet pStmt = con.prepareStatement("INSERT INTO table_a
> VALUES (...)");
> for(0 to 93)
> {
> [fill values into pStmt]
> if(not last row)
> {
> pStmt.addBatch();
> }
> }
> pStmt.executeBatch();
> pStmt.clearBatch();
>
> [seperate values into the parts for tables B,C,D]
>
> for(each value type) // sorting is B,C,D :: ANCHOR 1
> {
> pStmt = con.prepareStatement("INSERT INTO table_" + value type + "
> VALUES (...)");
> for(each value per type)
> {
> [fill values into pStmt]
> if(not last row)
> {
> pStmt.addBatch();
> }
> }
> pStmt.executeBatch();
> pStmt.clearBatch();
> }
>
> pStmt = con.prepateStatement("INSERT INTO ref_a_to_e VALUES (a_value,
> e_value)");
> for(0 to 93)
> {
> [fill values into pStmt]
> if(not last row)
> {
> pStmt.addBatch();
> }
> }
> pStmt.executeBatch();
> pStmt.clearBatch();
>
>>
>>> If this was not understandable or you want to have some more
>>> information (or
>>> some sample code) don't hestiate to mail me, but as I have lots of
>>> work It
>>> may take a day or two till I anser.
>>>
>>> Please notice that I may not give you the original code or database
>>> structure cause my company does not develop this project open source.
>>
>>
>>
>> If this is a bug, it sounds like it is in the JDBC driver. In which
>> case, you should probably talk to them (http://jdbc.postgresql.org/),
>> but they'll probably need a short example of how to reproduce it.
>
>
> I'll send it there as well, thank you.
>
> Zo Phar
>
> Angelo Neuschitzer
>

Angelo Neuschitzer wrote:

>> PreparedStatemet pStmt = con.prepareStatement("INSERT INTO table_a
>> VALUES (...)");
>> for(0 to 93)
>> {
>> [fill values into pStmt]
>> if(not last row)
>> {
>> pStmt.addBatch();
>> }
>> }
>> pStmt.executeBatch();
>> pStmt.clearBatch();

AFAIK, this "not last row" logic seems wrong. Calling executeBatch()
only executes those queries that have been added to the batch via
addBatch(), so the loop will lose the last INSERT.

-O

Angelo Neuschitzer wrote:

>> pStmt.executeBatch();
>> pStmt.clearBatch();

Also, executeBatch() clears the batch on success so the call to
clearBatch() is unnecessary.

-O

Yes, I know, I found the problem for me already.
If one does call addBatch() every time it works fine.
But thats not the point.
If I don't do it (there should be 1 line missing each block) but fiddle
around with the second block
{ // original
insert 82 rows (in table B)
2 (in table C)
9 (in table D)
{
{ // changed
2 (in table C)
9 (in table D)
82 (in table B)
{

it works well...

Also It should not matter at all what is in tables B,C and D if I enter
a reference from A to E... but it seems that it does.
If got some minutes free I'll go and get the old code again to reproduce
some things and write a report.

Zo Phar

Angelo Neuschitzer

Oliver Jowett wrote:

>Angelo Neuschitzer wrote:
>
>
>
>>>PreparedStatemet pStmt = con.prepareStatement("INSERT INTO table_a
>>>VALUES (...)");
>>>for(0 to 93)
>>>{
>>> [fill values into pStmt]
>>> if(not last row)
>>> {
>>> pStmt.addBatch();
>>> }
>>>}
>>>pStmt.executeBatch();
>>>pStmt.clearBatch();
>>>
>>>
>
>AFAIK, this "not last row" logic seems wrong. Calling executeBatch()
>only executes those queries that have been added to the batch via
>addBatch(), so the loop will lose the last INSERT.
>
>-O
>
>
>
>

--
Angelo Neuschitzer
Development

Java. Mit Sicherheit.
Jenomics GmbH, Thalkirchnerstraße 55, 80337 München, Deutschland

www.jenomics.de an(at)jenomics(dot)de
Tel: +49 (0) 89 767 59 003; Fax: +49 (0) 89 767 59 005

------------------------------------------------------

Diese Nachricht enthält vertrauliche Informationen und ist
ausschließlich für den Adressaten bestimmt. Der Gebrauch durch Dritte
ist verboten. Jenomics ist nicht verantwortlich für die ordnungsgemäße,
vollständige oder verzögerungsfreie Übertragung dieser Nachricht.

This message may contain confidential information and is intended solely
for the use by the addresse. Use of this communication by others is
prohibited. Jenomics are neither liable for the proper and complete
transmission of the information in this message nor for any delay in its
receipt.

Angelo Neuschitzer wrote:

> If got some minutes free I'll go and get the old code again to reproduce
> some things and write a report.

Please do, I don't really understand what is wrong here other than your
application code was missing an addBatch(). If you can give us a
selfcontained test case that shows the problem we might be able to help.

-O