A big part of my job is to spend time figuring out how new technologies work, build on it, and then figure out new and inventive ways of merging it into new and existing systems- so I spent a lot of time “playing” around with stuff.

One thing I recently threw together, was some simple stored procedures for PostgreSQL, to give us built-in access to our memcached cluster, using the PL/PERL procedural language, and the Cache::Memcached module. None of this is rocket science, but might be useful for somebody out there that is looking to implement something similar.

First off, you need to build PostgreSQL with PERL support, and create the plperlu (un-trusted) language in our database; you need to use the “un-trusted” language, as we need to “use” the Cache::Memcached module;

postgres=# create language plperlu ;
CREATE LANGUAGE

Then you need to install the Cache::Memcached PERL module; I’ll assume you know how to do that; you can either install it through CPAN, or get it from the cpan.org site.

You also need at least one instance of memcached running somewhere; it doesn’t have to be on your local network, it just needs to be accessible by your database.

Then create the following procedures; I’ve only listed the “set” and “get” procedures, but you could easily follow the Cache::Memcached module man page, and create a “replace” and “delete” (and any other) procedures you needed.

So first the “set” procedure:

create or replace function memcache_set(_key text, _value bytea) returns int as $$

        use Cache::Memcached;

        my ($_key, $_value) = @_;

        $m = new Cache::Memcached {
                'debug'	=> 0
        };

        my @list = ("127.0.0.1:9996", "127.0.0.1:9997");
        my $servers = \@list;

        $m->set_servers($servers);
        $m->enable_compress(0);

        if ($m->set($_key, $_value))
        {
                return 0;
        } else
        {
                return 1;
        }

$$ language plperlu;

And then a “get” procedure:

create or replace function memcache_get(_key text) returns bytea as $$

        use Cache::Memcached;       

        my ($_key) = @_;

        $m = new Cache::Memcached {
                'debug'	=> 0
        };

	my @list = ("127.0.0.1:9996", "127.0.0.1:9997");
        my $servers = \@list;       

        $m->set_servers($servers);
        $m->enable_compress(0);

        my $val = $m->get($_key);
        if (defined($val))
        {
                return $val;
        } else
        {
                return undef;
        }

$$ language plperlu;

Now, in this case, I used a list of two different servers, both running on localhost, one on port 9996 and the other on port 9997; these servers could be anywhere, and you could only list one- but a really important thing to remember: if you do list more than one server, make sure you always list it in the same order, between all your procedures and all applications that may use this same data.

memcached calculates which server to store the data on, based on the order of this list; so you’ll get un-expected results if you list these servers differently between your applications.

A Simple Test

Now that you have the functions defined, you can use them to do some really interesting caching, directly from your database. Starting off with a simple test:

postgres=# select memcache_set('foo', 'bar');
 memcache_set 
---------------
               0
(1 row)

Then do a “get” to retrieve the data

postgres=# select memcache_get('foo');
 memcache_get
---------------
 bar
(1 row)

Database Session Handler

Now, lets say you had a database-backed session handler for your website; say maybe a PHP site, using something like this, which simply uses the PHP session_set_save_handler() method. Assume a simple session table like this:

create table sessions (
	session_id char(32) not null,
	data bytea not null default ''
);

“session_id” is the PHP session id (an MD5 string in this case), and “data” is the serialized contents of the PHP session.

Then add two rules- one for insert and one for update; this triggers the new memcache_set() procedure, so that new session data is always pushed out to your memcached servers

create rule session_insert_rule as on insert to sessions
	do also select memcache_set(NEW.session_id::text, NEW.data);

create rule session_update_rule as on update to sessions
	do also select memcache_set(NEW.session_id::text, NEW.data);

Testing this is simple:

postgres=# insert into sessions values ('5cc27b0285c9d2e6dc4125456d308548', 
     'This would be the session data');
INSERT 0 1

postgres=# select memcache_get('5cc27b0285c9d2e6dc4125456d308548');
              memcache_get
--------------------------------
 This would be the session data
(1 row)

And then subsequent updates

postgres=# update sessions set data = 'This is the new data' where
   session_id = '5cc27b0285c9d2e6dc4125456d308548';
UPDATE 1

postgres=# select memcache_get('5cc27b0285c9d2e6dc4125456d308548');
   memcache_get
-------------------
 This is the new data
(1 row)

Now just make your PHP session handler (or whatever) checks memcached first, before selecting from the sessions table; if you build it right, and set your expiration times correctly, you should be able to build a system that (almost) *never* reads this data from the database, as the data is always available from memcached.