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 KAA71920 for ; Fri, 8 Oct 1999 10:43:37 -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 KAA03106; Fri, 8 Oct 1999 10:43:01 -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: Fri, 08 Oct 1999 10:43:01 -0400 Message-ID: <3104.939393781@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 > And I get 0 records selected. After some code browsing I have come up with a theory: is it possible that you are running out of disk space during the sort? I see psort.c neglects to check for write failure :-(, although I am not entirely clear on why that particular message and nothing else would come out after it bulled ahead with an incomplete temporary file. Sorting a large table seems to require temp disk space equal to several times the size of the table. Since you said your table is 40M rows, I can easily believe you don't have that much free space... I don't believe anyone has taken a hard look at psort.c in a long time. I will try to dive into it and see if its resource consumption can't be reduced. Mergesort inherently requires 2X the original data space, but it seems that psort is using even more than that, and perhaps this can be improved. regards, tom lane