Patrick Welche's recent problems (see pgsql-general) point out that the
old CREATE CONSTRAINT TRIGGER syntax that optionally omits a "FROM
table" clause doesn't work anymore --- the system *needs* tgconstrrelid
to be set in an RI constraint trigger record, because the RI triggers
now use that OID to find the referenced table. (The table name in the
tgargs field isn't used anymore, mainly because it's not schema-aware.)

This means that RI trigger definitions dating back to 7.0 (or whenever
it was that we fixed the pg_dump bug about not dumping tgconstrrelid)
don't work anymore.

There are a couple things I think we should do. One: modify the CREATE
CONSTRAINT TRIGGER code to try to extract a foreign relation name from
the tgargs if FROM is missing. Without this, we have no hope of loading
working FK trigger definitions from old dumps. Two: modify pg_dump to
extract a name from the tgargs in the same fashion. I'd rather have
pg_dump do this than the backend, and this will at least make things
better in the case where you're using a 7.3 pg_dump against an older
database.

However, if we are going to put that kind of knowledge into pg_dump,
it would only be a small further step to have it dump these triggers
as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
better for forward compatibility than dumping the raw triggers.

Thoughts?

regards, tom lane

Search Discussions

  • Rod Taylor at Sep 26, 2002 at 9:12 pm

    On Thu, 2002-09-26 at 16:57, Tom Lane wrote:
    This means that RI trigger definitions dating back to 7.0 (or whenever
    it was that we fixed the pg_dump bug about not dumping tgconstrrelid)
    don't work anymore.
    I thought 7.0 introduced foreign keys in the first place, so perhaps
    7.1?

    However, if they're coming from 7.0 or earlier would it be appropriate
    to have them bounce through 7.2 / 7.1 first?

    Pain in the ass to dump and reload twice to get to the latest, but since
    they only upgrade once every 2 to 3 years...

    Is this the only problem that 7.0 people are going to experience (server
    side, SQL changes are abundant)?
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    If this type of stuff has to be done, then this is probably the best way
    to go.

    --
    Rod Taylor
  • Tom Lane at Sep 26, 2002 at 9:20 pm

    Rod Taylor writes:
    However, if they're coming from 7.0 or earlier would it be appropriate
    to have them bounce through 7.2 / 7.1 first?
    Won't help. 7.2 will dump 'em out without a FROM clause, just like they
    were loaded.
    Is this the only problem that 7.0 people are going to experience (server
    side, SQL changes are abundant)?
    You're missing the point. Welche was upgrading *from 7.2*. But his
    trigger definitions had a dump/reload history going back to 7.0.

    regards, tom lane
  • Rod Taylor at Sep 26, 2002 at 9:35 pm

    Is this the only problem that 7.0 people are going to experience (server
    side, SQL changes are abundant)?
    You're missing the point. Welche was upgrading *from 7.2*. But his
    trigger definitions had a dump/reload history going back to 7.0.
    Oh.. I certainly did.

    --
    Rod Taylor
  • Robert Treat at Sep 26, 2002 at 9:18 pm
    On Thu, 2002-09-26 at 16:57, Tom Lane wrote:
    <snip>
    There are a couple things I think we should do. One: modify the CREATE
    CONSTRAINT TRIGGER code to try to extract a foreign relation name from
    the tgargs if FROM is missing. Without this, we have no hope of loading
    working FK trigger definitions from old dumps. Two: modify pg_dump to
    extract a name from the tgargs in the same fashion. I'd rather have
    pg_dump do this than the backend, and this will at least make things
    better in the case where you're using a 7.3 pg_dump against an older
    database. <snip>
    Thoughts?
    I'm trying to think of the cases where this extraction might fail, but
    maybe more important is what happens if it does fail?

    Robert Treat
  • Tom Lane at Sep 26, 2002 at 9:22 pm

    Robert Treat writes:
    I'm trying to think of the cases where this extraction might fail, but
    maybe more important is what happens if it does fail?
    Then you have broken RI triggers ... which is the problem now.

    regards, tom lane
  • Robert Treat at Sep 26, 2002 at 9:43 pm

    On Thu, 2002-09-26 at 17:22, Tom Lane wrote:
    Robert Treat <xzilla@users.sourceforge.net> writes:
    I'm trying to think of the cases where this extraction might fail, but
    maybe more important is what happens if it does fail?
    Then you have broken RI triggers ... which is the problem now.
    Uh...yeah, I got that part. I meant what will be done if/when it fails?
    Throw a WARNING and keep going? Throw an ERROR and die?

    Robert Treat
  • Tom Lane at Sep 26, 2002 at 10:22 pm

    Robert Treat writes:
    On Thu, 2002-09-26 at 17:22, Tom Lane wrote:
    Robert Treat <xzilla@users.sourceforge.net> writes:
    I'm trying to think of the cases where this extraction might fail, but
    maybe more important is what happens if it does fail?
    Then you have broken RI triggers ... which is the problem now.
    Uh...yeah, I got that part. I meant what will be done if/when it fails?
    Throw a WARNING and keep going? Throw an ERROR and die?
    What I was thinking of was to do the following in CREATE CONSTRAINT
    TRIGGER:

    if (no FROM clause)
    {
    try to extract table name from given tgargs;
    try to look up table OID;
    if successful, insert table OID into tgconstrrelid;
    }

    If the lookup fails, you'd be left creating a constraint trigger with
    zero tgconstrrelid, which is what's happening now. That would error
    out upon use (if it's really an RI trigger), thus alerting you that
    you have a broken trigger. (We could add a couple of lines in the
    RI triggers to cause the error message to be more helpful than
    "Relation 0 not found".)

    regards, tom lane
  • Stephan Szabo at Sep 26, 2002 at 10:10 pm

    On Thu, 26 Sep 2002, Tom Lane wrote:

    Patrick Welche's recent problems (see pgsql-general) point out that the
    old CREATE CONSTRAINT TRIGGER syntax that optionally omits a "FROM
    table" clause doesn't work anymore --- the system *needs* tgconstrrelid
    to be set in an RI constraint trigger record, because the RI triggers
    now use that OID to find the referenced table. (The table name in the
    tgargs field isn't used anymore, mainly because it's not schema-aware.)

    This means that RI trigger definitions dating back to 7.0 (or whenever
    it was that we fixed the pg_dump bug about not dumping tgconstrrelid)
    don't work anymore.

    There are a couple things I think we should do. One: modify the CREATE
    CONSTRAINT TRIGGER code to try to extract a foreign relation name from
    the tgargs if FROM is missing. Without this, we have no hope of loading
    working FK trigger definitions from old dumps. Two: modify pg_dump to
    extract a name from the tgargs in the same fashion. I'd rather have
    pg_dump do this than the backend, and this will at least make things
    better in the case where you're using a 7.3 pg_dump against an older
    database.
    I'd worry about doing things only to pg_dump since that'd still leave
    people that did use the old dump in the dark and there'd be nothing even
    indicating a problem until they did something that used the constraint.
    Even a notice for a missing FROM would be better (although at that
    point how far is it to just fixing the problem). I can look at it this
    weekend (since it probably was my bug in the first place) unless you'd
    rather do it.
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    Wasn't there still some question about the fact that ATAC causes a
    check of the constraint which for large tables is not insignificant.
    I don't remember if there was any consensus on how to deal with that.
  • Tom Lane at Sep 26, 2002 at 10:25 pm

    Stephan Szabo writes:
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    Wasn't there still some question about the fact that ATAC causes a
    check of the constraint which for large tables is not insignificant.
    I don't remember if there was any consensus on how to deal with that.
    Hmm, good point. That's probably why we didn't go ahead and do it
    already...

    Maybe we should just put the lookup hack into the backend's CREATE
    CONSTRAINT TRIGGER code and leave it at that.

    regards, tom lane
  • Stephan Szabo at Sep 26, 2002 at 10:44 pm

    On Thu, 26 Sep 2002, Tom Lane wrote:

    Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    Wasn't there still some question about the fact that ATAC causes a
    check of the constraint which for large tables is not insignificant.
    I don't remember if there was any consensus on how to deal with that.
    Hmm, good point. That's probably why we didn't go ahead and do it
    already...

    Maybe we should just put the lookup hack into the backend's CREATE
    CONSTRAINT TRIGGER code and leave it at that.
    That seems reasonable. And probably not too hard. There might still
    be cases where we can't get it, and I think we probably should at least
    throw a notice on the create in that case, the admin will *probably*
    ignore it, but if they want to fix the situation right away they can.
  • Matthew T. O'Connor at Sep 28, 2002 at 3:00 am
    From: "Tom Lane" <tgl@sss.pgh.pa.us>
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    There was some talk of adding Rod Taylor's identifies upgrade script to
    contrib, or mentioning it in the release. I think that it upgrades Foreign
    key, Unique, and Serial constraints, is that relevant here? Could it be
    used (or modified) to handle this situation? Just a thought.
  • Bruce Momjian at Sep 28, 2002 at 5:04 am
    Both are done, and in CVS in /contrib/adddepend.

    ---------------------------------------------------------------------------

    Matthew T. O'Connor wrote:
    From: "Tom Lane" <tgl@sss.pgh.pa.us>
    However, if we are going to put that kind of knowledge into pg_dump,
    it would only be a small further step to have it dump these triggers
    as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
    better for forward compatibility than dumping the raw triggers.
    There was some talk of adding Rod Taylor's identifies upgrade script to
    contrib, or mentioning it in the release. I think that it upgrades Foreign
    key, Unique, and Serial constraints, is that relevant here? Could it be
    used (or modified) to handle this situation? Just a thought.

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 26, '02 at 8:57p
activeSep 28, '02 at 5:04a
posts13
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase