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.
i wrote this code:
CREATE TYPE srf_get_old_cf_for_advert AS (
codename TEXT,
value TEXT
);
CREATE OR REPLACE FUNCTION get_old_cf_for_advert(INT8) RETURNS setof srf_get_old_cf_for_advert AS $BODY$
my $advert_id = shift;
my $cf_map = {};
my $sth = spi_query("SELECT v.* FROM adverts a JOIN v_category_custom_fields v ON
a.category_id = v.category_id WHERE a.id = $advert_id");
while (my $row = spi_fetchrow($sth)) {
$cf_map->{ $row->{'codename'} } = $row->{'custom_field_name'};
}
my $old_cf = spi_query("SELECT acf.* FROM advert_custom_fields acf WHERE acf.advert_id = $advert_id");
my $row = spi_fetchrow($old_cf);
return unless $row;
for my $key (keys %{ $cf_map }) {
my $cf_name = $cf_map->{ $key };
my $cf_value = $row->{ $cf_name };
next unless defined $cf_value;
return_next(
{
'codename' => $key,
'value' => $cf_value,
}
);
}
return;
$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;
to give some more details:
- in both tables (advert_custom_fields and adverts) we have 308428 adverts.
- computer i was running it on is just a workstation - 1g of memory, 5400 rpm sata hdd (laptop)
memory settings:
# - Memory -
shared_buffers = 20000kB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8000kB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
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.
CONTEXT: PL/pgSQL function "migrate_cf_old_to_hstore" line 6 at for over select rows
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?
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?
best regards,
hubert
--
http://www.depesz.com/ - nowy, lepszy depesz
