Re: memcached and PostgreSQL

On 17 Nov 2004 03:08:20 -0500, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
> > So memcached becomes a very good place to stick data that's read often but not
> > updated often, or alternately data that changes often but is disposable. An
> > example of the former is a user+ACL list; and example of the latter is web
> > session information ... or simple materialized views.
>
> I would like very much to use something like memcached for a materialized view
> I have. The problem is that I have to join it against other tables.
>
> I've thought about providing a SRF in postgres to read records out of
> memcached but I'm unclear it would it really help at all.
>
> Has anyone tried anything like this?

I haven't tried it yet, but I plan too. An intersting case might be
to use plperlu to interface with memcached and store hashes in the
cache via some external process, like a CGI script. Then just define
a TYPE for the perl SRF to return, and store the data as an array of
hashes with keys matching the TYPE.

A (perhaps useless) example could then be something like:

CREATE TYPE user_info AS ( sessionid TEXT, userid INT, lastaccess
TIMESTAMP, lastrequest TEXT);

CREATE FUNCTION get_user_info_by_session ( TEXT) RETURNS SETOF user_info AS $$
use Cache::Memcached;

my $session = shift;

my $c = $_SHARED{memcached} || Cache::Memcached->new( {servers =>
'127.0.0.1:1111'} );

my $user_info = $m->get('web_access_list');

# $user_info looks like
# [ {userid => 5, lastrequest => 'http://...', lastaccess => localtime(),
# sessionid => '123456789'}, { ...} ]
# and is stored by a CGI.

@info = grep {$$_{sessionid} eq $session} @$user_info;

return \(at)info;
$$ LANGUAGE 'plperlu';

SELECT u.username, f.lastrequest FROM users u,
get_user_info_by_session('123456789') WHERE f.userid = u.userid;

Any thoughts?

>
> --
> greg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer