X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6CE97D1B430 for ; Fri, 2 Jan 2004 03:26:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88830-04 for ; Thu, 1 Jan 2004 23:25:55 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B5276D1B47D for ; Thu, 1 Jan 2004 23:25:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i023PK19002715; Thu, 1 Jan 2004 22:25:20 -0500 (EST) To: Jan Wieck Cc: ivan , pgsql-hackers@postgresql.org Subject: Re: cache in plpgsql In-reply-to: <3FF4DC86.1000400@Yahoo.com> References: <3FF2C608.3090906@Yahoo.com> <3FF2CFE0.70804@Yahoo.com> <3FF4DC86.1000400@Yahoo.com> Comments: In-reply-to Jan Wieck message dated "Thu, 01 Jan 2004 21:50:46 -0500" Date: Thu, 01 Jan 2004 22:25:20 -0500 Message-ID: <2714.1073013920@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/10 X-Sequence-Number: 48734 Jan Wieck writes: > But can you tell me how to actually check if a saved SPI plan > references that particular catalog object or not? This is certainly doable in principle. recordDependencyOnExpr() contains much of the logic that would be needed -- it would need to be modified so that the dependency info can just be put in memory and not stored into pg_depend, but that's surely not hard. (Note that to use it directly, we'd want to examine the parsed querytree not the plan tree, but that isn't a problem AFAICS.) Whether it's *practical* is another question. Cache inval events happen often enough that speed of response to 'em is an issue. Maybe we could use a hashtable of dependencies to avoid expensive searches for cached plans that must be invalidated. Another little problem is that plpgsql doesn't really have any mechanism for invalidating cached stuff at all; it will leak memory like there's no tomorrow if we start dropping cached subplans. plpgsql needs to be rewritten so that everything it allocates lives in per-function memory contexts that can be dropped when an invalidation happens. As far as I can see a dependency-based solution is possible. It's just a Small Matter Of Programming. http://www.catb.org/~esr/jargon/html/S/SMOP.html regards, tom lane