Re: PostgreSQL 9.2.4 temp files never released?

Em 28/09/2013 15:54, Adrian Klaver escreveu:
> On 09/28/2013 11:30 AM, Edson Richter wrote:
>> Em 28/09/2013 15:22, Adrian Klaver escreveu:
>>> On 09/28/2013 11:16 AM, Edson Richter wrote:
>>>> I've a 12Gb database running without problems in Linux Centos 64bit
>>>> for
>>>> years now.
>>>> Looking database statistics (in pgAdmin III), I can see that there are
>>>> 366 temporary files, and they sum up 11,863,839,867 bytes in size.
>>>
>>> What are the temp files named and where are they located?
>>
>> Sorry if this sounds silly, but how can I discover this information?
>
> Assuming pgAdmin is using  pg_stat_database then:
>
> http://www.postgresql.org/docs/9.2/static/storage-file-layout.html
>
> "Temporary files (for operations such as sorting more data than can
> fit in memory) are created within PGDATA/base/pgsql_tmp, or within a
> pgsql_tmp subdirectory of a tablespace directory if a tablespace other
> than pg_default is specified for them. The name of a temporary file
> has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning
> backend and NNN distinguishes different temporary files of that backend."

Ok. Found the place.
So, there is nothing there. PG_DATA/base/pgsql_tmp is a empty directory.
I've run the query over pg_stat_database view and there is nothing wrong
with pgAdmin III - the information is all there.
I've also run a vacuum freeze analyze, but made no difference.

I believe that statistics are outdated, since there is no temp file at all.

Any tip on that?

Thanks,
Edson