Re: BUG #16373: Behavior of Temporary table creation

Hi Tom, thanks a lot for the reply and clearing this out for me!

I did some digging and figured that the backend file is created as soon as
the create temp table is executed like you mentioned (with relpersistence
as RELPERSISTENCE_TEMP, further calling smgrcreate() to create the backend
file).
https://doxygen.postgresql.org/storage_8c_source.html#l00118
https://doxygen.postgresql.org/smgr_8c_source.html#l00333

Q=> So does that mean postgres does not have *only* “in memory” temporary
tables in any case?
At best, what it is going to do is to cache the temp. table pages in the
temp buffer, but the blocks are still there on the storage, its just cached
in memory for the time being, no matter how small the temporary table is.
is it correct?

There are conflicting information around this and postgres docs are also
not very clear with regards to temp tables and how its handled.
In the below forum discussion as well, it is mentioned that “For a small
table the data will be in the memory, For a large table if data is not fit
in memory then data will be flushed to disk periodically as the database
engine needs more working space for other requests.
..
The memory is controlled by temp_buffers parameter”
https://www.postgresql.org/message-id/CALnrrJThsS3ZshTvsUOpBNbSQKkRWSnRaPS9CtVHwYUJh4%2Bgww%40mail.gmail.com

Q=> I might seem to be misunderstanding the working here, but flushing from
the temp buffers to the disk by checkpointer will be done every time the
checkpointer kicks in, and not only if the temp_buffer is full/exceeded,
right?

Thanks again for all the help!

Regards,
Hritik

On Fri, 17 Apr 2020 at 19:04, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > 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,
>
> They are accessed through temp buffers, yes, but storage space gets
> reserved on-disk immediately whenever the relation is extended.
> This is the same as for non-temp tables.
>
> regards, tom lane
>

--
Regards,
Hritik Gupta