Thread: Autocommit off in psql??
Hi! Is there a way of turning autocommit of in psql ? It would be nice for people used to Oracle's SQL*Plus. No fatal error has occurred yet, but some minor problems could have been avoided if rollback was possible when the fingers on the keyboard are faster than the brain :) Björn
On Mon, May 21, 2001 at 09:38:11PM +0200, Bj?rn Lundin wrote: > Hi! > Is there a way of turning autocommit of in psql ? > It would be nice for people used to Oracle's SQL*Plus. > No fatal error has occurred yet, but some minor problems could have been > avoided if rollback was possible when the fingers on the keyboard are > faster than the brain :) Yes, just start an explicit transaction with "BEGIN" -- this disables autocommit (so when you want to commit your transaction, you'll need to do "COMMIT" by hand). Cheers, Neil
Actually, I think the original question alluded to SQL*PLUS behavior that allows one to disable autocommit for an ENTIRE session, not simply a discrete transaction. In psql, in order to enforce autocommit=off for an entire session, you must repeatedly issue a series of BEGIN directives, one following each ROLLBACK, COMMIT, or ABORT, yes? tjm -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Neil Conway Sent: Tuesday, May 22, 2001 3:49 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Autocommit off in psql?? On Mon, May 21, 2001 at 09:38:11PM +0200, Bj?rn Lundin wrote: > Hi! > Is there a way of turning autocommit of in psql ? > It would be nice for people used to Oracle's SQL*Plus. > No fatal error has occurred yet, but some minor problems could have been > avoided if rollback was possible when the fingers on the keyboard are > faster than the brain :) Yes, just start an explicit transaction with "BEGIN" -- this disables autocommit (so when you want to commit your transaction, you'll need to do "COMMIT" by hand). Cheers, Neil ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Quoting Neil Conway (nconway@klamath.dyndns.org): > Yes, just start an explicit transaction with "BEGIN" -- this disables > autocommit (so when you want to commit your transaction, you'll need > to do "COMMIT" by hand). That brings to mind a couple of questions I had: Can I just open a transaction, delete 100,000 rows in three tables, add back 100,000 rows in three tables, and then end the transaction? Even if it takes two hours and 5,000 lines of Perl code to do it? If so, how much disk space am I going to need? Would double the current used amount do it? And should I vacuum immediately afterwards? -- Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody The WWW is exciting because Microsoft doesn't own it, and therefore, there's a tremendous amount of innovation happening. -- Steve Jobs
On Tue, May 22, 2001 at 08:18:31PM -0400, Paul Tomblin wrote: > Quoting Neil Conway (nconway@klamath.dyndns.org): > > Yes, just start an explicit transaction with "BEGIN" -- this disables > > autocommit (so when you want to commit your transaction, you'll need > > to do "COMMIT" by hand). > > That brings to mind a couple of questions I had: > > Can I just open a transaction, delete 100,000 rows in three tables, add > back 100,000 rows in three tables, and then end the transaction? Even if > it takes two hours and 5,000 lines of Perl code to do it? If so, how much > disk space am I going to need? Would double the current used amount do > it? And should I vacuum immediately afterwards? Yes, absolutly. We do that here quite a bit so that if there a bug in the program and it falls over, the database rolls back and we can fix it and just rerun the program without worrying about the data. In fact, sometimes for testing I comment out the last commit until the program runs to completion with the proper debug output. The amount of disk space is exactly the same as without a transaction. Probably however much space you think 200,000 rows will take up. A vacuum is recommened anytime you play with that much data. The only issue is that transactions can't be nested but that shouldn't be a problem. Also, it's possible that the transaction may block other queries on the database but someone with more knowledge than me will have to answer that. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
I'd like to seccond a request for that kind of feature if it isn't already available. If it is available, how do I set it up ? ""Tim Mickol"" <tmickol@combimatrix.com> wrote in message news:NCEEJEAEIDNFKBMALPGMOEIMCCAA.tmickol@combimatrix.com... > Actually, I think the original question alluded to SQL*PLUS behavior that > allows one to disable autocommit for an ENTIRE session, > not simply a discrete transaction. In psql, in order to enforce > autocommit=off for an entire session, you must repeatedly issue a series of > BEGIN directives, one following each ROLLBACK, COMMIT, or ABORT, yes? >
Yes, I meant turn off Autocommit for the entire psql session. Starting every manipulation of the database with a BEGIN is a bit tiresome, and i often catch myself with forgetting it. If it isn't possible, it would be a nice featue!? Björn Tim Mickol wrote: > Actually, I think the original question alluded to SQL*PLUS behavior that > allows one to disable autocommit for an ENTIRE session, > not simply a discrete transaction. In psql, in order to enforce > autocommit=off for an entire session, you must repeatedly issue a series > of BEGIN directives, one following each ROLLBACK, COMMIT, or ABORT, yes? > > > Yes, just start an explicit transaction with "BEGIN" -- this disables > autocommit (so when you want to commit your transaction, you'll need > to do "COMMIT" by hand). > > Cheers, > > Neil > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Can anyone tell me how to do this ? "Bj�rn Lundin" <bjorn.lundin@swipnet.se> wrote in message news:9eh2vh$h10$1@news.tht.net... > Yes, I meant turn off Autocommit for the entire psql session. > Starting every manipulation of the database with a BEGIN is > a bit tiresome, and i often catch myself with forgetting it. > If it isn't possible, it would be a nice featue!? > Bj�rn >