Re: [SQL] Questions about vacuum analyze - Mailing list pgsql-sql

More information on the continuing vacuum saga:
The latest run after having processed an additional 40K+ msgs (4M+ inserts).
Start: 10/06/1999@07:28:07
complete: 10/06/1999@21:17:32
About 14 hours.
Two tables.
The currnt table has 39.5M+ rows in it.  Select count(*) took 14m41s
The history table only has 190K rows.

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.  Can't find this message in the docs.  Any
suggestions?  Should I submit this on the SQL list?

Thanks, you're help is greatly appreciated!

--
Steven Wheeler
Mid-Range UNIX Engineering
Sabre Inc.
(918) 292-4119

Tom Lane wrote:

> "Steven M. Wheeler" <swheeler@sabre.com> writes:
> > I believe that 6.5.2 and a little space reclamation in my DB directory may
> > have taken care of the vacuum problem.  The last vacuum ran in about 10
> > minutes on an already vacuumed DB.
>
> Ah, that's more like it...
>
> > 1) With 6.5.2 should I still drop the indexes prior to running vacuum?
>
> Probably, but I'm not sure.  Try it both ways and see.
>
> > 2) Is there a command/script that will cleanup the temp files and previous
> > table files in the DB directory.  The reason I ask: I now have 3 copies of
> > my currnt table file (currnt, currnt.1, currnt.2) for a total usage of
> > about 3GB.  Since it appears that currnt.2 is the one being accessed, can I
> > safely delete currnt & currnt.1?
>
> NO NO NO NO NO!!!!
>
> Files named like that are NOT temp files!!  What they are are segments
> of a large table.  We segment big tables into gigabyte-sized chunks
> to avoid problems on systems that have an int32-related limit on the
> size of individual files (which is most Unixes these days, I think).
>
> If you see anything named like pg_tempNNN.NNN, then that really is
> a temp file, and if it's not got a very recent mod time then it's
> probably left over from a crashed backend.  Old temp files should be
> safe to get rid of.  (IIRC, one of the components of the name is the
> PID of the creating backend, so you can check to be sure that the
> backend is no longer around if you want to be doubly sure.)
>
>                         regards, tom lane