Re: Removing pgsql_tmp files

On Nov 8, 2010, at 16:03 , Tom Lane wrote:

> Michael Glaesemann <michael(dot)glaesemann(at)myyearbook(dot)com> writes:
>> We've got over 250GB of files in a pgsql_tmp directory, some with modification timestamps going back to August 2010 when the server was last restarted.
>
> That's very peculiar. Do you keep query logs? It would be useful to
> try to correlate the temp files' PIDs and timestamps with the specific
> queries that must have created them.

We don't log all of them, but I checked those we did. It looks like it's happening when queries are timing out. I'm seeing this pattern pretty consistently:

temporary file + query
canceling statement due to statement timeout
second temp file

Here's a sample:

pid | 877
sess_id | 4ccf7257.36d
sess_line | 16
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.412-04
error | LOG
sql_state | 00000
message | temporary file: path "pg_tblspc/16384/pgsql_tmp/pgsql_tmp877.0", size 87184416

pid | 877
sess_id | 4ccf7257.36d
sess_line | 17
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.412-04
error | ERROR
sql_state | 57014
message | canceling statement due to statement timeout

pid | 877
sess_id | 4ccf7257.36d
sess_line | 18
filename | pgsql_tmp877.0
accessed_at | 2010-09-15 12:14:45-04
modified_at | 2010-11-01 22:37:00-04
logged_at | 2010-11-01 22:37:01.434-04
error | LOG
sql_state | 00000
message | temporary file: path "pg_tblspc/16384/pgsql_tmp/pgsql_tmp877.1", size 5480448
query |

It looks like the pgsql_tmp877.1 file is cleaned up as it doesn't appear in the pgsql_tmp directory.

> Personally, I'd not risk trying to match on PID; it should be sufficient
> to delete anything with a timestamp older than the oldest active
> backend. (Unless you've got some really long-lived sessions in
> there...)

That's easily-enough determined from pg_stat_activity.

> What PG version is this?

select version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
(1 row)

Michael Glaesemann
michael(dot)glaesemann(at)myyearbook(dot)com