Re: A bit confused about "pgsql_tmp" vs "temp tablespace"

On 07/18/2018 06:57 AM, Thomas Kellerer wrote:
> Adrian Klaver schrieb am 18.07.2018 um 15:06:
>>> In the chapter "Database File layout" the pgsql_tmp is explained as follows:
>>>
>>>      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
>>>
>>> However the documentation for "temp_tablespaces" states:
>>>
>>>      Temporary files for purposes such as sorting large data sets are also created
>>>      in these tablespaces.
>>>
>>>
>>> How do these two things related to each other?
>>>
>>> Does this mean that if I do not explicitly create a dedicated "temp tablespace" then the pgsql_tmp subdirectory is
used.
>>> But _if_ I do create a temp tablespace (by creating one, and adding it to temp_tablespaces) then the sorting is
done*there*?
 
>>
>> Yes, for those objects that do not have a tablespace specified in their CREATE statement.
>>
>>>
>>> So far I thought that a temp tablespace is only used for temporary tables (and indexes on them) but that paragraph
inthe
 
>>> temp_tablespaces documentation seems to indicate otherwise.
>>
>> The Database File Layout section you quoted above says the same
>> thing. Basically setting temp_tablespaces just overrides where temp
>> objects and operation files are placed when a tablespace is not
>> specified in their creation.
> 
> Thanks.
> 
> I understand the relation between explicitly CREATEd objects and the temp tablespace(s).
> 
> But what about the (temp) space needed for e.g. sorting, grouping or intermediate results from CTEs or derived
tables?
> Is that also controlled through the temp_tablespaces?

Yes, all setting temp_tablespace from '' to some_tablespace(s) does is 
redirect the creation of unspecified temp files from the db 
default_namespace/pgsql_tmp to the named some_tablespace(s)/pgsql_tmp.

To verify this create a tablespace and add it to temp_tablespace and 
then do temp operations and look at the_tablespace/pgsql_tmp/.

> 
> 
>   
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com