pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hritik Gupta <hritik122@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16373: Behavior of Temporary table creation
Date: Fri, 17 Apr 2020 14:05:36 -0400
Message-ID: <18686.1587146736@sss.pgh.pa.us> (raw)
In-Reply-To: <CAHY7Mq4FYbwhQCXJ1GYG5FPWjo2Z5nun6-Z2S8spNyOHTkFz1g@mail.gmail.com>
References: <16373-a2325a5f0e87714c@postgresql.org>
	<19190.1587130449@sss.pgh.pa.us>
	<CAHY7Mq4FYbwhQCXJ1GYG5FPWjo2Z5nun6-Z2S8spNyOHTkFz1g@mail.gmail.com>

Hritik Gupta <hritik122@gmail.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





view thread (5+ messages)  latest in thread

Message-ID: <18686.1587146736@sss.pgh.pa.us>
Permalink:  ../18686.1587146736@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18686.1587146736@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, hritik122@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16373: Behavior of Temporary table creation
  In-Reply-To: <18686.1587146736@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox