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

Fabio Pardi <f.pardi@xxxxxxxxxxxx> · Fri, 20 Jul 2018 15:19:23 +0200



    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

      
              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