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

BUG #6763: Severe memory leak with arrays and hstore

From: karavelov(at)mail(dot)bg
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6763: Severe memory leak with arrays and hstore
Date: 2012-07-26 01:32:12
Message-ID: E1SuCw0-0007s2-5D@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6763
Logged by:          Luben Karavelov
Email address:      karavelov(at)mail(dot)bg
PostgreSQL version: 9.1.4
Operating system:   Debian Linux
Description:        

I was trying to migrate a big ER table (user preferences) into a new table.

Old able:
old_prefs (
    user_id integer NOT NULL,
    name    varchar NOT NULL,
    value   varchar NOT NULL
);

New table:
preferences (
    user_id integer PRIMARY KEY,
    prefs   hstore;
);
The query I have tried to use is:

INSERT INTO preferences 
SELECT user_id,hstore(array_agg(name), array_agg(value)) 
FROM old_prefs 
GROUP BY user_id;

But the postgres backend consumed all the available memory (6G free + 4G
swap) and finally was killed by the kernel.

Its 8G RAM machine and here are the memory options from the config:

shared_buffers = 1800MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 256MB
max_stack_depth = 2MB

Finally I have managed to migrate it in batches of 100-200k user ids and
disconnecting after each query in order to free the backend and leaked
memory.

Best regards
Luben



Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-07-26 05:27:17
Subject: Re: BUG #6763: Severe memory leak with arrays and hstore
Previous:From: Bruce MomjianDate: 2012-07-25 20:40:14
Subject: Re: Re: [BUGS] BUG #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)

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