Re: Autocommit off in psql??

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/