Re: RI triggers and schemas

If pg_upgrade was shipped with 7.3 in working order with the ability
to convert the old foreign key commands to the new ones I don't think
anyone would care how many funny things are involved. Just fix the
foreign key stuff for 7.3 pg_dump and only support upgrades using that
version, or included pg_upgrade script (any 7.2 release to 7.3)

That said, it doesn't look like it'll be a pretty thing to do with a
shell script.

Hoop jumping may be required to go from 6.5 or 7.0/1 directly to 7.3.

Downside is pg_upgrade is fairly new (can it be trusted -- made to
work 100%?)
Upside is no changes would be required to 7.2 and lots of people would
be really happy to have a fast upgrade process (dump / restore can
take quite a while on large dbs)

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: "Jan Wieck" <JanWieck(at)Yahoo(dot)com>; <pgsql-hackers(at)postgresql(dot)org>
Sent: Sunday, March 31, 2002 8:43 PM
Subject: Re: [HACKERS] RI triggers and schemas

> Last week I said:
> >> I think that instead of storing just table names in the trigger
> >> parameters, we should store either table OIDs or schema name +
table
> >> name. [ ... ]
> >> So I'm leaning towards OIDs, but wanted to see if anyone had a
beef
> >> with that.
>
> I've just realized that if we change the RI trigger arguments this
way,
> we will have a really serious problem with accepting pg_dump scripts
> from prior versions. The scripts' representation of foreign key
> constraints will contain commands like
>
> CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "bar" FROM
"baz" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'baz', 'bar',
'UNSPECIFIED', 'f1', 'f1');
>
> which will absolutely not work at all if the 7.3 triggers are
expecting
> to find OIDs in those arguments.
>
> I thought about allowing the triggers to take qualified names in the
> style of what nextval() is doing in current sources, but that's
still
> going to have a lot of nasty compatibility issues --- mixed-case
> names, names containing dots, etc are all going to be interpreted
> differently than before.
>
> I think we may have little choice except to create two sets of RI
trigger
> procedures, one that takes the old-style arguments and one that
takes
> new-style arguments. However the old-style set will be horribly
fragile
> because they'll have to interpret their arguments based on the
current
> namespace search path.
>
> Of course the *real* problem here is that pg_dump is outputting a
> low-level representation of the original constraints. We knew all
along
> that that would get us into trouble eventually ... and that trouble
is
> now upon us. We really need to fix pg_dump to emit ALTER TABLE ADD
> CONSTRAINT type commands instead of trigger definitions.
>
> A possible escape from the dilemma is to fix pg_dump so that it can
emit
> ADD CONSTRAINT commands when it sees RI triggers, release that in
7.2.2,
> and then *require* people to use 7.2.2 or later pg_dump when it
comes
> time to update to 7.3. I do not much like this ... but it may be
better
> than the alternative of trying to maintain backwards-compatible
> triggers.
>
> Comments? Better ideas?
>
> regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>