Thread: LOST REFERENTIAL INTEGRITY
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.
Within that I have a table with referential integrity constraints which
no longer work.
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.
I do ...-c"\d table_with_referential_integrity" and here's what I get:
[~]$ mpt -c"\d pat_emp_ins"
Table "pat_emp_ins"
Attribute | Type | Modifier
---------------------------+-----------+----------
pat_id | text | not null
ins_co_id | text | not null
employer_id | text | not null
insurance_group | text |
note | text |
print_note_primary | boolean |
print_note_secondary | boolean |
Indices: pat_emp_ins_employer_id_key,
pat_emp_ins_ins_co_id_key,
pat_emp_ins_pat_id_key
[~ create_tables_for_database]$
And here is the SQL I used to generate this table:
--
create table pat_emp_ins (pat_id text not null
references patient,
ins_co_id text not
null
references insurance_company,
employer_id text not null
references employer,
insurance_group text,
note text,
print_note_primary boolean,
print_note_secondary boolean,
unique(pat_id,ins_co_id,employer_id));
--
Problem is, my users using my application are able to insert rows into
"pat_emp_ins" table which have values for "employer_id" and/or
"ins_co_id" which do not exist in the referenced tables. This seems to
have happened recently but I do not know how recently. This application
has been running production since 2003-11-07.
"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\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
Tom Lane wrote:
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.
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?"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
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.
"Jimmie H. Apsey" <japsey@futuredental.com> writes:
>> Each FK constraint should have three associated triggers (two on the
>> referencing table, one on the referenced table).
> 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?
If you are still running 7.1 you obviously do not know the meaning of
the word "fear" ;-) --- it not only has lots of since-fixed bugs, but
at that time we hadn't yet solved the transaction ID wraparound problem,
which means your DB is guaranteed to self-destruct once you reach the
4-billion-transaction mark.
I'd recommend an upgrade to 7.4.5 at your earliest convenience.
regards, tom lane
Tom Lane wrote:
I have kept up-to-date our Red Hat kernels as you can probably see from the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own version of Postgres alongside and compiled into Red Hat's latest and greatest kernel? If that's true, WHEW! I wonder what version of Postgres is installed in Red Hat's latest kernel of AS 3.0?"Jimmie H. Apsey" <japsey@futuredental.com> writes:Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table).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?If you are still running 7.1 you obviously do not know the meaning of the word "fear" ;-) --- it not only has lots of since-fixed bugs, but at that time we hadn't yet solved the transaction ID wraparound problem, which means your DB is guaranteed to self-destruct once you reach the 4-billion-transaction mark. I'd recommend an upgrade to 7.4.5 at your earliest convenience. regards, tom lane
On Mon, Oct 04, 2004 at 05:25:59PM -0400, Jimmie H. Apsey wrote: > I have kept up-to-date our Red Hat kernels as you can probably see from > the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own > version of Postgres alongside and compiled into Red Hat's latest and > greatest kernel? If that's true, WHEW! I wonder what version of > Postgres is installed in Red Hat's latest kernel of AS 3.0? The version of your kernel and the version of postgres are completely unrelated, you can upgrade either whenever you like independant of the other. The only thing you may need to look into is the version of libc and other such libraries. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
"Jimmie H. Apsey" <japsey@futuredental.com> writes:
>> I'd recommend an upgrade to 7.4.5 at your earliest convenience.
>>
> I have kept up-to-date our Red Hat kernels as you can probably see from
> the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own
> version of Postgres alongside and compiled into Red Hat's latest and
> greatest kernel? If that's true, WHEW!
Unfortunately I don't get to dictate Red Hat's backwards-compatibility
policies :-( ... and their policy for AS 2.1 is that it's gonna be
Postgres 7.1 till it dies. This means that anything that's
fundamentally unfixable without an initdb is going to remain broken.
> I wonder what version of
> Postgres is installed in Red Hat's latest kernel of AS 3.0?
RHEL3 uses the PG 7.3 release series, which is a little behind the times
but far less likely to eat your data than 7.1.
regards, tom lane
On Mon, 4 Oct 2004, Tom Lane wrote:
> "Jimmie H. Apsey" <japsey@futuredental.com> writes:
>>> I'd recommend an upgrade to 7.4.5 at your earliest convenience.
>>>
>> I have kept up-to-date our Red Hat kernels as you can probably see from
>> the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own
>> version of Postgres alongside and compiled into Red Hat's latest and
>> greatest kernel? If that's true, WHEW!
>
> Unfortunately I don't get to dictate Red Hat's backwards-compatibility
> policies :-( ... and their policy for AS 2.1 is that it's gonna be
> Postgres 7.1 till it dies. This means that anything that's
> fundamentally unfixable without an initdb is going to remain broken.
AFAIK, the policy is to keep _compatible_ version, which is a sound
policy. RH users sould be able to perform upgrades w/o fear of losing
anything. I can't speak for the postgresql RPM, but I know their
policy is to backport fixes (if possible).
Unluckily, sometimes a pg_dumpall & restore just won't do. You need
to manually edit your dump for the next version of postgres to be
able grok it. Nothing hard, usually, just silly stuff, but anyway
that rules out an automatic dump&restore at rpm -U time.
Of course, no one prevents you from compiling your own version of
postgres and running it on a separate dataspace.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it