"no unpinned buffers available" ? why? (hstore and plperl involved)
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
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
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
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.
not good - but - if it is pl/perl only issue - i can live with it.
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 besafe
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.
really? i was thinking really hard on how to do it in sql, but didn't found
any way to achieve it. actually - i dont really think it would be possible
at all in standard sql. but then - maybe i'm wrong.
best regards,
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
"hubert depesz lubaczewski" <depesz@gmail.com> writes:
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.
Could you reduce this to a self-contained example please? Your
functions depend on a bunch of tables that you have not provided
definitions or data for ...
regards, tom lane
hubert depesz lubaczewski wrote:
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.really? i was thinking really hard on how to do it in sql, but didn't found
any way to achieve it. actually - i dont really think it would be possible
at all in standard sql. but then - maybe i'm wrong.
Well, it looks to me like the main problem is you're trying to convert a
table like: (advert_id, cust1, cust2, cust3) to: (advert_id, codename,
codevalue).
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.
--
Richard Huxton
Archonet Ltd
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.
no, because meaning of "col1" in advert_custom_fields is different for each
record.
for one record it's codename might be "email" for another record it might be
"engine size".
hubert
--
http://www.depesz.com/ - nowy, lepszy depesz
On 1/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Could you reduce this to a self-contained example please? Your
functions depend on a bunch of tables that you have not provided
definitions or data for ...
i'll try. it will take some time though.
hubert
--
http://www.depesz.com/ - nowy, lepszy depesz
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.no, because meaning of "col1" in advert_custom_fields is different for each
record.
for one record it's codename might be "email" for another record it
might be
"engine size".
And is that not what's stored in "v_category_custom_fields"? So you can
do the transformation and get (advert_id=1, codename='col1',
value='vvv') then use v_category_custom_fields to update the 'col1' part.
--
Richard Huxton
Archonet Ltd
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.no, because meaning of "col1" in advert_custom_fields is different for
each
record.
for one record it's codename might be "email" for another record it
might be
"engine size".And is that not what's stored in "v_category_custom_fields"? So you can
do the transformation and get (advert_id=1, codename='col1',
value='vvv') then use v_category_custom_fields to update the 'col1' part.
this information is stored there, yet i have no clue on how you would like
to make it with standard sql statements? for every "advert" there are about
20-30 "custom fields" (in one record in advert_custom_fields). to do it your
way i would need to make approximatelly 30 (numer of custom field) times
300000 (number of adverts) queries. that would be way slower and definitelly
not automatic.
best regards,
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
hubert depesz lubaczewski wrote:
On 1/3/07, Richard Huxton <dev@archonet.com> wrote:
If you do that separately at the start of the process, (one query per
custom column in the old table) then it becomes straightforward.no, because meaning of "col1" in advert_custom_fields is different for
each
record.
for one record it's codename might be "email" for another record it
might be
"engine size".And is that not what's stored in "v_category_custom_fields"? So you can
do the transformation and get (advert_id=1, codename='col1',
value='vvv') then use v_category_custom_fields to update the 'col1' part.this information is stored there, yet i have no clue on how you would like
to make it with standard sql statements? for every "advert" there are about
20-30 "custom fields" (in one record in advert_custom_fields). to do it
your
way i would need to make approximatelly 30 (numer of custom field) times
300000 (number of adverts) queries. that would be way slower and
definitelly
not automatic.
Show me the table definitions and some sample data and I'll see if the
SQL is do-able.
--
Richard Huxton
Archonet Ltd
hubert depesz lubaczewski wrote:
On 1/4/07, Richard Huxton <dev@archonet.com> wrote:
Show me the table definitions and some sample data and I'll see if the
SQL is do-able.technically - i can, but please - belive me it is not possible.
advert_custom_fields table has approx. 1200 columns (for reasons i was
explaining some time ago).
sample data would look like:
# select id, category_id from adverts order by id desc limit 5;
id | category_id
----------+-------------
35161391 | 35
35161390 | 35
35161389 | 230
35161388 | 34
35161387 | 37
(5 rows)# select * from v_category_custom_fields limit 5;
category_id | codename | custom_field_name
-------------+-----------+-------------------
1 | contact | text_6
1 | web | text_5
1 | mail | text_4
1 | phone | text_3
1 | price_usd | number_3
(5 rows)advert_custom_fields basically has id, advert_id, and then 128 column per
type (text, number, boolean, integer, date, time, timestamp).
OK, let's look at it one type at a time. You'd obviously generate the
following query via a script then save it as a view/prepared query.
SELECT advert_id, 'text_1'::text as colname, text_1 AS value
FROM advert_custom_fields
UNION ALL
SELECT advert_id, 'text_2'::text as colname, text_2 AS value
FROM advert_custom_fields
UNION ALL
...
SELECT advert_id, 'text_128'::text as colname, text_128 AS value
FROM advert_custom_fields;
Now that's going to run a set of seq-scans, so if the table's not going
to fit in RAM then you'll probably want to add a WHERE advert_id=xxx
part to each clause. Then call it once per advert-id in a loop as you
are at present. Or, you could do it in batches of e.g. 100 with a
partial index.
I'd be tempted to create a TEMP TABLE from that query, then join to the
table for the codename lookup via v_category_custom_fields. Of course,
you could do it all in the giant UNION ALL query if you wanted to.
--
Richard Huxton
Archonet Ltd
Import Notes
Reply to msg id not found: 9e4684ce0701040142l1fcc9544oaabbf58594565505@mail.gmail.com
Tom,
I've also got a customer getting this error message.
the OS is OSX 10.3 they are using plpgsql, and shared buffers is set
very low
shared_buffers = 16
Dave
On 3-Jan-07, at 10:19 AM, Tom Lane wrote:
Show quoted text
"hubert depesz lubaczewski" <depesz@gmail.com> writes:
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.Could you reduce this to a self-contained example please? Your
functions depend on a bunch of tables that you have not provided
definitions or data for ...regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
Dave Cramer <pg@fastcrypt.com> writes:
I've also got a customer getting this error message.
the OS is OSX 10.3 they are using plpgsql, and shared buffers is set
very low
shared_buffers = 16
Well, the answer to that is "if it hurts, don't do that". You couldn't
expect to process more than a very small number of very simple queries
with so few buffers. (Example: a simple INSERT involving a btree index
will require at least four concurrently pinned buffers if there's a need
for a btree page split; a join query would require at least one buffer
per table and index involved, etc.) Hubert was using a reasonably large
number of buffers, so his case sounds more like an actual bug, but I'd
call the above just pilot error.
regards, tom lane