Re: CTEs and temp_buffers?

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