title: ERROR: out of shared memory
body: <p>I have a query that inserts a given number of test records.
  It looks something like this:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION _miscRandomizer(vNumberOfRecords int)
  RETURNS void AS $$
  declare
      -- declare all the variables that will be used
  begin
      select into vTotalRecords count(*) from tbluser;
      vIndexMain := vTotalRecords;
  
      loop
          exit when vIndexMain &gt;= vNumberOfRecords + vTotalRecords;
  
          -- set some other variables that will be used for the insert
          -- insert record with these variables in tblUser
          -- insert records in some other tables
          -- run another function that calculates and saves some stats regarding inserted records
  
          vIndexMain := vIndexMain + 1;
          end loop;
      return;
  end
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>When I run this query for 300 records it throws the following error:</p>
  
  <pre class="lang-sql prettyprint-override"><code>********** Error **********
  
  ERROR: out of shared memory
  SQL state: 53200
  Hint: You might need to increase max_locks_per_transaction.
  Context: SQL statement "create temp table _counts(...)"
  PL/pgSQL function prcStatsUpdate(integer) line 25 at SQL statement
  SQL statement "SELECT prcStatsUpdate(vUserId)"
  PL/pgSQL function _miscrandomizer(integer) line 164 at PERFORM
  </code></pre>
  
  <p>The function prcStatsUpdate looks like this:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION prcStatsUpdate(vUserId int)
  RETURNS void AS
  $$
  declare
      vRequireCount boolean;
      vRecordsExist boolean;
  begin
      -- determine if this stats calculation needs to be performed
      select into vRequireCount
          case when count(*) &gt; 0 then true else false end
      from tblSomeTable q
      where [x = y]
        and [x = y];
  
      -- if above is true, determine if stats were previously calculated
      select into vRecordsExist
          case when count(*) &gt; 0 then true else false end
      from tblSomeOtherTable c
      inner join tblSomeTable q
         on q.Id = c.Id
      where [x = y]
        and [x = y]
        and [x = y]
        and vRequireCount = true;
  
      -- calculate counts and store them in temp table
      create temp table _counts(...);
      insert into _counts(x, y, z)
      select uqa.x, uqa.y, count(*) as aCount
      from tblSomeOtherTable uqa
      inner join tblSomeTable q
         on uqa.Id = q.Id
      where uqa.Id = vUserId
        and qId = [SomeOtherVariable]
        and [x = y]
        and vRequireCount = true
      group by uqa.x, uqa.y;
  
      -- if stats records exist, update them; else - insert new
      update tblSomeOtherTable 
      set aCount = c.aCount
      from _counts c
      where c.Id = tblSomeOtherTable.Id
        and c.OtherId = tblSomeOtherTable.OtherId
        and vRecordsExist = true
        and vRequireCount = true;
  
      insert into tblSomeOtherTable(x, y, z)
      select x, y, z
      from _counts
      where vRecordsExist = false
        and vRequireCount = true;
  
      drop table _counts;
  end;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>It looks like the error is a result of a memory building up somewhere but since I create temp table, use it and drop right away (thus to my understanding releasing memory), I don't see how that would be possible.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I updated prcStatsUpdate function to represent the actual function that I have. I just replaced table and column names to be something generic.
  The reason I didn't post this first time is that it's mostly very simple sql operations and I assumed there could not be any issues with it.</p>
  
  <p>Also, where do you start line counting from? It says error is on line 25, but that just can't be true since line 25 is a condition in the <code>where</code> clause if you start counting from the beginning. Do you start counting from <code>begin</code>?</p>
  
  <p>Any ideas?</p>