Re: LOST REFERENTIAL INTEGRITY - Mailing list pgsql-general

Tom Lane wrote:

"Jimmie H. Apsey" <japsey@futuredental.com> writes: 

Referential Integrity on one of our production tables seems to have been 
lost.  I am running Postgres 7.1.3 embedded within Red Hat 
kernel-2.4.9-e.49.   

7.1 is mighty ancient, but ...
 

I do not know how to disable referential integrity on a column in a table.
I do not know how to view what Postgres thinks my referential integrity 
constraints are on this table.   

In that version, you'd be talking about triggers on the tables, and it
seems that psql's \d didn't learn to display triggers till later.
You'll need to look at pg_trigger directly.  For example,

regression=# select version();                            version
------------------------------------------------------------------PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# create table bar (f2 int references foo);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
regression=# \d foo         Table "foo"Attribute |  Type   | Modifier
-----------+---------+----------f1        | integer | not null
Index: foo_pkey

-- drat, no trigger display
regression=# select * from pg_trigger order by oid desc limit 3;tgrelid |            tgname            | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr |                         tgargs
---------+------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+--------------------------------------------------------2913646 | RI_ConstraintTrigger_2913673 |   1655 |     17 | t         | t              | <unnamed>    |       2913659 | f            | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\0002913646 | RI_ConstraintTrigger_2913671 |   1654 |      9 | t         | t              | <unnamed>    |       2913659 | f            | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\0002913659 | RI_ConstraintTrigger_2913669 |   1644 |     21 | t         | t              | <unnamed>    |       2913646 | f            | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000
(3 rows)

regression=#

Each FK constraint should have three associated triggers (two on the
referencing table, one on the referenced table).  You can sort out which
is which by looking at the tgargs field --- note how the referencing and
referenced table and field names are embedded in that.  I suspect that
some of these triggers got dropped or disabled.

If you don't find all three triggers for some one constraint, the best
bet is to drop any remaining triggers from the set and then issue ALTER
TABLE ADD FOREIGN KEY to re-make a consistent trigger set.
		regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org
 

OH, that's very scary for me that triggers can vanish/be eliminated w/o my direct action.  Yes, I do now see that the triggers on my production table have been lost.  I built a test table and they appear as expected.  Is there any way I can prevent this or become aware that something had done this to my production database?

On my machine:

[~]$ mpt -c"select version();"
                           version                          
-------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

[~]$

I'll now go, as suggested by you, drop triggers on the test database to see to it that it actually works as expected.  Then I'll re-build the FK triggers within the test database before I do it to the production database.