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