On Wed, 20 Aug 2003, Jan Wieck wrote:
> Alvaro Herrera wrote:
>
> > On Wed, Aug 20, 2003 at 10:31:25AM -0400, Vivek Khera wrote:
> >
> >> Seriously, how much slower can it be if the backend were to do the
> >> checking for external references upon updating/deleting a row? The
> >> cost would be distributed across time as opposed to concentrated at
> >> once within a vacuum process. I am fairly certian it would reduce
> >> disk bandwidth requirements since at least one necessary page will
> >> already be in memory.
> >
> > There's no way to check for "external references", because said
> > references are actually the running transactions. So you can't drop a
> > row until all the transactions that were active during your transaction
> > are finished. Certainly your own backend can't do the checking, because
> > there's no way to even assert that it will be live when those other
> > transactions finish. Who will? The last of said transactions? It
> > certainly will be expensive for a backend to keep track of the
> > deleted/updated tuples by all _other_ backends, just in case...
> > Long running transactions can't be neglected, so you can't keep it in
> > memory.
>
> This isn't so. It's regular backends that turn on the flag in a tuples
> header telling that it's committed deleted. And they do so during a
> normal scan, so even during a SELECT. It's not the backend who actually
> deleted a row that knows, it's a later one checking the tuple visibility
> that can tell "this one's dead for all of us and will not come alive via
> rollback any more".
True, but the message being responded to was specifically "if the backend
were to do the checking for external references upon updating/deleting a
row". In any case, I thought it only does the committed deleted stuff
when it comes upon a row in a scan, which means that it's still not
automatic clean up in general since any particular deleted row may not get
looked at for some amount of time after all possible viewers are gone.
