The following bug has been logged on the website:
Bug reference: 6763
Logged by: Luben Karavelov
Email address: [email protected]
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
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
