Re: Suggestion; "WITH VACUUM" option

On Mon, 16 Dec 2002, Marc G. Fournier wrote:

> On Mon, 16 Dec 2002, scott.marlowe wrote:
>
> > On Mon, 16 Dec 2002, Tom Lane wrote:
> >
> > > Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > > > How hard would it be to add a "WITH (VACUUM)" option to UPDATE and DELETE
> > > > queries? This option would cause the regular vacuum activity -- purging the
> > > > dead tuple and its index references -- to be done immediately, as part of the
> > > > statement, instead of being deferred.
> > >
> > > > Easy? Hard? Insane? What do you think?
> > >
> > > Impossible. You can't vacuum a tuple until the last open transaction
> > > that can see it is gone. It is therefore *impossible* for a transaction
> > > to vacuum away its own detritus; until the transaction commits, you
> > > can't even start to wonder whether other open transactions see it or
> > > not.
> > >
> > > Vacuuming has to be done later, and that being the case, I don't see any
> > > real advantage to altering the "background vacuum" design we have.
> >
> > Then, would a "commit with vacuum" work? OR a "begin transaction with
> > vacuum" Just tossing them out there...
>
> Tom will correct me here, but I believe what he was trying to get across
> isn't that the 'current transaction' is the problem ... the problem is the
> other connections who have open transactions ... my simplistic
> understanding (and I hope it isn't too flawed) of MVCC is that as long as
> *one* transaction is outstanding on a tuple, that tuple can't be
> physically removed ... as far as any new transactions are concerned, it
> has disappeared ... so if I open a transaction, then you open one 'with
> vacuum', your 'with vacuum' will fail unless I happen to be out of my
> transaction before you ...

Oh, yeah I have no doubt of that. I was thinking more along the lines of
when a transaction ends it throws a background "vacuum table1;vacuum
table2;vacuum tablen" command into some kind of vacuuming hopper. I.e. it
doesn't block waiting, it runs it as though it were run AFTER the
transaction. If there are a few tuples from other transactions we can't
reclaim, no big deal.

The other option would be some kind of GUC that set a max number of rows
deleted/updated in a table in a transaction that would trigger this kind
of thing automagically.

But I could see such a setting causing just as much harm (chaos theory
anyone? :-) as good.