Re: Suggestion; "WITH VACUUM" option

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