Обсуждение: "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:
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:
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:
--
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:
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

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:

> "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