I have an application which, during the normal course of its operation, creates a single temporary table.
BEGIN; -- bunch of stuff here CREATE TEMPORARY TABLE results ON COMMIT DROP AS SELECT <big huge select here> -- a bit more stuff here ROLLBACK; In this case, *every* transaction ends in a rollback. 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. I traced this to, I think, src/backend/access/transam/slru.c in the SimpleLruFlush routine. My guess (probably wrong) is that there is no way to signal to the LRU cache that a file is /really/ no longer needed without flushing all of them. Since creating a temporary table results in (probably several) files that are created, these files are not removed until either a) room for them is necessary in the LRU cache or, b) the server is shut down. I probably have this wrong. What can be done to remove all of the files associated with dropped (temporary) tables _when_ the they are dropped? I'm using 8.4.5. -- Jon -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
