Tomas Szepe <[EMAIL PROTECTED]> writes:
>> Are you running with autovacuum on, or not?
> At the moment autovacuum is off, but it _might_ have been on in the first
> 40 hours or so... Sorry, I can't say exactly.
Okay ... so with autovac off, I can reproduce a problem this way:
1. Put 1000 repetitions of this into a script file:
create temp table tt(x int, y int);
drop table tt;
2. Execute the script file, with no other sessions active.
3. vacuum full verbose pg_attribute;
You need a script file, not say a loop in a plpgsql function, because
the commands have to be in separate transactions. On a machine with
MAXALIGN=4 you might need a different number of columns in the temp
table --- I only tried it with MAXALIGN=8 hardware.
I believe what's happening here is that as we create and delete temp
tables, we prune the current insertion target page in pg_attribute
repeatedly, turning removed rows into LP_DEAD line pointers; but since
no actual vacuum gets done, the LP_DEAD line pointers can't go away and
gradually accumulate. Eventually there are MaxHeapTuplesPerPage line
pointers on the page, so no more can be inserted, and the insertion
activity shifts to a new page of pg_attribute. Then when we eventually
do VACUUM FULL, PageGetHeapFreeSpace returns zero "because it's full"
(this would happen whether or not notup was true). If notup does happen
to be true, we hit the incorrectly handled empty_end_pages case; and
in any case we are leaving money on the table because the page will
falsely be thought to not have any usable space. (So just fixing the
notup case isn't really enough here...)
With autovac on, this would be a lot harder to hit because autovac would
most likely clean the page before you'd gotten around to doing VACUUM
FULL. That might explain how the problem escaped notice during testing
--- AFAICS this behavior isn't specific to the system catalogs but would
occur in any table that received repetitive insertions/deletions. It's
a bit unnerving that it wasn't seen, though ... makes one wonder about
how thoroughly the HOT code has really been exercised in combination
with VACUUM FULL.
This needs a little thought about the cleanest way to fix. I'll put
up a proposed patch later.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
