Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA72581 for ; Thu, 7 Oct 1999 10:09:12 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA28914; Thu, 7 Oct 1999 10:08:30 -0400 (EDT) To: "Steven M. Wheeler" cc: pgsql-sql@hub.org Subject: Re: [SQL] Questions about vacuum analyze In-reply-to: Your message of Thu, 07 Oct 1999 06:29:38 -0500 <37FC8422.82295F9A@sabre.com> Date: Thu, 07 Oct 1999 10:08:30 -0400 Message-ID: <28912.939305310@sss.pgh.pa.us> From: Tom Lane "Steven M. Wheeler" 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