Thread: Reconstructing FKs in pg_dump
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
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
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
Rod Taylor <rbt@rbt.ca> 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
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.
regards, tom lane
> > 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
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
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.
Robert Treat <xzilla@users.sourceforge.net> 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,
inserttable 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 <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.
regards, tom lane
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.
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.
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, Pennsylvania19073