pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Manual trigger removal [WAS] Flushing Postgres Function Cache
7+ messages / 3 participants
[nested] [flat]
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 12:21 Raf <rafiq@joshua.dreamthought.com>
0 siblings, 1 reply; 7+ messages in thread
From: Raf @ 2007-05-17 12:21 UTC (permalink / raw)
To: 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@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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 13:02 Raf <rafiq@joshua.dreamthought.com>
parent: Raf <rafiq@joshua.dreamthought.com>
0 siblings, 2 replies; 7+ messages in thread
From: Raf @ 2007-05-17 13:02 UTC (permalink / raw)
To: pgsql-admin
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
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 13:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Raf <rafiq@joshua.dreamthought.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-05-17 13:42 UTC (permalink / raw)
To: Raf <rafiq@joshua.dreamthought.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 13:54 Raf <rafiq@joshua.dreamthought.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Raf @ 2007-05-17 13:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 14:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Raf <rafiq@joshua.dreamthought.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-05-17 14:11 UTC (permalink / raw)
To: Raf <rafiq@joshua.dreamthought.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 14:29 Raf <rafiq@joshua.dreamthought.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Raf @ 2007-05-17 14:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual trigger removal [WAS] Flushing Postgres Function Cache
@ 2007-05-17 15:27 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Raf <rafiq@joshua.dreamthought.com>
1 sibling, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2007-05-17 15:27 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-05-17 15:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-17 12:21 Re: Manual trigger removal [WAS] Flushing Postgres Function Cache Raf <rafiq@joshua.dreamthought.com>
2007-05-17 13:02 ` Raf <rafiq@joshua.dreamthought.com>
2007-05-17 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-17 13:54 ` Raf <rafiq@joshua.dreamthought.com>
2007-05-17 14:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-17 14:29 ` Raf <rafiq@joshua.dreamthought.com>
2007-05-17 15:27 ` Andrew Sullivan <ajs@crankycanuck.ca>
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