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