LOST REFERENTIAL INTEGRITY

**Tom Lane** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

"Jimmie H. Apsey" <japsey@futured ental.com> writes:[color=blue]
> 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.[/color]

7.1 is mighty ancient, but ...
[color=blue]
> 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.[/color]

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_ConstraintTr igger_2913673 | 1655 | 17 | t | t | <unnamed> | 2913659 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\000f2\ 000f1\000
2913646 | RI_ConstraintTr igger_2913671 | 1654 | 9 | t | t | <unnamed> | 2913659 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\000f2\ 000f1\000
2913659 | RI_ConstraintTr igger_2913669 | 1644 | 21 | t | t | <unnamed> | 2913646 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\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?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Jimmie H. Apsey** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

Tom Lane wrote:
[color=blue]
>"Jimmie H. Apsey" <japsey@futured ental.com> writes:
>
>[color=green]
>>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.
>>
>>[/color]
>
>7.1 is mighty ancient, but ...
>
>
>[color=green]
>>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.
>>
>>[/color]
>
>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_ConstraintTr igger_2913673 | 1655 | 17 | t | t | <unnamed> | 2913659 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\000f2\ 000f1\000
> 2913646 | RI_ConstraintTr igger_2913671 | 1654 | 9 | t | t | <unnamed> | 2913659 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\000f2\ 000f1\000
> 2913659 | RI_ConstraintTr igger_2913669 | 1644 | 21 | t | t | <unnamed> | 2913646 | f | f | 6 | | <unnamed>\000ba r\000foo\000UNS PECIFIED\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
>
>
>[/color]
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** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

"Jimmie H. Apsey" <japsey@futured ental.com> writes:[color=blue][color=green]
>> Each FK constraint should have three associated triggers (two on the
>> referencing table, one on the referenced table).[/color][/color]
[color=blue]
> 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?[/color]

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** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

Tom Lane wrote:
[color=blue]
>"Jimmie H. Apsey" <japsey@futured ental.com> writes:
>
>[color=green][color=darkred]
>>>Each FK constraint should have three associated triggers (two on the
>>>referencin g table, one on the referenced table).
>>>
>>>[/color][/color]
>
>
>[color=green]
>>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?
>>
>>[/color]
>
>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
>
>
>[/color]
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** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

On Mon, Oct 04, 2004 at 05:25:59PM -0400, Jimmie H. Apsey wrote:[color=blue]
> 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?[/color]

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/[color=blue]
> 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.[/color]

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

iD8DBQFBYcOoY5T wig3Ge+YRAk7TAJ wIrWuTckhzyj8PI wK7Q0Yyqwt2kgCe PFXX
5Vy8LWGEzMEu2F8 eQy9QhDg=
=nYqN
-----END PGP SIGNATURE-----

**Tom Lane** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

"Jimmie H. Apsey" <japsey@futured ental.com> writes:[color=blue][color=green]
>> I'd recommend an upgrade to 7.4.5 at your earliest convenience.
>>[/color]
> 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![/color]

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.
[color=blue]
> I wonder what version of
> Postgres is installed in Red Hat's latest kernel of AS 3.0?[/color]

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 majordomo@postg resql.org

**Marco Colombo** · Nov 23 '05, 02:24 AM

Re: LOST REFERENTIAL INTEGRITY

On Mon, 4 Oct 2004, Tom Lane wrote:
[color=blue]
> "Jimmie H. Apsey" <japsey@futured ental.com> writes:[color=green][color=darkred]
>>> I'd recommend an upgrade to 7.4.5 at your earliest convenience.
>>>[/color]
>> 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![/color]
>
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)