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.
