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

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