Re: BUG #1864: Strange behavoir of batches

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