query result set caching

[Martin Sarsale]

Dear All:

Im looking for solutions (Free Software is better) to do query result
caching.
Thanks to the people from #postgresql I know that postgres doesn't do
that by himself and the solution should be some kind of middle ware.

This is our problem: we are executing the same set of SELECT SP/queries
hundreds of times, without updating the DB. We would like to cache the
result of this SP/queries so the next time somebody runs one of them,
the results come from the cache instead of the db.

We would like to use an already developed and tested solution but if we
can't find anything that fits our needs, we can develop our own solution
(playing w/ PHP and PG's LISTEN/NOTIFY)

Thanks in advance

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQBBEUGzhruVGj+ub3MRAsxMAJ91DWjIPBgu22uO9AzgWV UFxCpt8wCgsigd
WlVLDcLkYp1mV2y///zhSBw=
=4NXw
-----END PGP SIGNATURE-----

[Bill Moran]

Martin Sarsale <ma****@emepe3.net> wrote:

Dear All:

Im looking for solutions (Free Software is better) to do query result
caching.
Thanks to the people from #postgresql I know that postgres doesn't do
that by himself and the solution should be some kind of middle ware.

This is our problem: we are executing the same set of SELECT SP/queries
hundreds of times, without updating the DB. We would like to cache the
result of this SP/queries so the next time somebody runs one of them,
the results come from the cache instead of the db.

We would like to use an already developed and tested solution but if we
can't find anything that fits our needs, we can develop our own solution
(playing w/ PHP and PG's LISTEN/NOTIFY)

There was a lot of talk about "materialized views" not too many months ago
on one of the PostgreSQL lists. I believe these techniques can accomplish
what you're looking for. An archive or google search should turn up
lots of info.

HTH.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Steve Crawford]

On Wednesday 04 August 2004 1:06 pm, Martin Sarsale wrote:

Dear All:

Im looking for solutions (Free Software is better) to do query
result caching.
Thanks to the people from #postgresql I know that postgres doesn't
do that by himself and the solution should be some kind of middle
ware.

This is our problem: we are executing the same set of SELECT
SP/queries hundreds of times, without updating the DB. We would
like to cache the result of this SP/queries so the next time
somebody runs one of them, the results come from the cache instead
of the db.

Possible kludge that may or may not help in your situation:

Can you create a table that is the result of your query and have the
front-end select from that? You could even create a trigger to update
the result table whenever the source table(s) are altered.

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org