Re: LOST REFERENTIAL INTEGRITY

Tom Lane wrote:

>"Jimmie H. Apsey" <japsey(at)futuredental(dot)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\000
> 2913646 | RI_ConstraintTrigger_2913671 | 1654 | 9 | t | t | <unnamed> | 2913659 | f | f | 6 | | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000
> 2913659 | 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.