Error No Unpinned Buffers Available
buildfarm-members pgsql-cluster-hackers pgsql-committers pgsql-hackers pgsql-rrreviewers pgsql-translators pgsql-www Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Re: Getting "ERROR: no unpinned buffers available" on HEAD, should I investigate? From: Alvaro Herrera
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News "no unpinned buffers available" ? why? (hstore and plperl involved) From: "hubert depesz lubaczewski"
♦ Locked 13 messages hubert depesz lubaczewski Reply | Threaded Open this post in threaded view ♦ http://postgresql.nabble.com/GENERAL-quot-no-unpinned-buffers-available-quot-why-hstore-and-plperl-involved-td1873527.html ♦ | Report Content as Inappropriate ♦ ♦ [GENERAL] "no http://osdir.com/ml/pgsql-hackers/2010-12/msg01259.html 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 error no 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'} } = error no unpinned $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*1
I'm getting quite a lot of "no unpinned buffers available" errors. Increasing shared_buffers from 32MB (the default) to 64MB makes the errors go away, as does setting fsync=off. I'm not sure how many buffers a particular backend can hold at any time. I'd have though it'd be something like one per relation and scan-in-progress, with indices counting as relations. In that case, with max_connections=100, my tests shouldn't pin more than a couple of hundred buffers at a time. That, however, amounts to about 1MB or so of pinned buffers, so it shouldn't break with shared_buffers=32MB. I'm also confused by fsync=off making a difference. That should make writing dirty buffers slower, but would it affect the number of buffers pinned? In short, I'm wondering whether I might have hit a bug, or if I should just increase shared_buffers and move on. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Thread at a glance: Previous Message by Date: Re: [HACKERS] ALTER TABLE ... REPLACE WITH On Tue, 2010-12-14 at 16:19 -0800, Josh Berkus wrote: > Without some means of doing a clone of the table in a single command, > you've eliminated half the scripting work, but not helped at all with > the other half. I'm not trying to eliminate scripting work, I'm trying to minimise the lock window with a reliable and smooth atomic switcheroo. > Actually, you know what would be ideal? > > REPLACE TABLE old_table WITH SELECT ... > > Give it some thought ... I have; the above would hold the lock window open while the SELECT runs and that is explicitly something we are trying to avoid. Good creative input though, thank you. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Next Message by Date: Re: [HACKERS] hstores in pl/python On Dec 13, 2010, at 6:16 PM, Tom Lane wrote: > how do you identify which type OID is really hstore? How about an identification field on pg_type? CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore'; -- Where the "identifier" is an arbitrary string. Type information can be looked up by the PL, and the I/O functions can be dynamically resolved using the identifier. -- Sent via pgsql-hackers mailing list (pgsql-hackers@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Previous Message by Thread: [HACKERS] Segfault related to pg_authid when ru