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

BUG #1229: memory leak in backend with COPY and FOREIGN KEY

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1229: memory leak in backend with COPY and FOREIGN KEY
Date: 2004-08-24 16:41:04
Message-ID: 20040824164104.1AFE35A1195@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1229
Logged by:          Andreas Heiduk

Email address:      Andreas(dot)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) 




Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-08-24 17:25:32
Subject: Re: BUG #1229: memory leak in backend with COPY and FOREIGN
Previous:From: PostgreSQL Bugs ListDate: 2004-08-24 16:33:46
Subject: BUG #1228: numeric field from a view from a view does not recognise any where values

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