Обсуждение: CTEs and temp_buffers?

On Tue, Apr 26, 2022 at 10:08:31AM -0700, Wells Oliver wrote:
> Out of curiosity, does the value of temp_buffers apply to how CTEs are
> 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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

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


Bruce Momjian wrote on 4/26/2022 1:48 PM:

On Tue, Apr 26, 2022 at 10:08:31AM -0700, Wells Oliver wrote:

Out of curiosity, does the value of temp_buffers apply to how CTEs are
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.

Regards,

Michael Vitale, Sr. PostgreSQL DBA

Michaeldba@sqlexec.com

703-600-9343 

Wells Oliver <wells.oliver@gmail.com> writes:
> Out of curiosity, does the value of temp_buffers apply to how CTEs are
> 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

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.

Tom Lane wrote on 4/26/2022 2:03 PM:

Wells Oliver <wells.oliver@gmail.com> writes:

Out of curiosity, does the value of temp_buffers apply to how CTEs are
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

Regards,

Michael Vitale, Sr. PostgreSQL DBA

Michaeldba@sqlexec.com

703-600-9343 

MichaelDBA <MichaelDBA@sqlexec.com> writes:
> 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.

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