The following bug has been logged online:

Bug reference: 1229
Logged by: Andreas Heiduk

Email address: Andreas.Heiduk (AT) web (DOT) de

PostgreSQL version: 7.4.3

Operating system: Linux Debian testing (i86)

Description: memory leak in backend with COPY and FOREIGN KEY

Details:

Short version:

I'm trying to COPY ~18Mrows into a table which has a foreign key to another
table. Memory and swap are exhausted and finaly the postgres.log says:

[2354] ERROR: could not write block 104984 of relation "track": Cannot
allocate memory
CONTEXT: writing block 104984 of relation 252172594/252175859
COPY track, line 10205801: "rock a00e050c 2 La
De Da \N 38443"

If I try to load the same data without the foreign key to the other table,
the memory usage is constant and low. If I create the foreign key after the
COPY, the memory consumption is also constant and low.


Longer Version:

Here are the tables in question:

Table "public.disc"
Column | Type | Modifiers
-----------+-------------------+-----------
category | character varying | not null
discid | character(8) | not null
title | character varying | not null
ext | character varying |
year | integer |
genre | character varying |
playtime | integer | not null
playorder | character varying |
rev | integer | not null
processor | character varying | not null
submitter | character varying | not null
Indexes:
"disc_pkey" primary key, btree (category, discid)

Table "public.track"
Column | Type | Modifiers
----------+-------------------+-----------
category | character varying | not null
discid | character(8) | not null
number | smallint | not null
title | character varying |
ext | character varying |
foffset | integer | not null

The foreign key constraint is created with

ALTER TABLE track ADD FOREIGN KEY (category, discid) REFERENCES
disc(category, discid);

The "disc" table is filled with 1.4Mrows, the "track" table should be filled
with 18Mrows.
Both tables are loaded with psql \copy from text files.
ps(1) says, that the memory is consumed by the postgres process handling the
COPY.

The postgres version is:

PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC)
3.3.4 (Debian 1:3.3.4-3)




---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Hello!

After splitting the the job into smaller pieces (e.g. 18x 1Mrow) the backend process now seems to release the memory after each subjob. Therefore the trigger queue seems to be a good candidate. Until now this queue was unknown to me.

Perhaps a note in the docu of COPY FROM and in the section "13.4.2 Use COPY FROM" within "Performance Tips" would prevent other people like me doing such bad things.

Many thanks for the fast help.

Andreas Heiduk


Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> schrieb am 24.08.04 19:25:56:

__________________________________________________ _____________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings