BUG #6763: Severe memory leak with arrays and hstore - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      6763
Logged by:          Luben Karavelov
Email address:      karavelov@mail.bg
PostgreSQL version: 9.1.4
Operating system:   Debian Linux
Description:=20=20=20=20=20=20=20=20

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=20
SELECT user_id,hstore(array_agg(name), array_agg(value))=20
FROM old_prefs=20
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 =3D 1800MB
temp_buffers =3D 16MB
work_mem =3D 64MB
maintenance_work_mem =3D 256MB
max_stack_depth =3D 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