Skip site navigation (1) Skip section navigation (2)

Re: BUG #2428: ERROR: out of memory, running INSERT SELECT statement

From: Casey Duncan <casey(at)pandora(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2428: ERROR: out of memory, running INSERT SELECT statement
Date: 2006-05-10 16:34:20
Message-ID: 8B94F81F-52F9-4FBD-8D8A-2EEB237B4D1D@pandora.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On May 9, 2006, at 9:09 PM, Tom Lane wrote:

> "Casey Duncan" <casey(at)pandora(dot)com> writes:
>> CREATE FUNCTION write_error_trigf() RETURNS trigger AS '
>>     BEGIN
>>         RAISE EXCEPTION ''Writes not allowed to this table on this  
>> node'';
>>     END;
>> ' LANGUAGE plpgsql;
>> END; --Upgrade script
>
> You did not show how this function is being used, but I'm wondering if
> it is an AFTER trigger on inserts into ss?  If so, the reason for the
> out-of-memory failure might be accumulation of pending trigger event
> records.
>
> Without wishing to defend our lack of ability to spill trigger events
> to disk, you probably wouldn't be happy with the performance if it did
> work :-(.  Consider making the trigger BEFORE instead of AFTER, so  
> that
> there's not a need to remember a ton of pending trigger firings.  I  
> don't
> see any reason why this trigger needs to be AFTER.

That's good to know, but I think it's a red herring in this case. The  
upgrade script creates this function, but it doesn't setup the  
triggers themselves, another external script does that after the  
upgrade. Basically we're creating a multi-database cluster where all  
the dbs have the same schema, but you aren't supposed to write to  
certain tables in certain nodes.

The script actually fails before the function is even defined anyhow,  
on this statement:

INSERT INTO ss
     (ss_id, name, ll_id, shared_ss_id, time_added,
     shared_creator_id)
SELECT nextval('ss_id_seq'), s.name, lts.ll_id, s.ss_id,
     lts.time_added, s.ll_id
FROM ss AS s, ll_to_ss AS lts
WHERE lts.ll_id != s.ll_id;

There are no triggers of any kind on ss. Basically this statement is  
a step toward eliminating the ll_to_ss table (which was used for a  
many-to-many between ll and ss) and creating a one-to-many between ll  
and ss. This merges some portion of ll_to_ss with ss.

-Casey



In response to

Responses

pgsql-bugs by date

Next:From: bhavaniDate: 2006-05-11 05:29:02
Subject: BUG #2431: Error:SELECT query has no destination for result data
Previous:From: Cristiano DuarteDate: 2006-05-10 14:26:30
Subject: Re: BUG #2429: Explain does not report object's schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group