Re: VACUUM and open transactions

> Alvaro Herrera wrote:
> >Joseph S wrote:
> >>I realize this thread is old, but I just conducted an experiment with pg
> >>8.0.10 and a transaction with a SERIALIZABLE isolation level does
> >>prevent VACUUM from reclaiming rows that were created and then obsoleted
> >> in a subsequent transaction.
> >
> >Right. This is expected. VACUUM cannot remove them because the
> >serializable transaction might still want to see those rows. (I am
> >assuming the serializable transaction is still running when the vacuum
> >starts. If that's not the case, please explain better).

Joseph S wrote:
> The serializable transaction *can't* see those rows, they were created
> and obsoleted after the start of the transaction. The point of make the
> transaction serializable in the first place was to allow VACUUM to
> reclaim those rows.

Well, if you're thinking that vacuum will reclaim those rows just
because the transaction is serializable and thus the rows are invisible,
you're mistaken. If that's the only reason to set the transaction
serializable, you'll be better off changing it to read committed because
you're not getting that benefit.

It's possible that there's some optimization to be made here, but right
now it doesn't exist. (And to be frank, I haven't thought about the
issue to be certain that the optimization is possible at all -- maybe
there's some reason why it's not, for example ctid chains or whatever).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.