Re: [SQL] Questions about vacuum analyze

"Steven M. Wheeler" <swheeler(at)sabre(dot)com> writes:
> I am now running into another problem. I need to do select distinct from the
> currnt to get dates for maintenance activity (rows to summarize and move to
> history). When ever I do I get this message in my server.log file:

> NOTICE: BufFileRead: should have flushed after writing

Hmph --- just looking at the code that issues that message, it looks
like it is a harmless gripe (it complains, but then goes and does the
missing step). However, it's suggestive that there may be a logic error
in the sorting code that's calling the BufFileXXX routines.

> And I get 0 records selected.

And that's even more suggestive ;-)

This is going to be tough to debug if it takes a multi-gig database to
replicate. But if I'm guessing right about where the problem is, it
probably can be replicated on smaller tables if you use a smaller -S
(sort memory limit) setting when starting the backend. You can set -S
as small as 32 (kb), and having done that the bug might show up on tables
with a few hundred K of data. Would you try that and let me know?

The easiest way to experiment is to set -S on a per-backend basis
via the PGOPTIONS environment variable, eg
setenv PGOPTIONS "-S 32"
psql ...
The backend started for this psql session will set S=32 instead of
whatever the prevailing default is.

regards, tom lane