X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F1F56DA7FE for ; Thu, 20 Oct 2005 11:07:30 -0300 (ADT) 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 14841-04 for ; Thu, 20 Oct 2005 14:07:26 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 19428D99D8 for ; Thu, 20 Oct 2005 11:07:29 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 22C6E31058; Thu, 20 Oct 2005 16:07:32 +0200 (MET DST) From: Kuba Ouhrabka X-Newsgroups: pgsql.performance Subject: cached plans in plpgsql Date: Thu, 20 Oct 2005 16:07:22 +0200 Organization: Hub.Org Networking Services Lines: 20 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: cs, en-us, en To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200510/283 X-Sequence-Number: 15051 Hi, is there an easy way to flush all cached query plans in pl/pgsql functions? I've long running sessions where data are changing and the plans become inaccurate after a while. I can imagine something like recreating all pl/pgsql functions. I can recreate them from sql source files but I'd prefer recreating them inside the database without accessing files outside. I can think only of one way - reconstructing function source code from pg_proc and EXECUTEing it. But it's not the cleanest solution (there isn't saved the actual source code anywhere so there could be problems with quoting etc.). Can you see any other possibility? How do you solve this problem? [And yes, I don't want to re-connect...] Thanks, Kuba