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(dot)oliver(at)gmail(dot)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(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343