Caching/Indexing Function Output
Hello,
I've written several functions using the SPI_ interface, some of them are to
simply ease inserting into a few tables-
ie- I wrote a function sp_host('10.0.0.1'); which returns the id of the
given
ip address from a ip_addresses table if it exists, otherwise, it inserts it,
and
returns that id. We use this to keep the logic simple when inserting new
entries
into a weblogs table-
insert into weblogs (..., host) values (..., sp_host('10.0.0.1'));
We also use this for a dozen or so other similar tables-
My question is- is there anyway, since a call to sp_host('10.0.0.1') will
always
return the same value (the primary key/sequence value from the ip_addresses
table), to cache/index that value somehow, so anytime I ever call that same
function, with the same argument, it returns a cached/indexed value?
ie- I make an initial call to sp_host('10.0.0.1'), which returns the id
1000; the
system caches this, and stores:
function=sp_host, arg='10.0.0.1', return=1000
or
key=sp_host('10.0.0.1'), value=1000
the next time I call sp_host, with the same arg, it simply returns 1000,
instead
of actually calling the function?
clearly I don't want to do this for *all* the functions I've written, but it
would
probably speed these functions up considerably?
Thanks!
Mike Pultz
mike@mrhost.ca
On Thu, Dec 23, 2004 at 01:22:42PM -0800, Mike wrote:
My question is- is there anyway, since a call to sp_host('10.0.0.1') will
always
return the same value (the primary key/sequence value from the ip_addresses
table), to cache/index that value somehow, so anytime I ever call that same
function, with the same argument, it returns a cached/indexed value?
Some procedural languages (PL/Tcl, PL/Python; also PL/Perl in 8.0)
have global data that you could use as a cache. The function would
still be called each time, but you could at least use the cache to
save the cost of a database query.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Thu, Dec 23, 2004 at 01:22:42PM -0800, Mike wrote:
My question is- is there anyway, since a call to sp_host('10.0.0.1')
will
always
return the same value (the primary key/sequence value from the
ip_addresses
table), to cache/index that value somehow, so anytime I ever call that
same
function, with the same argument, it returns a cached/indexed value?
Some procedural languages (PL/Tcl, PL/Python; also PL/Perl in 8.0)
have global data that you could use as a cache. The function would
still be called each time, but you could at least use the cache to
save the cost of a database query.
All the functions I wrote are in C-
So yeah, I guess I could have a global key/value hash that I keep up from
inside the
functions, and if the ip address exists, then return the value, otherwise
actually
execute the function.
I could probably use something like libmm if I didn't have access to global
vars, and
store them in shared memory- I was just hoping to have something that
postgres
would manage automagically for me. :)
It would also be cool if the planner could decide which to use- ie it would
be
considered cheaper to use the cache then call the function if the ip address
was
listed.
Thanks-
Mike