Subject: CTEs and temp_buffers?

From: Wells Oliver <[email-masked]>
Date: Tue, 26 Apr 2022 10:08:31 -0700

Out of curiosity, does the value of temp_buffers apply to how CTEs are
generated under the hood?

-- 
Wells Oliver
[email-masked] <[email-masked]>

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 26 Apr 2022 13:48:21 -0400

> generated under the hood?

To see temp file usage, you can enable log_temp_files, and check the
server logs or enable client_min_messages.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

---

From: MichaelDBA <[email-masked]>
Date: Tue, 26 Apr 2022 13:59:02 -0400

Hmmm, I think you may be wrong about that one, Bruce.  I tried that 
before and log_temp_files seems to only log work_mem errors, not 
temp_buffers associated with temporary table allocations. The only way 
to see temp files used by temporary tables is to monitor the 
*<datadir>/base/pgsql_tmp*directory, and that can be quite tricky since 
it happens so fast there.

Regards,
Michael Vitale

> server logs or enable client_min_messages.

Regards,

Michael Vitale, Sr. PostgreSQL DBA

[email-masked] <mailto:[email-masked]>

703-600-9343

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Apr 2022 14:03:15 -0400

> generated under the hood?

No.  Intermediate results within a query (whether CTE or not) might
get spilled to disk in a "temporary file", but that's a distinct
mechanism from temp tables, which is what temp_buffers applies to.

Bruce's nearby answer explains how you can control/monitor temp
files, but he didn't actually answer your question ;-)

			regards, tom lane

---

From: MichaelDBA <[email-masked]>
Date: Tue, 26 Apr 2022 14:55:54 -0400

Hi Tom,
Of course, you're right! I got sidetracked thinking about temp_buffers 
with respect to temporary tables and not CTEs where work_mem stuff would 
apply.  But back to the temp_buffers thing.  Can you acknowledge that my 
thinking is right about that?  That temporary table buffers if exceeding 
temp_buffers gets logged to the *<datadir>/base/pgsql_tmp* area and is 
not logged in the logs at all?   My testing seems to confirm that.

Regards,

703-600-9343

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Apr 2022 15:03:56 -0400

> not logged in the logs at all?   My testing seems to confirm that.

No.  Temp tables go into the same directories as regular tables.
The files have funny names though, following the format "tN_OID"
rather than just "OID".

			regards, tom lane