hubert depesz lubaczewski wrote:
hi,
i got this situation: i'm using 8.3devel checked out from cvs about a week
ago. if this is neccesary i can rerun the tests in 8.2 or something else.
CREATE OR REPLACE FUNCTION get_old_cf_for_advert(INT8) RETURNS setof
srf_get_old_cf_for_advert AS $BODY$
$BODY$ LANGUAGE 'plperl';
CREATE OR REPLACE FUNCTION migrate_cf_old_to_hstore(in_advert_id INT8)
RETURNS hstore AS $BODY$
declare
temprec RECORD;
use_cf hstore;
BEGIN
use_cf := '';
for temprec in SELECT * FROM get_old_cf_for_advert(in_advert_id) LOOP
use_cf := use_cf || ( temprec.codename => temprec.value );
END LOOP;
RETURN use_cf;
END;
$BODY$ language 'plpgsql';
CREATE TABLE hstore_migration as SELECT id as advert_id,
migrate_cf_old_to_hstore(id) as hstore_cf FROM adverts;
and - after some time of this "create table", postmaster process eats all
the memory (over 1.8g), and dies with:
psql:133.sql:125: ERROR: error from Perl function: no unpinned buffers
available at line 5.
my questions are:
1. is it a bug and will it be fixed?
2. if it is a bug - is it in hstore? plperl? my code?
My guess would be that plperl isn't freeing it's result set storage
until the end of the transaction. Might not be classed as a bug, but
certainly an inefficiency.
3. i can do the migration using small parts - let's say 100 records at a
time, disconnect, reconnect, convert next 100 records. but - will i be safe
later on during standard work?
Once the connection is closed, all memory should be freed.
But, it looks to me like you might be able to replace the plperl
function by just a straight query. That should be faster too.
--
Richard Huxton
Archonet Ltd
