HOWTO caching data across function calls: temporary tables, cursor?

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: HOWTO caching data across function calls: temporary tables, cursor?
Date: 2008-04-01 08:57:17
Message-ID: 20080401105717.52da3d1d@webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I made a similar question but maybe it was not that clear.

I've a large table (items) linked with other tables (attributes).

Some product ends into a basket.

create table items(
item_id serial primary key,
attributes...
);

create table item_attributes(
item_id int references items (item_id)
attributes...
);

create table baskets(
basket_id serial primary key,
...other stuff
);
create table basket_items(
item_id int references items (item_id),
basket_id int references baskets (basket_id),
...
);

I've a bunch of functions that operates on the basket (a smaller list
of products with their attributes).

So many functions ends up in repeating over and over a select similar
to:

select [list of columns] from baskets b
join basket_items bi on b.basket_=bi.basket_id
join items i on i.item_id=bi.item_id
join item_attributes a a.item_id=i.item_id
where b.basket_id=$1

It would be nice if I could avoid to execute this query over and over.
I'd have to find a way to pass this data across functions.

One way would be to put this data in a temporary table, but many
things are unclear to me.
I still have to find a way to reference these tables across functions
(there will be different basket_id, and each transaction should see
the same temp table and not "steal" the one of other transactions).
I've to take care of name clash and visibility.
I need to take care of garbage collection at the right time.
I've no idea of the performance gain.
Caching of queries in function (relation with OID ##### does not
exist) and all the above make the use of temp tables a bit
overwhelming.

It seems that another way would be to use cursors... but I haven't
been able to find any example.

I think this is a common problem but I can't find general guidelines.

I'm on 8.1

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-04-01 09:26:02 Re: Using tables in other PostGreSQL database
Previous Message Tomasz Ostrowski 2008-04-01 08:48:07 Re: simple update queries take a long time - postgres 8.3.1