The following bug has been logged online:
Bug reference: 1229
Logged by: Andreas Heiduk
Email address: [EMAIL PROTECTED]
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
