Обсуждение: identify referential integrity triggers unassociated with foreign key constraints

Hello,

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
thelatest 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
upas triggers, neither do they show up as foreign keys as some of the system table relationships that exist now for
identifyingthese 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-keyconstraints. 

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. 

What I would like to do is to identify these almost invisible "foreign key" triggers so that they can be dropped sanely
andthen create new, named constraints can be identified using ordinary tools. Is this possible? 

Many thanks,
Bill MacArthur

Bill MacArthur <webmaster@dhs-club.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