Re: HINT: Perhaps out of disk space?

On Fri, Dec 23, 2005 at 11:36:54AM -0500, Tom Lane wrote:
> Michael Adler <adler(at)pobox(dot)com> writes:
> > I'm investigating a problem that happened last night and I would
> > appreciate any recommendations. The logs indicate that the disks were
> > full, but I truly doubt that since we only use about 14GB out of the
> > available 65GB.
>
> > I found entries like this in the logs:
>
> > ERROR: could not write block 2354 of temporary file: No space left on device
> > HINT: Perhaps out of disk space?
> > ....
> > ERROR: could not extend relation "parent_table": No space left on device
> > HINT: Check free disk space.
> > ....
> > LOG: could not close temporary statistics file "/var/lib/postgres/data/global/pgstat.tmp.1464": No space left on device
>
> > According to the logs, the problem went away after a reboot. I wonder
> > if the kernel or the RAID device got confused and postgres was simply
> > echoing what it was told. We run a couple hundred postgres servers and
> > we have not seen this before (except when the disks truly were full).
>
> I'm inclined to think that a query created a 50GB temporary file ...
> the postmaster cleans out temp files when restarted, so that would
> have destroyed the evidence.

I'm curious about what could have resulted in so much temporary
storage for a database that fits entirely in 2.5GB space. I can
imagine taking the largest table and joining it against itself many
times without a WHERE clause. What else would use a lot of temp
storage?

How long would it take to clean out 50GB of temp files? It looks like
the postmaster was able to start up instantly after the reboot (ready
less than 1 second after "LOG: database system was shut down at...")

I really appreciate any guidance you could offer.

-Mike