Re: understanding the interaction with delete/select/vacuum

Alan Stange <stange@rentec.com> writes:
> I have a long running process which does a 'SELECT ID FROM T'.  The
> results are being streamed to the client using a fetch size limit.  This
> process with take 26 hours to run.    It turns out that all the "C" and
> "P" are going to be deleted when the SELECT gets to them.

> Several hours into this process, after the "C" rows have been deleted in
> a separate transaction but we haven't yet gotten to the "P" rows, a
> vacuum is begun on table T.

> What happens?

VACUUM can't remove any rows that are still potentially visible to any
open transaction ... so those rows will stay.  It's best to avoid having
single transactions that take 26 hours to run --- there are a lot of
other inefficiencies that will show up in such a situation.  Can you
break the long-running process into shorter transactions?

            regards, tom lane