Re: temporary tables, and lots of 0 byte files

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> Here is the problem: When I started benchmarking this application, I
> noticed it (postgresql) started chewing up inodes on the logical
> volume which houses /var/lib/pgsql. It chewed up a few thousand
> inodes, and then a few thousand more, and then there were no more.

> I investigated /var/lib/pgsql/data/ and found within the database's
> directory many thousands of 0-byte files. I stopped the application
> expecting at the end of a /session/ that the inodes would be unlinked,
> but there was no change. lsof told me nobody had them open. However, a
> start/stop of postgresql removed (at least most of) the 0-byte files.
> After some trial and error, I straced the right process (the bgwriter
> process) and found that, upon signal to leave, it would write some
> checkpoint-y stuff and then proceed about unlinking most or all of the
> 0-byte files.

This is not a bug; it's normal behavior when dropping a table. The
table file is truncated to zero bytes at commit, but it's not unlinked
until the next checkpoint. I surmise that you may have a very long
checkpoint cycle time selected.

> What can be done to remove all of the files associated with dropped
> (temporary) tables _when_ the they are dropped?

Nothing other than forcing a checkpoint. There are
race-condition-related reasons for doing it like this, which I don't
have at the top of my brain, but you can find them in the archives
if you care enough.

regards, tom lane