BUG #16373: Behavior of Temporary table creation - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16373
Logged by:          Hritik Gupta
Email address:      hritik122@gmail.com
PostgreSQL version: 10.11
Operating system:   MacOS
Description:

Hi! Have a small doubt I’m hoping you can help me with..simply put, where
are the temporary tables created? 

AFAIK, they stay in the memory till the size reaches the temp_buffers limit,
and then to the storage, for where it is accessed using the temp buffer, and
this is inline with the below PG forum as well :
 
https://www.postgresql.org/message-id/flat/CAMuJg2SnL78C7kWE4Fu8c7NUEFW_bf-4G2c9-4Sa%3DfMLV4bLqA%40mail.gmail.com

Kind of refers in the parameter itself as well:

https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-BUFFERS
https://stackoverflow.com/questions/57306828/does-postgres-support-in-memory-temp-table

Here, Tom supported the understanding as well so I was thinking this is how
its supposed to work. But testing it recently bought a contradictory
behavior where the temp tables are directly created on disk even if they are
well below the temp_buffer size (which to my understanding should’ve been in
the memory). Below is the testing: 


hrigupta=# select version();
-[ RECORD 1
]---------------------------------------------------------------------------------------------------------------
version | PostgreSQL 10.11 on x86_64-apple-darwin18.7.0, compiled by Apple
clang version 11.0.0 (clang-1100.0.33.12), 64-bit

hrigupta=# show temp_buffers;
-[ RECORD 1 ]+----
temp_buffers | 8MB

hrigupta=# create temporary table hg1 (id int);
CREATE TABLE

Expanded display is on.

hrigupta=# select * from pg_class where relname like 'hg1';
-[ RECORD 1 ]-------+------
relname             | hg1
relnamespace        | 57585
reltype             | 57589
..
relfilenode         | 57587
..
hrigupta=# select pg_relation_filepath(57587);
-[ RECORD 1 ]--------+--------------------
pg_relation_filepath | base/32944/t3_57587

#inserted few records : 
hrigupta=# insert into hg1 values (generate_series(1,100000));
INSERT 0 100000

hrigupta=# \dt+
List of relations
-[ RECORD 1 ]--------------------
Schema      | pg_temp_3
Name        | hg1
Type        | table
Owner       | hrigupta
Size        | 3920 kB

Here the size is ~3.8 MB and should’ve been in memory as its still within
temp_buffers, but the file is actually created on disk with 3.8 MB size (and
increases as I insert the records). this should’ve been the behavior after
the size exceeds 8 MB (temp_buffers)

8c859021cf33:32944 hrigupta$ ls -lhr | head -3
total 33456
-rw-------  1 hrigupta  admin    24K Apr 16 17:04 t3_57587_fsm
-rw-------  1 hrigupta  admin   3.8M Apr 16 17:05 t3_57587

So my questions are: 

- why are temp tables creating files in the disk when there the size is
actually less than the memory limit (temp_buffers) — Please note that there
is only one temp table for the session (and nothing else consuming the
temp_buffers)

- Another question I am concerned with is that as Tom mentioned, “toast
table is also temp”. So technically access to toast tables are done through
temp buffers rather than the shared buffer? How does that work.. because we
would need to pull up pages for the relation in shared buffer to access it,
but they might have associated toast tables as well. Are access to the data
in those toast tables done separately in temp buffers? 
==> has a discussion on this and it seems like the mentioned reference is
for toast tables associated with temp tables only, which makes sense. i'd
just like to re-confirm the statement..

Thanks so much for the help!