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

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

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:

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...