Re: PostgreSQL 9.2.4 temp files never released?

Em 28/09/2013 18:12, Tomas Vondra escreveu:
> On 28 Září 2013, 22:54, Edson Richter wrote:
>> 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.
> Do you realize the counters in pg_stat_database (temp_files, temp_bytes)
> are counters tracking all the temp files created since the last reset of
> the stats (possible since the cluster was created)?
>
> So these values only increment (and it's meant to behave like that). BTW
> this is true for most values in the stats catalogs, so there's nothing
> like "current stats" - to get something like that you need to get two
> snapshots and subtract them (to get the delta).
>
> Tomas

Ah, ok! One more lesson learned...
I would expect stats being current of, or up-to-date after a vacuum
analyze...
Thank you all very much.

Edson