Re: pgsql_tmp consuming all inodes

"Nestor A. Diaz" <nestor(at)tiendalinux(dot)com> writes:
> I have a couple of databases running on a cluster, and two of them have
> been experiencing the following behavior since installed yesterday:
> They create a lot of files under directory "pgsql_tmp" up to the point
> they consume all the inodes, when I do a 'df -i' I found the partition
> assigned to the tablespace where the database is located is out of inodes.

I'd bet money that what you've got is a broken query, ie something that's
trying to compute a ridiculously large join. PG will faithfully try to
do it, until it runs out of disk space to hold the intermediate result.
It's not exactly hard to do that in SQL --- forget a join condition,
say, and suddenly you've got a Cartesian-product intermediate result.

> On the other side our experience with 8.4 have been good, except that
> today the database goes in recovery mode due to an out of memory error:

Sounds a bit like the same problem in a different guise.

regards, tom lane