Databases
StackOverflow2013
Postsdbo
POERROR: out of shared memory
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POERROR: out of shared memory
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDmitry
    1. USDmitry
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI updated my question. Thanks for suggestion to put the whole code.
      singulars
      1. POERROR: out of shared memory
      1. USDmitry
    2. CODid you try increasing max_locks_per_transaction?
      singulars
      1. POERROR: out of shared memory
      1. USMike Sherrill 'Cat Recall'
    3. COI didn't yet. Before I do so I thought I'd ask if anybody could point on an error that I might have missed. I'd like to understand what's causing this to happen.
      singulars
      1. POERROR: out of shared memory
      1. USDmitry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload