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 <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
