Thread: Autocommit

Autocommit

From
Hasnul Fadhly bin Hasan
Date:
Hi,

I am just wondering, by default, autocommit is enabled for every client
connection.  The documentations states that we have to use BEGIN
and  END or COMMIT so to increase performance by not using autocommit.
My question is, when we use the BEGIN and END statements, is autocommit
unset/disabled automatically or we have to disable/unset it manually?


Hasnul



Re: Autocommit

From
Michael Fuhr
Date:
On Mon, Feb 14, 2005 at 04:01:20PM +0800, Hasnul Fadhly bin Hasan wrote:
>
> I am just wondering, by default, autocommit is enabled for every client
> connection.  The documentations states that we have to use BEGIN
> and  END or COMMIT so to increase performance by not using autocommit.
> My question is, when we use the BEGIN and END statements, is autocommit
> unset/disabled automatically or we have to disable/unset it manually?

What version of PostgreSQL is your server running and what client
software are you using?  PostgreSQL 7.3 had a server-side autocommit
setting, but it caused problems with some clients so 7.4 got rid
of it and left autocommit up to the client.  How to enable or disable
client-side autocommit depends on the client software, but if you're
able to execute a BEGIN (or START TRANSACTION) statement then you
should be inside a transaction until you execute COMMIT (or END)
or ROLLBACK.  That is, unless your client intercepts these statements
and does whatever it wants....

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Autocommit

From
Hasnul Fadhly bin Hasan
Date:
Hi Micheal,

Thanks for the reply.  I am using postgres 7.4.5 client.  There's one that is using 7.4.1 client.  I'm not sure if there would be any difference.
When i use psql and check the status of autocommit, it is set to enable.  I'm not sure if libpq and psql uses the same defaults.

Thanks,

Hasnul



Michael Fuhr wrote:
On Mon, Feb 14, 2005 at 04:01:20PM +0800, Hasnul Fadhly bin Hasan wrote: 
I am just wondering, by default, autocommit is enabled for every client 
connection.  The documentations states that we have to use BEGIN
and  END or COMMIT so to increase performance by not using autocommit. 
My question is, when we use the BEGIN and END statements, is autocommit 
unset/disabled automatically or we have to disable/unset it manually?   
What version of PostgreSQL is your server running and what client
software are you using?  PostgreSQL 7.3 had a server-side autocommit
setting, but it caused problems with some clients so 7.4 got rid
of it and left autocommit up to the client.  How to enable or disable
client-side autocommit depends on the client software, but if you're
able to execute a BEGIN (or START TRANSACTION) statement then you
should be inside a transaction until you execute COMMIT (or END)
or ROLLBACK.  That is, unless your client intercepts these statements
and does whatever it wants....
 

Re: Autocommit

From
Michael Fuhr
Date:
On Mon, Feb 14, 2005 at 04:58:31PM +0800, Hasnul Fadhly bin Hasan wrote:

> Thanks for the reply.  I am using postgres 7.4.5 client.  There's one
> that is using 7.4.1 client.  I'm not sure if there would be any difference.
> When i use psql and check the status of autocommit, it is set to
> enable.  I'm not sure if libpq and psql uses the same defaults.

As far as I can tell, libpq doesn't have an autocommit setting --
it just sends statements on behalf of the application.  Clients
that allow the user to disable autocommit presumably do so by
implicitly sending BEGIN statements to start new transactions.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/