Re: BUG #3483: Dropped temporary tables filled up the disk

"Brent Reid" <bfraci(at)aol(dot)com> writes:
> Description: Dropped temporary tables filled up the disk

I believe what's going on in your test case is that

(1) The two sessions are competing for shared buffers; this means that
sometimes the SELECT (session B) has to flush buffers that were dirtied
by session A. This means it will set up SmgrRelation cache entries
(effectively, open files) for tables that it would otherwise never have
reason to access --- specifically, the temp tables that your sessions A
are generating.

(2) There is a mechanism to signal smgr cache invalidation when a
table is dropped; and that should result in the open file getting
closed. However, the inval message queue is only checked
by a backend when it starts a command or needs to open a new table.
Thus, if you've got a long-running command in session B it would not
act on the inval messages right away, and would accumulate open file
entries for temp tables that might have been dropped later.

What's not real apparent to me is how your Thread B managed to avoid
processing inval messages for a long time, as apparently it must have
done to acquire open file pointers for a large number of tables later
dropped by Thread A. Your description doesn't suggest that it was
engaged in a single very long SQL command. It could also avoid inval
processing if it were idle, of course, but it wasn't idle according to
your description, and anyway it certainly couldn't accumulate new open
files while sitting idle. So there something about your coding methods
for Thread B that's a bit out of the ordinary. Can you show us anything
of that code, or more accurately the SQL it's using?

Another factor in this is that temp tables ordinarily wouldn't compete
for shared buffer space, and thus situation (1) shouldn't arise in the
first place if all the tables being created/dropped are temp. However,
if a temp table has a toast table for wide fields, the toast table is
not currently treated as temp --- that is, access to it goes through
shared buffers. This is something we oughta fix sometime, but it's
certainly not going to change in 8.1.x.

Aside from tweaking Thread B in whatever way is needed to get it to
check the inval queue more often, you might look at whether you can
reasonably increase shared_buffers to reduce the degree of competition.

regards, tom lane