Re: "no unpinned buffers available" ? why? (hstore and

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