X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 789A99DC831 for ; Thu, 16 Feb 2006 13:14:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34151-10 for ; Thu, 16 Feb 2006 13:14:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from prod01.jerrysievers.com (adsl-64-118-249-70.netrox.net [64.118.249.70]) by postgresql.org (Postfix) with ESMTP id A374E9DC82C for ; Thu, 16 Feb 2006 13:14:30 -0400 (AST) Received: (from gsievers@localhost) by prod01.jerrysievers.com (8.11.0/8.11.0) id k1GHETK13724; Thu, 16 Feb 2006 12:14:29 -0500 To: pgsql-admin@postgresql.org Subject: Re: Dropping of indexes with cached PL query plans References: <28733.1140106370@sss.pgh.pa.us> From: Jerry Sievers Date: 16 Feb 2006 12:14:29 -0500 In-Reply-To: Tom Lane's message of "Thu, 16 Feb 2006 11:12:50 -0500" Message-ID: Lines: 51 X-Mailer: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200602/198 X-Sequence-Number: 20619 Tom Lane writes: > Jerry Sievers writes: > > Any of these connections that may have previously executed PL > > functions which planned an index lookup are now going to fault if same > > index goes away. > > > Had wondered if a postmaster 'reload' would elicit a recompiling of > > func query plans (did *not* see this in the docs but was hopeful...). > > Starting a fresh session (database connection) is sufficient, you need > not bounce the postmaster as such. Yes Tom, thanks. Note that I am referring to the 'reload' operation, that is; simple HUPping of postmaster. But of course, this doesn't cause cached query plans to be recalc'd anyway, as you point out below (at least, not presently). I wonder if you'd comment on the (apparently) useful workaround of doing a CREATE or REPLACE FUNCTION on the PL funcs? This does work, on 8.0 systems, though perhaps isn't a robust solution for some other reason. I do understand also that such a solution will *not* solve the same problem as it relates to Postgres prepared statements. As I understant it, only the active session could deallocate and re-prepare such a statement. > We are looking at making replanning happen automatically after a schema > change; I'm hopeful that that gets done for 8.2. That would be excellent IMO. Failing that, if systemic replanning is deemed too heavy to initiate on any DDL change; something like an administrator forced re-cacheing might suffice. Eg; pg_ctl recache (SIGUSR1 or somesuch tells running sessions to replan any cached queries). At any rate, great work past and present. Thanks! -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobile http://www.JerrySievers.com/