Grokbase › Groups › PostgreSQL › pgsql-hackers › August 2005
FAQ
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?

Regards,
Andreas

Search Discussions

  • Hannu Krosing at Aug 29, 2005 at 10:06 am ⇧

    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

    --
    Hannu Krosing <[email protected]>
  • Andreas Pflug at Aug 29, 2005 at 11:10 am ⇧

    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?!?

    Regards,
    Andreas
  • Hannu Krosing at Aug 30, 2005 at 11:27 am ⇧

    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 <[email protected]>
  • David Parker at Aug 30, 2005 at 12:12 pm ⇧
    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: [email protected]
    On Behalf Of Hannu
    Krosing
    Sent: Tuesday, August 30, 2005 7:28 AM
    To: Andreas Pflug
    Cc: [email protected]; 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 <[email protected]>

    _______________________________________________
    Slony1-general mailing list
    [email protected]
    http://gborg.postgresql.org/mailman/listinfo/slony1-general
  • Chris Browne at Aug 30, 2005 at 5:04 pm ⇧

    "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.
  • Tom Lane at Aug 30, 2005 at 6:07 pm ⇧

    Chris Browne 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
  • Neil Conway at Aug 30, 2005 at 6:55 pm ⇧

    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 at Aug 30, 2005 at 6:13 pm ⇧

    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 <alvherre[]alvh.no-ip.org> Architect, www.EnterpriseDB.com
    "Si quieres ser creativo, aprende el arte de perder el tiempo"
  • Andreas Pflug at Aug 30, 2005 at 9:36 pm ⇧

    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
  • Chris Browne at Aug 30, 2005 at 11:04 pm ⇧

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 28, '05 at 8:23p
activeAug 30, '05 at 11:04p
posts11
users7
websitepostgresql.org...
irc#postgresql

7 users in discussion

Andreas Pflug: 3 posts Hannu Krosing: 2 posts Chris Browne: 2 posts Alvaro Herrera: 1 post David Parker: 1 post Neil Conway: 1 post Tom Lane: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase