PostgreSQL JDBC - Strange behavoir of batches

Author

Strange behavoir of batches

Angelo Neuschitzer

2005-09-12, 11:24 am

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/pgsq...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,
>
> Correct.
>
>
>
> 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.
>
>
>
> 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();
>
>
>
> I'll send it there as well, thank you.
>
> Zo Phar
>
> Angelo Neuschitzer
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett

2005-09-12, 8:24 pm

Angelo Neuschitzer wrote:
[color=darkred]

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2005-09-12, 8:24 pm

Angelo Neuschitzer wrote:
[color=darkred]

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

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Oliver Jowett

2005-09-13, 7:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Angelo Neuschitzer

2005-09-13, 11:24 am

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:
>
>
>
>
>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@jenomics.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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links