LOST REFERENTIAL INTEGRITY

[Jimmie H. Apsey]

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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Jimmie H. Apsey" <ja****@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jimmie H. Apsey]

Tom Lane wrote:

"Jimmie H. Apsey" <ja****@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

---------------------------(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.

[Tom Lane]

"Jimmie H. Apsey" <ja****@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jimmie H. Apsey]

Tom Lane wrote:

"Jimmie H. Apsey" <ja****@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

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?

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBYcOoY5Twig3Ge+YRAk7TAJwIrWuTckhzyj8PIwK7Q0 Yyqwt2kgCePFXX
5Vy8LWGEzMEu2F8eQy9QhDg=
=nYqN
-----END PGP SIGNATURE-----

[Tom Lane]

"Jimmie H. Apsey" <ja****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Marco Colombo]

On Mon, 4 Oct 2004, Tom Lane wrote:

"Jimmie H. Apsey" <ja****@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.
_____/ _____/ _/ Co*****@ESI.it

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)