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 E6DC1329F03 for ; Mon, 27 Sep 2004 18:37:57 +0100 (BST) 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 67203-07 for ; Mon, 27 Sep 2004 17:37:52 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9A126329D15 for ; Mon, 27 Sep 2004 18:37:51 +0100 (BST) Received: from [134.22.69.205] (dyn-69-205.tor.dsl.tht.net [134.22.69.205]) by tht.net (Postfix) with ESMTP id 0F03A76AE0; Mon, 27 Sep 2004 13:37:51 -0400 (EDT) Subject: Re: Caching of Queries From: Rod Taylor To: matt@ymogen.net Cc: 'Aaron Werman' , 'Scott Kirkwood' , Postgresql Performance In-Reply-To: <00e801c4a4b6$5526dd30$8300a8c0@solent> References: <00e801c4a4b6$5526dd30$8300a8c0@solent> Content-Type: text/plain Message-Id: <1096306670.40463.173.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 27 Sep 2004 13:37:51 -0400 Content-Transfer-Encoding: 7bit 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: 200409/286 X-Sequence-Number: 8414 > So yeah, here's another vote for this feature. It doesn't even need to > happen automagically to be honest, so long as it's really simple for the > client to turn on (preferably per-statement or per-table). It might be easiest to shove the caching logic into pgpool instead. Create an extension of EXPLAIN which returns data in an easy to understand format for computers so that pgpool can retrieve information such as a list of tables involved, Extend LISTEN to be able to listen for a SELECT on a table -- replacement for dynamically adding triggers to send a notify on inserts, updates, deletes. Create some kind of generic LISTEN for structural changes. I know SLONY could make use of triggers on ALTER TABLE, and friends as well. When pg_pool is told to cache a query, it can get a table list and monitor for changes. When it gets changes, simply dumps the cache.