Thread: Autocommit
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
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/
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:
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....
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/