Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B42829FB5D3 for ; Thu, 17 May 2007 10:00:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 59786-04 for ; Thu, 17 May 2007 10:00:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from joshua.dreamthought.com (joshua.dreamthought.com [193.201.200.96]) by postgresql.org (Postfix) with ESMTP id 471E29FB545 for ; Thu, 17 May 2007 10:00:43 -0300 (ADT) Received: from rafiq (helo=localhost) by joshua.dreamthought.com with local-esmtp (Exim 4.66 ()) (envelope-from ) id 1HofdD-000Idm-MP for pgsql-admin@postgresql.org; Thu, 17 May 2007 14:02:59 +0100 Date: Thu, 17 May 2007 14:02:59 +0100 (BST) From: Raf To: pgsql-admin@postgresql.org Subject: Re: Manual trigger removal [WAS] Flushing Postgres Function Cache In-Reply-To: <20070517131522.T69332@joshua.dreamthought.com> Message-ID: <20070517135952.F69332@joshua.dreamthought.com> References: <20070517092853.R69332@joshua.dreamthought.com> <20070517122322.V69332@joshua.dreamthought.com> <20070517131522.T69332@joshua.dreamthought.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-SA-Exim-Connect-IP: X-SA-Exim-Mail-From: rafiq@joshua.dreamthought.com X-SA-Exim-Scanned: No (on joshua.dreamthought.com); SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/160 X-Sequence-Number: 25480 Resolved, however feedback regarding better ways of doing this would be appreciated. The final step was the decrement the reltriggers counter for the parent table's pg_class entry to 0. This appears to have resolved the problem. I'm curious as to whether the problem may have resulted from slony corruptting internal pg_ or postgres not correctly respecting the integrity of drop-trigger type transactions and their respective dependencies. On Thu, 17 May 2007, Raf wrote: > Please read from bottom up. I've proceeded further with this investigation > and have removed slony. In spite of this the trigger in pg_trigger and > pg_dep remained. I then removed both related records in pg_depend and > pg_trigger. > > If I look in pg_class, it shows that there are 4 triggers on the table which > previously owened the trigger. I no longer have any reference to this oid in > pg_depend or pg_trigger. > > Yet, after restarting postgres and all client connections, I now get the > error: > > ERROR: 1 trigger record(s) not found for relation "" > > As mentioned, I've checked the for relating oid's in pg_depend and > pg_trigger. Nadda. > > If anyone else has manually done something similar before, I would be > grateful for some guidence. > > Many Thanks. > > Raf > > > > > On Thu, 17 May 2007, Raf wrote: > >> Update on the status of my findings relating to the bug below: >> >> I did: >> >> rafiq@rafiq ~/scratch/ $ echo "select tablename from pg_tables where >> tablename like 'pg_%'" | psql -U > pg_tables.txt >> rafiq@rafiq ~/scratch/ $ for i in `cat pg_tables.txt`; do echo "==$i=="; >> echo "select * from $i"| psql -U | grep 54881296; # the-oid; >> done; >> >> And got: >> >> ... >> ==pg_trigger== >> 273090 | _ipt_replication_logtrigger_114 | 54881296 | 29 | t | f | | >> 0 | f | f | 3 | | >> _ipt_replication\000114\000vvvvvvkkkkvvv\000 >> ... >> ==pg_depend== >> 16412 | 54881725 | 0 | 1255 | 54881296 | 0 | n >> ... >> >> >> Which to me looks very slonny related. It seems like the OID related to a >> slonny trigger. >> >> Has anyone seen this before, with regard to slonny? I'm thinking of >> removing and readding slonny associations. Is this best way forward? >> >> Cheers, >> >> Raf >> >> >> On Thu, 17 May 2007, Raf wrote: >> >>> Greetings, >>> >>> In the face of shabby google results, I hoped that someone hear might know >>> something about the postgres function cache. We have a server which >>> reports an error of the form "cache lookup failed for function ." >>> >>> Initially, I'd hoped to resolve this without a restart (which didn't do >>> the trick either), and had been searching for some way to clear postgres' >>> internal function cache - I think it stores plans/hints/compiled-version >>> relating to stored proc's? >>> >>> I tried to select the reported oid out of pg_proc, which resulted in an >>> empty set. >>> >>> I then went on to explicitly drop the function and recreate it. This was >>> interspersed with numerous postgres restarts and dropping of all client >>> connections. In spite of this I still get the same error from our client >>> which still reports the same OID; which doesn't exist in pg_proc. >>> >>> In any case, I can't seem to find any documentation which tells me how to >>> clear this cache. >>> >>> Versions: >>> >>> pg: postgresql v 8.0.1-r4 >>> os: Gentoo Linux/Kernel 2.6.12.5 #1 >>> >>> Any help would be of value? >>> >>> Cheers, >>> >>> Raf >>> >>> >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match >> >