Thread: [OT] Flushing Postgres Function Cache
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
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
>
>
>
>
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 >
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 >> >
Raf <rafiq@joshua.dreamthought.com> writes:
> 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.
You should probably have set it to 3 not 0; what you did has disabled
*all* the triggers on that table. Is that really what you want?
> 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.
You'd be better off asking about that on the Slony mailing lists.
regards, tom lane
Hi Tom, Thanks for the feedback. On Thu, 17 May 2007, Tom Lane wrote: > Raf <rafiq@joshua.dreamthought.com> writes: >> 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. > > You should probably have set it to 3 not 0; what you did has disabled > *all* the triggers on that table. Is that really what you want? Even though there were 0 rows associated with the table's oid in pg_depend and pg_triggers ? Should this not be my indicator of the reltriggers count? For completeness, I ran a script we have which runs a bunch of alter tables, so that the schema returns to its natural state. I believe that this should fix any missing triggers, once the redundant trigger had been removed. Would you agree? Thanks, Raf
Raf <rafiq@joshua.dreamthought.com> writes:
> On Thu, 17 May 2007, Tom Lane wrote:
>> You should probably have set it to 3 not 0; what you did has disabled
>> *all* the triggers on that table. Is that really what you want?
> Even though there were 0 rows associated with the table's oid in
> pg_depend and pg_triggers ?
Oh, OK, you want zero then. I had misread it to indicate that only one
of four triggers had gone missing.
regards, tom lane
On Thu, 17 May 2007, Tom Lane wrote: > Raf <rafiq@joshua.dreamthought.com> writes: >> On Thu, 17 May 2007, Tom Lane wrote: >> Even though there were 0 rows associated with the table's oid in >> pg_depend and pg_triggers ? > > Oh, OK, you want zero then. I had misread it to indicate that only one > of four triggers had gone missing. Well, the counter was set at 4. The triggers were sloni related, and I think running Sloni removal may have removed the other triggers without updating the trigger count. Why, I don't know? I was left with the one dangling trigger, which was manually removed and I then updated the counter to represent the state of pg_deps and pg_triggers. Thanks, Raf
On Thu, May 17, 2007 at 02:02:59PM +0100, Raf wrote:
> 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.
Did you do any DDL with Slony installed, without using EXECUTE
SCRIPT?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.
--Jane Jacobs