Skip site navigation (1) Skip section navigation (2)

Re: Manual trigger removal [WAS] Flushing Postgres Function Cache

From: Raf <rafiq(at)joshua(dot)dreamthought(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
Date: 2007-05-17 12:21:21
Message-ID: 20070517131522.T69332@joshua.dreamthought.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)rafiq ~/scratch/ $ echo "select tablename from pg_tables where 
> tablename like 'pg_%'" | psql -U <user> <db> > pg_tables.txt
> rafiq(at)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
>

In response to

Responses

pgsql-admin by date

Next:From: RafDate: 2007-05-17 13:02:59
Subject: Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
Previous:From: Ritu KhetanDate: 2007-05-17 11:27:37
Subject: Error while running Postgres 8.1 as a service

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group