Re: [HACKERS] [Slony1-general] Re: dangling lock information?
[EMAIL PROTECTED] (Alvaro Herrera) writes:
> On Tue, Aug 30, 2005 at 12:45:18PM -0400, Chris Browne wrote:
>> [EMAIL PROTECTED] ("David Parker") writes:
>> > The slony log trigger saves execution plans, so any given
>> > connection that has been used with a slony schema installed will
>> > have cached OIDs referring to the sl_log_1 table. When you drop
>> > the schema, those OIDs obviously go away. When you re-create the
>> > schema, and try to use the old connection, it still has the old
>> > plan cached in it, so the OIDs in the plan are out of sync with
>> > what actually exists in the database.
>> >
>> > This is the behavior I've observed in our environment,
>> > anyway. The problem always shows up when slony is RE-installed
>> > under an outstanding connection.
>>
>> I have observed much the same behaviour...
>>
>> It would be really useful to have some guidance as to how to
>> resolve this.
>>
>> What is needed is to invalidate the cached execution plans.
>
> The simplest way to do that is to disconnect the client, and start a
> fresh session.
I'm keen on a "simplest way" that doesn't essentially involve having
to restart the application...
>> Unfortunately, it's not at all obvious how to accomplish that :-(.
>
> I don't think it can be easily done with the current code. This is
> plpgsql code, right? There are some ways to cause recompilation for
> those, at least on the 8.1 code I'm looking at.
No, the troublesome parts are in C/SPI code.
If it's something Neil Conway hasn't quite figured out how to handle
yet, I don't feel so bad that I can't imagine a way to do it... :-)
--
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/spiritual.html
A cool feature of OOP is that the simplest examples are 500 lines.
-- Peter Sestoft
---(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
Re: [HACKERS] [Slony1-general] Re: dangling lock information?
Alvaro Herrera wrote: Unfortunately, it's not at all obvious how to accomplish that :-(. I don't think it can be easily done with the current code. This is plpgsql code, right? There are some ways to cause recompilation for those, at least on the 8.1 code I'm looking at. Well at least when a procedure is dropped, its cached plans could be dropped as well (apparently the cache plan is located trough some kind of hash, not the pg_proc.oid?). I do understand that the usual case, a table oid changed while cached inside a procedure isn't easily detectable because it would require dependency information generated from procedure's source. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Slony1-general] Re: dangling lock information?
Tom Lane wrote: Yeah. This is not really Slony's fault --- we need a general solution to that in the backend. I think Neil was working on it, but I dunno how far along he is. Yeah, I had wanted to get this into 8.1, but I couldn't find time. I still plan to work on it for 8.2, unless someone beats me to it. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Slony1-general] Re: dangling lock information?
On Tue, Aug 30, 2005 at 12:45:18PM -0400, Chris Browne wrote:
> [EMAIL PROTECTED] ("David Parker") writes:
> > The slony log trigger saves execution plans, so any given connection
> > that has been used with a slony schema installed will have cached OIDs
> > referring to the sl_log_1 table. When you drop the schema, those OIDs
> > obviously go away. When you re-create the schema, and try to use the old
> > connection, it still has the old plan cached in it, so the OIDs in the
> > plan are out of sync with what actually exists in the database.
> >
> > This is the behavior I've observed in our environment, anyway. The
> > problem always shows up when slony is RE-installed under an outstanding
> > connection.
>
> I have observed much the same behaviour...
>
> It would be really useful to have some guidance as to how to resolve
> this.
>
> What is needed is to invalidate the cached execution plans.
The simplest way to do that is to disconnect the client, and start a
fresh session.
> Unfortunately, it's not at all obvious how to accomplish that :-(.
I don't think it can be easily done with the current code. This is
plpgsql code, right? There are some ways to cause recompilation for
those, at least on the 8.1 code I'm looking at.
--
Alvaro Herrera Architect, www.EnterpriseDB.com
"Si quieres ser creativo, aprende el arte de perder el tiempo"
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [Slony1-general] Re: dangling lock information?
Chris Browne <[EMAIL PROTECTED]> writes: > What is needed is to invalidate the cached execution plans. Yeah. This is not really Slony's fault --- we need a general solution to that in the backend. I think Neil was working on it, but I dunno how far along he is. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Slony1-general] Re: dangling lock information?
[EMAIL PROTECTED] ("David Parker") writes:
> The slony log trigger saves execution plans, so any given connection
> that has been used with a slony schema installed will have cached OIDs
> referring to the sl_log_1 table. When you drop the schema, those OIDs
> obviously go away. When you re-create the schema, and try to use the old
> connection, it still has the old plan cached in it, so the OIDs in the
> plan are out of sync with what actually exists in the database.
>
> This is the behavior I've observed in our environment, anyway. The
> problem always shows up when slony is RE-installed under an outstanding
> connection.
I have observed much the same behaviour...
It would be really useful to have some guidance as to how to resolve
this.
What is needed is to invalidate the cached execution plans.
Unfortunately, it's not at all obvious how to accomplish that :-(.
Alas, any time I touch the SPI code in other than relatively trivial
ways, it falls over and croaks :-(.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/linuxdistributions.html
One good turn gets most of the blankets.
---(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
