pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Rylander <mrylander@gmail.com>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: memcached and PostgreSQL
Date: Wed, 17 Nov 2004 11:18:02 -0500
Message-ID: <b918cf3d041117081859c17b9a@mail.gmail.com> (raw)
In-Reply-To: <87y8h06gp7.fsf@stark.xeocode.com>
References: <20041117040024.GB8838@pobox.com>
	<200411162147.54831.josh@agliodbs.com>
	<87y8h06gp7.fsf@stark.xeocode.com>

On 17 Nov 2004 03:08:20 -0500, Greg Stark <gsstark@mit.edu> wrote:
> Josh Berkus <josh@agliodbs.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 \@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@gmail.com
GPLS -- PINES Development
Database Developer



view thread (17+ messages)  latest in thread

Message-ID: <b918cf3d041117081859c17b9a@mail.gmail.com>
Permalink:  ../b918cf3d041117081859c17b9a@mail.gmail.com/
Also on:    postgresql.org/message-id/b918cf3d041117081859c17b9a@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mrylander@gmail.com, gsstark@mit.edu
  Subject: Re: memcached and PostgreSQL
  In-Reply-To: <b918cf3d041117081859c17b9a@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox