Thread: Re: [Slony1-general] Re: dangling lock information?
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. - DAP -----Original Message----- From: slony1-general-bounces@gborg.postgresql.org [mailto:slony1-general-bounces@gborg.postgresql.org] On Behalf Of Hannu Krosing Sent: Tuesday, August 30, 2005 7:28 AM To: Andreas Pflug Cc: slony1-general@gborg.postgresql.org; PostgreSQL-development Subject: [Slony1-general] Re: [HACKERS] dangling lock information? On E, 2005-08-29 at 13:09 +0200, Andreas Pflug wrote: > Hannu Krosing wrote: > > >On P, 2005-08-28 at 22:23 +0200, Andreas Pflug wrote: > > > > > >>I'm currently testing pgAdmin support for slony, on pgsql CVS HEAD, > >>and encounter strange problems from time to time. > >>After dropping and recreating the slony schema, all changes > >>committed and all backends in <IDLE> state, I'm getting "relation > >>with OID xxx does not exist" when I'm trying to add a path. > >>This seems to be triggered inside slony functions when a > >> LOCK _test.pg_config IN EXCLUSIVE MODE is performed. > >>The problem is gone as soon as I close the connection I've been > >>using for prior schema changes, and use a fresh connection. > >> > >>Does this description ring a bell for somebody? > >> > >> > > > >seems like the usual "pl/pgsql caches query plans and relation > >referenced inside the cached plan is gone" thing > > > > > Kind of, but the complete schema including procedures was dropped, so > apparently after recreation the old plans were reused?!? In that case this should probably be asked at slony list. Added to CC. -- Hannu Krosing <hannu@skype.net> _______________________________________________ Slony1-general mailing list Slony1-general@gborg.postgresql.org http://gborg.postgresql.org/mailman/listinfo/slony1-general
dparker@tazznetworks.com ("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.
Chris Browne <cbbrowne@acm.org> 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
On Tue, Aug 30, 2005 at 12:45:18PM -0400, Chris Browne wrote:
> dparker@tazznetworks.com ("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 <alvherre[]alvh.no-ip.org> Architect, www.EnterpriseDB.com
"Si quieres ser creativo, aprende el arte de perder el tiempo"
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
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
alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
> On Tue, Aug 30, 2005 at 12:45:18PM -0400, Chris Browne wrote:
>> dparker@tazznetworks.com ("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