Обсуждение: identify referential integrity triggers unassociated with foreign key constraints
identify referential integrity triggers unassociated with foreign key constraints
От
Bill MacArthur
Дата:
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