Re: repeated out of shared memory error - not related to max_locks_per_transaction

MichaelDBA <MichaelDBA@xxxxxxxxxxx> · Fri, 20 Jul 2018 09:00:09 -0400

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

   	Fabio Pardi
        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:

   	MichaelDBA
        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

   	Campbell, Lance
        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