Tom Lane wrote:
"Jimmie H. Apsey" <[EMAIL PROTECTED]> 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.
|
