Обсуждение: getting around 'out of memory' errors
Hi, I've recently been using PostgreSQL and am having some trouble
performing an insert.
My situation is this:
I have a table, A with 15 fields, out of which I am interested in 2
fields, a and b. The table has 8,000,000 rows
I have another table, B, which has 3 fields a, c, and d. The field a
references field a in table A. Table B is empty at this point.
I tried to do an insert using the following command:
insert into B (a,c,d) select a, f1(b), f2(b) from A;
where f1() and f2() are some functions. This ran for more than 5 hours
and then exited with an error:
ERROR: out of memory
DETAIL: Failed on request of size 44
I wondered whether this was because temporary columns were being
generated. So I then wrote a small PL/pgSQL function:
create or replace function fpinsert() returns void as '
declare
arow record;
counter integer := 0;
begin
for arow in select * from A loop
execute ''insert into B (a, c, d) values (''
|| quote_literal(arow.a) || '',''
|| ''f1('' || quote_literal(arow.b) || ''),''
|| ''f2('' || quote_literal(arow.b) || ''));'';
counter := counter + 1;
end loop;
return;
end;
' language 'plpgsql';
I assumed that this would run (possible slower) since it would loop over
the rows one by one. However this also failed with an out of memory
error (same as above, though the request size was 29).
Am I doing anything obviously wrong? I don't really care that the insert
takes a long time as it's a one time thing. But I can't see why my
PL/pgSQL function will also run out of memory, since it's operating row
by row.
Any pointers would be greatly appreciated.
The machine I'm running on has 16GB of RAM and I'm running PostgreSQL
7.4.13 on RedHat Enterprise Linux
Thanks,
-------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
All great ideas are controversial, or have been at one time.
Rajarshi Guha <rguha@indiana.edu> writes:
> I have a table, A with 15 fields, out of which I am interested in 2
> fields, a and b. The table has 8,000,000 rows
> I have another table, B, which has 3 fields a, c, and d. The field a
> references field a in table A. Table B is empty at this point.
> I tried to do an insert using the following command:
> insert into B (a,c,d) select a, f1(b), f2(b) from A;
I think you are getting burnt by the list of pending trigger actions
to check the foreign-key references in B. Might be easiest to drop the
foreign key constraint, fill table B, re-add the constraint. I forget
how smart 7.4 is about adding FK constraints exactly, but it shouldn't
run out of memory anyway.
regards, tom lane
On Thu, 2006-08-24 at 10:50 -0400, Tom Lane wrote: > Rajarshi Guha <rguha@indiana.edu> writes: > > I have a table, A with 15 fields, out of which I am interested in 2 > > fields, a and b. The table has 8,000,000 rows > > > I have another table, B, which has 3 fields a, c, and d. The field a > > references field a in table A. Table B is empty at this point. > > > I tried to do an insert using the following command: > > insert into B (a,c,d) select a, f1(b), f2(b) from A; > > I think you are getting burnt by the list of pending trigger actions > to check the foreign-key references in B. Might be easiest to drop the > foreign key constraint, fill table B, re-add the constraint. I forget > how smart 7.4 is about adding FK constraints exactly, but it shouldn't > run out of memory anyway. Thanks for the pointer. I've dropped the constraint and am now running the INSERT. However when I look at the output of top, I'm seeing that the %MEM value is continuously increasing and I'm worried that I'm going to hit the same problem in a few hours. One thing I did not mention previously is that table A has some constraints on some fields (notably field b is specified to be NOT NULL). My understanding is that these constraints would not matter since I am simply performing a SELECT - is this a correct assumption? Or should I temporarily drop those constraints as well? Thanks, ------------------------------------------------------------------- Rajarshi Guha <rguha@indiana.edu> GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE ------------------------------------------------------------------- Q: What's yellow, linear, normed and complete? A: A Bananach space.
Rajarshi Guha <rguha@indiana.edu> writes:
> On Thu, 2006-08-24 at 10:50 -0400, Tom Lane wrote:
>> I think you are getting burnt by the list of pending trigger actions
>> to check the foreign-key references in B.
> Thanks for the pointer. I've dropped the constraint and am now running
> the INSERT.
> However when I look at the output of top, I'm seeing that the %MEM value
> is continuously increasing and I'm worried that I'm going to hit the
> same problem in a few hours.
Sure you got all the FK constraints involving table B? Do you have any
AFTER triggers other than the FK constraints?
> One thing I did not mention previously is that table A has some
> constraints on some fields (notably field b is specified to be NOT
> NULL).
Plain old CHECK constraints shouldn't matter. Tell us more about those
functions in the SELECT though --- what are they?
regards, tom lane
On Thu, 2006-08-24 at 14:12 -0400, Tom Lane wrote: > Rajarshi Guha <rguha@indiana.edu> writes: > > On Thu, 2006-08-24 at 10:50 -0400, Tom Lane wrote: > >> I think you are getting burnt by the list of pending trigger actions > >> to check the foreign-key references in B. > > > Thanks for the pointer. I've dropped the constraint and am now running > > the INSERT. > > However when I look at the output of top, I'm seeing that the %MEM value > > is continuously increasing and I'm worried that I'm going to hit the > > same problem in a few hours. > > Sure you got all the FK constraints involving table B? Do you have any > AFTER triggers other than the FK constraints? Yes, \d B shows no constraints > > One thing I did not mention previously is that table A has some > > constraints on some fields (notably field b is specified to be NOT > > NULL). > > Plain old CHECK constraints shouldn't matter. Tell us more about those > functions in the SELECT though --- what are they? That would be a problem as I have not written them. I know the INSERT (and PL/pgSQL function) and functions f1() and f2() work on some trivial test tables - I should probably contact the developer. Thanks, ------------------------------------------------------------------- Rajarshi Guha <rguha@indiana.edu> GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE ------------------------------------------------------------------- A bug in the hand is better than one as yet undetected.