X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A7C1D3A3D61 for ; Wed, 17 Nov 2004 16:18:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21619-07 for ; Wed, 17 Nov 2004 16:18:04 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.205]) by svr1.postgresql.org (Postfix) with ESMTP id CDF743A4179 for ; Wed, 17 Nov 2004 16:18:02 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a36so901829rnf for ; Wed, 17 Nov 2004 08:18:02 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=dMrsVE3hgX0D4/YrHdVroYpORmqpNw6urvWFc7tclbeBncguuLwvEhiSuY8gsPbEVpYPB6ZEGLXEqpInSljnIGKZbKeR56QXxOSRpL70qWi1ixCBwf5nTIS00hE5NmFX+BqQJ9sHZv2HSxsxPqZ13FcMag97f38SdzNNTbl75jo= Received: by 10.38.82.27 with SMTP id f27mr535630rnb; Wed, 17 Nov 2004 08:18:02 -0800 (PST) Received: by 10.38.126.17 with HTTP; Wed, 17 Nov 2004 08:18:02 -0800 (PST) Message-ID: Date: Wed, 17 Nov 2004 11:18:02 -0500 From: Mike Rylander Reply-To: Mike Rylander To: Greg Stark Subject: Re: memcached and PostgreSQL Cc: pgsql-performance@postgresql.org In-Reply-To: <87y8h06gp7.fsf@stark.xeocode.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <20041117040024.GB8838@pobox.com> <200411162147.54831.josh@agliodbs.com> <87y8h06gp7.fsf@stark.xeocode.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/254 X-Sequence-Number: 9208 On 17 Nov 2004 03:08:20 -0500, Greg Stark wrote: > Josh Berkus 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