Re: Jdbc3PoolingDataSource default auto commit is false

Actually, I thought of setting the autocommit flag per connection and oh
man is that bad. First it sent the "set autocommit = on" query, then it
did my query, then did a "set autocommit = off;" query, then I have no
idea why it did a "SELECT 1;" query.

I agree that the default should not be false, if you look at the
javadocs for "java.sql.Connection" it says "By default, new connections
are in auto-commit mode."

Here is a sample of the logs that I got when I was setting the
connections to autocommit, using Connection.setAutoCommit.||

2003-04-01 11:03:49 [8048] LOG: query: commit; set autocommit = on;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000592 sec
2003-04-01 11:03:49 [8048] LOG: query: -- MY FRIST QUERY --
2003-04-01 11:03:49 [8048] LOG: duration: 0.142125 sec
2003-04-01 11:03:49 [8048] LOG: query: set autocommit = off;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000421 sec
2003-04-01 11:03:49 [8048] LOG: query: select 1;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000190 sec
2003-04-01 11:03:49 [8048] LOG: query: commit; set autocommit = on;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000592 sec
2003-04-01 11:03:49 [8048] LOG: query: -- MY SECOND QUERY --
2003-04-01 11:03:49 [8048] LOG: duration: 0.001855 sec
2003-04-01 11:03:49 [8048] LOG: query: set autocommit = off;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000341 sec
2003-04-01 11:03:49 [8048] LOG: query: select 1;
2003-04-01 11:03:49 [8048] LOG: duration: 0.000132 sec

Csaba Nagy wrote:

>You still could have set the autocommit flag on a per connection basis.
>The default autocommit is the autocommit value a newly checked out
>connection will have, but you can set it to on after checking out the
>connection from the pool.
>And yes, when autocommit is off, you need to commit/rollback after your
>queries.
>BTW, I find the autocommit being off by default as rather useless and
>confusing, as people will know how to explicitly open a transaction when
>they want to have one wrapping more queries, and queries not explicitly
>wrapped in transactions are usually to be committed immediately. It is
>confusing, as your case clearly demonstrates.
>
>HTH,
>Csaba.
>
>On Wed, 2003-04-02 at 19:55, Ryan Christianson wrote:
>
>
>>Hi.
>>
>>I am useing Jdbc3PoolingDataSource to do database pooling. When I first
>>started using it some of my updates where not taking effect. So I
>>started enabled postgres logging and watched my queries, and I found out
>>why. The Jdbc3PoolingDataSource by default sets setDefaultAutoCommit to
>>false, and because of that, after every query it was calling "rollback".
>>
>>To fix it, I extended Jdbc3PoolingDataSource, over-wrote the
>>createConnectionPool like so:
>> protected ConnectionPool createConnectionPool() {
>> ConnectionPool pool = super.createConnectionPool();
>> pool.setDefaultAutoCommit(true);
>> return pool;
>> }
>>
>>So my question is, how should I have worked with defaultAutoCommit set
>>to true? Would I need to wrap all of my queries in begin; and commit; ?
>>
>>Thanks
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>