Re: [HACKERS] Reconstructing FKs in pg_dump
From: Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Reconstructing FKs in pg_dump
Both are done, and in CVS in /contrib/adddepend. --- Matthew T. O'Connor wrote: From: Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reconstructing FKs in pg_dump
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Reconstructing FKs in pg_dump
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Reconstructing FKs in pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reconstructing FKs in pg_dump
Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Reconstructing FKs in pg_dump
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Reconstructing FKs in pg_dump
On Thu, 2002-09-26 at 17:22, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reconstructing FKs in pg_dump
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reconstructing FKs in pg_dump
Robert Treat [EMAIL PROTECTED] writes:
On Thu, 2002-09-26 at 17:22, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Reconstructing FKs in pg_dump
Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reconstructing FKs in pg_dump
On Thu, 26 Sep 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
