Re: [SQL] Questions about vacuum analyze

"Steven M. Wheeler" <swheeler@sabre.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, egsetenv 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