Strange behavoir of batches - Mailing list pgsql-jdbc

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
>