Re: Excessive memory used for INSERT

From: Tom Lane
Subject: Re: Excessive memory used for INSERT
Date: ,
Msg-id: 7359.1418830891@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Excessive memory used for INSERT  (Torsten Zuehlsdorff)
Responses: Re: Excessive memory used for INSERT  (Alessandro Ipe)
List: pgsql-performance

Tree view

Excessive memory used for INSERT  (Alessandro Ipe, )
 Re: Excessive memory used for INSERT  (Torsten Zuehlsdorff, )
  Re: Excessive memory used for INSERT  (Tom Lane, )
   Re: Excessive memory used for INSERT  (Alessandro Ipe, )
    Re: Excessive memory used for INSERT  (Tom Lane, )
     Re: Excessive memory used for INSERT  (Alessandro Ipe, )
      Re: Excessive memory used for INSERT  (Alessandro Ipe, )
       Re: Excessive memory used for INSERT  (Tom Lane, )
        Re: Excessive memory used for INSERT  (Alessandro Ipe, )
        Re: Excessive memory used for INSERT  (Alessandro Ipe, )
         Re: Excessive memory used for INSERT  (Tom Lane, )
          Re: Excessive memory used for INSERT  (Alessandro Ipe, )
  Re: Excessive memory used for INSERT  (Alessandro Ipe, )
 Re: Excessive memory used for INSERT  (Torsten Förtsch, )
  Re: Excessive memory used for INSERT  (Alessandro Ipe, )

Torsten Zuehlsdorff <> writes:
> How many rows is "(SELECT * FROM upsert)" returning? Without knowing
> more i would guess, that the result-set is very big and that could be
> the reason for the memory usage.

Result sets are not ordinarily accumulated on the server side.

Alessandro didn't show the trigger definition, but my guess is that it's
an AFTER trigger, which means that a trigger event record is accumulated
in server memory for each inserted/updated row.  If you're trying to
update a huge number of rows in one command (or one transaction, if it's
a DEFERRED trigger) you'll eventually run out of memory for the event
queue.

An easy workaround is to make it a BEFORE trigger instead.  This isn't
really nice from a theoretical standpoint; but as long as you make sure
there are no other BEFORE triggers that might fire after it, it'll work
well enough.

Alternatively, you might want to reconsider the concept of updating
hundreds of millions of rows in a single operation ...

            regards, tom lane



pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Excessive memory used for INSERT
From: Torsten Förtsch
Date:
Subject: Re: Excessive memory used for INSERT