Re: Removing pgsql_tmp files

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

> Michael Glaesemann <michael.glaesemann@myyearbook.com> writes:
>> We've got over 250GB of files in a pgsql_tmp directory, some with modification timestamps going back to August 2010
whenthe 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
seeingthis 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.glaesemann@myyearbook.com