Re: BUG #16373: Behavior of Temporary table creation

Ah okay, that makes perfect sense and connects the dots in my head with
regards to temp tables and much more. Thanks so much for the explanation
and the reasoning behind why we reserve the space on the disk (i'm guessing
its the same reasoning for other entities as well, for instance, WAL
files).

Again, thank you so much, it has been such a help! I hope you're safe, do
have a great day!

Regards,
Hritik

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

> Hritik Gupta <hritik122(at)gmail(dot)com> writes:
> > 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?
>
> No. We *reserve space* on disk, typically by writing zeroes. But actual
> data would only get spilled there if we run out of temp buffer space.
> It's entirely possible for a temp table's data to stay in memory for
> its whole existence.
>
> For regular tables, there are effects such as checkpoints that would
> cause data to get written out from shared buffers periodically, even
> if there's plenty of buffer space. That doesn't apply to temp tables.
>
> The reason for reserving space is to try to avoid having to deal with
> ENOSPC failures during buffer spills. On newer filesystems that
> can happen anyway, of course, but it's still useful on non-COW
> filesystems.
>
> regards, tom lane
>

--
Regards,
Hritik Gupta