Re: heap vacuum & cleanup locks

On Wed, Nov 9, 2011 at 3:46 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Holding buffer pins for a long time is a problem in Hot Standby also,
> not just vacuum.

Agreed.

> AFAIK seq scans already work page at a time for normal tables. So the
> issue is when we *aren't* using a seq scan, e.g. nested loops joins.
>
> Is there a way to solve that?

Well, I'm not sure of the details of how page-at-a-time mode works for
seq scans, but I am absolutely 100% sure that you can reproduce this
problem using a cursor over a sequential scan. Just do this:

create table test (a text);
insert into test values ('aaa'), ('bbb');
delete from test where a = 'aaa';
begin;
declare x cursor for select * from test;
fetch next from x;

Then switch to another session and run "VACUUM test". Prior to commit
bbb6e559c4ea0fb4c346beda76736451dc24eb4e, this would hang. Now, it
doesn't. But "VACUUM FREEZE test" still does.

As for what to do about all this, I think Tom's idea would work for
good tuples, but the current freezing code can't handle dead tuples;
it counts on those having been already removed. I wonder if we could
just set xmin = InvalidTransactionId and set HEAP_XMIN_INVALID, or
something like that. I'm worried that there might be code out there
that thinks InvalidTransactionId can never appear in a real tuple.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company