Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem: commit doesn´t work

Problem: commit doesn´t work

From
"Marcos de Barros"
Date:
Hi Group,
 
I´m a new membrer and have a problem with the commit in jdbc postgres. I would appreciate some help.
 
I have a program that execute 40000 inserts in a table. I set autocommit = false before the process and begin the loop. The 39999 insert, for example, has an error and after the loop i execute the commit command. The SQL Server or Oracle driver commit all the 39998 right inserts. The postgres doesn´t commit one. I figure it´s a bug, or i´m doing something wrong. Above is the code:
 
for (int i=0; i < queryList.length; i++) {
    if (queryList[i] != null) {
        try {
            statement.execute(queryList[i]);
        }
        catch(Exception e) {
            System.out.println("queryList[" + i + "]" + queryList[i]);
        }
    }
}
connection.commit();
 
 
Thank you a lot!
 
Best Regards,
 
Marcos de Barros
Iclass Consultoria
mbarros@iclass.com.br
http://www.iclass.com.br
Tels: (21) 2240-8747 / (21) 2220-1480

Re: Problem: commit doesn´t work

From
Daniel Serodio
Date:
While I also dislike this behaviour, it's the backend's fault, not the
driver's. Try the same thing in psql (or any other frontend) and you'll
get the same results.

If you do try to repost this question on another list (pgsql-hackers
etc) I'd be interested in following the discussion, if you could CC: me
I'd be grateful.

[]'s
Daniel Serodio

On Tue, 2003-01-07 at 18:41, Marcos de Barros wrote:
> Hi Group,
>
> I´m a new membrer and have a problem with the commit in jdbc postgres.
> I would appreciate some help.
>
> I have a program that execute 40000 inserts in a table. I set
> autocommit = false before the process and begin the loop. The 39999
> insert, for example, has an error and after the loop i execute the
> commit command. The SQL Server or Oracle driver commit all the 39998
> right inserts. The postgres doesn´t commit one. I figure it´s a bug,
> or i´m doing something wrong. Above is the code:
>
> for (int i=0; i < queryList.length; i++) {
>     if (queryList[i] != null) {
>         try {
>             statement.execute(queryList[i]);
>         }
>         catch(Exception e) {
>             System.out.println("queryList[" + i + "]" + queryList[i]);
>         }
>     }
> }
> connection.commit();
>
>
> Thank you a lot!
>
> Best Regards,
>
> Marcos de Barros
> Iclass Consultoria
> mbarros@iclass.com.br
> http://www.iclass.com.br
> Tels: (21) 2240-8747 / (21) 2220-1480
--
Daniel Serodio <daniel@checkforte.com.br>
CheckForte

Re: Problem: commit doesn´t work

From
João Paulo Ribeiro
Date:
Hi, Marcos.

As far as i know the default behaviour if something  goes wrong in the
backend executing a statement during a transaction ( i assume that's the
case you are talking about) is the backend automatically to do a
rollback, terminate your transaction and report the error. As so when
you do the commit there is nothing to commit. This the default behaviour
expected and i cant tell exactly but i had used Oracle and this is the
default behaviour.
In a transaction every statement have to be successfull. If not, the
backend do automatically a rollback
The transaction is see like an unit and have to be done entirely or not
done at all.
There is some "ways" that people had invented to try to get somethink
like having a transaction but with something  allowing them to control
how much they want to rollback.
Oracle use what they call "Save points". Something like transaction into
transactions. This allow that you to tell do backend to only rollback to
last save point if something goes wrong.
But again, this not the default behaviour because this is  not the
behaviour expected for a transaction.

I think this is not a bug but the normal behaviour. :)
May be  i am wrong.

I hope this helped.


Marcos de Barros wrote:

> Hi Group,
>
> I´m a new membrer and have a problem with the commit in jdbc postgres.
> I would appreciate some help.
>
> I have a program that execute 40000 inserts in a table. I set
> autocommit = false before the process and begin the loop. The 39999
> insert, for example, has an error and after the loop i execute the
> commit command. The SQL Server or Oracle driver commit all the 39998
> right inserts. The postgres doesn´t commit one. I figure it´s a bug,
> or i´m doing something wrong. Above is the code:
>
> for (int i=0; i < queryList.length; i++) {
>     if (queryList[i] != null) {
>         try {
>             statement.execute(queryList[i]);
>         }
>         catch(Exception e) {
>             System.out.println("queryList[" + i + "]" + queryList[i]);
>         }
>     }
> }
> connection.commit();
>
>
> Thank you a lot!
>
> Best Regards,
>
> Marcos de Barros
> Iclass Consultoria
> mbarros@iclass.com.br <mailto:mbarros@iclass.com.br>
> http://www.iclass.com.br
> Tels: (21) 2240-8747 / (21) 2220-1480

Re: [JDBC] Problem: commit doesn´t work

From
"Michael Paesold"
Date:
[Topic: transaction aborted after error, no commit possible]

"Daniel Serodio" <daniel@checkforte.com.br> wrote:
> While I also dislike this behaviour, it's the backend's fault, not the
> driver's. Try the same thing in psql (or any other frontend) and you'll
> get the same results.
>
> If you do try to repost this question on another list (pgsql-hackers
> etc) I'd be interested in following the discussion, if you could CC: me
> I'd be grateful.


There is no sense in re-posting to hackers. This has been discussed before
and is well known behaviour. Postgres' transactions are atomic, either the
whole transaction will commit or nothing at all. Look at the archives to see
discussion about exactly the same topic.

AFAIK, Bruce is working on nested transactions for 7.4 or later. That will
open the possibility for the behaviour Marcos expected.

Best regards,
Michael Paesold