Re: 7.0 FK trigger question

From: wieck(at)debis(dot)com (Jan Wieck)
To: Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>
Cc: Postgresql <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 7.0 FK trigger question
Date: 2000-04-04 15:00:20
Message-ID: m12cUoW-0003knC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> Hi,
>
> due to the limitations in alter table, I generate some SQL to
> implement changes to tables. This works along the lines of
>
> 1. drop fk triggers on old table
> 2. rename serial sequences on old table
> 3. drop indexes on old table
> 4. rename old table
> 5. create new table
> 6. insert into new table select ... from old table
> 7. drop new sequences/rename old sequences
> 8. recreate fk triggers

9. drop old table

If you do 9. you can skip 1. because that's done
automatically.

> [...]
>
> This looks kind-of hairy to drop and recreate correctly.
>
> I thought an alternative may be to change the oid's in pg_trigger. But I
> saw that the oid's of the tables are part of the trigger name. I could
> probably recreate the trigger names with different oid's but this looks
> like asking for trouble.

Not exactly. The OIDs in the trigger names are just ones that
CREATE CONSTRAINT TRIGGER allocates itself to give any of
them a unique name. They aren't used anywhere else, so don't
care. And BTW: specifying a constraint really invokes these
commands internally.

> So what is the best solution? It would be great if there would be some
> way to drop foreign key triggers and re-instate them. This would also
> help with loading data where there are circular dependencies of foreign
> keys, as one could drop a trigger to break the loop, load the data, and
> re-instate the triggers.

Ideally you would use correct ALTER TABLE ... ADD CONSTRAINT
commands, which are implemented in 7.0.

pg_dump actually does sort of this "disable RI triggers" for
data only dumps. You might want to setup a simple test
database and take a data only dump to see the mechanism.

>
> So I guess my question really boils down to: is it possible to write a
> function that drops a foreign key trigger or re-instates it? This should
> really be ALTER TABLE table ALTER COLUMN column (DROP|CREATE)
> CONSTRAINT.... or something along those lines.

There's still something missing in ALTER TABLE. DROP
CONSTRAINT is one of them, but since your sequencs with
renaming the old etc. is the safest possibility anyway, it's
not that high priority.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sergio A. Kessler 2000-04-04 15:09:46 Re: Permissions
Previous Message Sergey V. Mikheev 2000-04-04 14:47:37 Permissions