Re: identify referential integrity triggers unassociated with foreign key constraints

Bill MacArthur <webmaster(at)dhs-club(dot)com> writes:
> We started using Postgres back around 7.1. We've done dump and reloads to get us up to where we are now at 8.1.

> Over time it would seem that the guts of foreign key constraints have
> changed, for the better. Soon we want to move to the latest version,
> but in the process, I would like to update all the old style "foreign
> keys" to the newest. By old style, I mean we have RI triggers on
> various tables like "RI_ConstraintTrigger_3495148". In pgadmin, these
> do not show up as triggers, neither do they show up as foreign keys as
> some of the system table relationships that exist now for identifying
> these sort of things did not exist when these "foreign keys" were
> first setup. (These constraints still work, you just can't tell they
> are they using pgadmin) Using \d tablename shows them in a list of
> triggers but not as Foreign-key constraints.

These should get updated to a "real" FK constraint automatically when
you load into 8.3 or 8.4. See commits here:
http://archives.postgresql.org/pgsql-committers/2007-11/msg00036.php
http://archives.postgresql.org/pgsql-committers/2007-11/msg00042.php
http://archives.postgresql.org/pgsql-committers/2007-11/msg00069.php
and concurrent discussions (probably on -hackers)

> Occasionally I have taken to dropping some of these triggers and
> mucking with the system tables like pg_trigger and so forth, but those
> experiences are horrid. Things have never been hosed, but I know there
> has to be a better way.

... or at least, I expect that that upgrade logic will work with
unmodified old-style FK trigger definitions. If you did something other
than drop all the old triggers and create a regular FK constraint,
I can't promise much.

regards, tom lane