pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Raf <rafiq@joshua.dreamthought.com>
To: pgsql-admin@postgresql.org
Subject: Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
Date: Thu, 17 May 2007 14:02:59 +0100 (BST)
Message-ID: <20070517135952.F69332@joshua.dreamthought.com> (raw)
In-Reply-To: <20070517131522.T69332@joshua.dreamthought.com>
References: <20070517092853.R69332@joshua.dreamthought.com>
	<20070517122322.V69332@joshua.dreamthought.com>
	<20070517131522.T69332@joshua.dreamthought.com>

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_<tables> 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 "<mytablename>"
>
> 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 <user> <db> > pg_tables.txt
>> rafiq@rafiq ~/scratch/ $ for i in `cat pg_tables.txt`; do echo "==$i=="; 
>> echo "select * from $i"| psql -U <user> <db> | 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 <oid>."
>>> 
>>> 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
>> 
>



view thread (7+ messages)  latest in thread

Message-ID: <20070517135952.F69332@joshua.dreamthought.com>
Permalink:  ../20070517135952.F69332@joshua.dreamthought.com/
Also on:    postgresql.org/message-id/20070517135952.F69332@joshua.dreamthought.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: rafiq@joshua.dreamthought.com
  Subject: Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
  In-Reply-To: <20070517135952.F69332@joshua.dreamthought.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox