Nope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:
I do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AM
work_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:
Friday, July 20, 2018 8:35 AM
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AM
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad