Re: VACUUM and open transactions

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> Right. This is expected. VACUUM cannot remove them because the
>>> serializable transaction might still want to see those rows.

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

VACUUM doesn't even know that the transaction is serializable, much less
specific details of which other transactions it can or can't see the
effects of. The rule is that anything newer than the "xmin" advertised
by the transaction might be visible.

This is, of course, an engineering tradeoff: we could cause transactions
to advertise more-complete details of the snapshots they're using, and
then try to teach VACUUM to take advantage of that knowledge. But the
distributed overhead of that is daunting, and the benefits uncertain.
Personally I think that xmin-only advertisement is a pretty good
tradeoff.

regards, tom lane