Verifying Referential Integrity

[Geisler, Jim]

So, as far as I know, PostgreSQL does not have any way of verifying the loss
of referential integrity.

Are there any recommended methods or utilities for checking referential
integrity in a PostgreSQL database?

[Alvaro Herrera]

Re: Verifying Referential Integrity

On Tue, Oct 05, 2004 at 02:03:09PM -0400, Geisler, Jim wrote:[color=blue]
> So, as far as I know, PostgreSQL does not have any way of verifying the loss
> of referential integrity.[/color]

.... just like it doesn't have a way of verifying loss of tables or any
other object. If someone messes up the schema (be it via ALTER commands
or directly modifying system catalogs), Postgres will continue working
with the new schema.

[color=blue]
> Are there any recommended methods or utilities for checking referential
> integrity in a PostgreSQL database?[/color]

Maybe do pg_dump -s periodically and compare to a known good version?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jimmie H. Apsey]

Re: Verifying Referential Integrity

Geisler, Jim wrote:
[color=blue]
> So, as far as I know, PostgreSQL does not have any way of verifying
> the loss of referential integrity.
>
> Are there any recommended methods or utilities for checking
> referential integrity in a PostgreSQL database?
>[/color]

Of course, Tom Lane suggested I look at the pg_trigger table. I suppose
I'll have to do this from time to time. His good point on an old
database system (7.1.3) like what is included within Red Hat AS 2.1 is
as he wrote, "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
ALTERTABLE ADD FOREIGN KEY to re-make a consistent trigger set."

I did what he suggested and then re-created the offending table and
altered the other offending table. Now, my database has working
referential integrity between the two tables involved.

Jim Apsey

[Frank Miles]

Re: Verifying Referential Integrity

On Tue, 5 Oct 2004, Geisler, Jim wrote:
[color=blue]
> So, as far as I know, PostgreSQL does not have any way of verifying the loss
> of referential integrity.
>
> Are there any recommended methods or utilities for checking referential
> integrity in a PostgreSQL database?[/color]

Perhaps someone knows of a more "automatic" solution, but what I have done
is generate a test set that exercises as many aspects of the table design
as I have been able to imagine. It also tests pgsql functions, including
triggers. This is particularly useful with version changes. As the
occasional bug is found, more test cases are added. Like a unit or
regression test, of course.

Those more experienced than I probably have better ideas...

-frank

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Re: Verifying Referential Integrity

"Geisler, Jim" <jgeisler@vocol lect.com> writes:[color=blue]
> So, as far as I know, PostgreSQL does not have any way of verifying the loss
> of referential integrity.[/color]

What are you trying to accomplish here, and in what PG version?

Are you trying to check that PG thinks that a foreign-key relationship
is installed? In recent versions psql's "\d" will tell you that. If
you're dealing with an old version you might have to look directly at
the system catalogs.

Are you not trusting that an active foreign-key relationship has been
correctly enforced? Then I think you want to do some kind of JOIN
query to see if you can find any rows with no master row. (You could
actually do this by temporarily creating a new, redundant FK constraint;
but if you are feeling that paranoid you're likely not going to trust
the system's answer anyway...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Jimmie H. Apsey]

Re: Verifying Referential Integrity

Tom Lane wrote:
[color=blue]
>"Geisler, Jim" <jgeisler@vocol lect.com> writes:
>
>[color=green]
>>So, as far as I know, PostgreSQL does not have any way of verifying the loss
>>of referential integrity.
>>
>>[/color]
>
>What are you trying to accomplish here, and in what PG version?
>
>Are you trying to check that PG thinks that a foreign-key relationship
>is installed? In recent versions psql's "\d" will tell you that. If
>you're dealing with an old version you might have to look directly at
>the system catalogs.
>
>Are you not trusting that an active foreign-key relationship has been
>correctly enforced? Then I think you want to do some kind of JOIN
>query to see if you can find any rows with no master row. (You could
>actually do this by temporarily creating a new, redundant FK constraint;
>but if you are feeling that paranoid you're likely not going to trust
>the system's answer anyway...)
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org
>
>
>[/color]
Of course, I use the most simple method of selecting all values which
are not in RI_table, e.g.

# select * from user_table where user_table.valu e not in (select
RI.value from RI_table);

I had to do this often when I ported from one Postgres-like database
(namely Illustra) into my current Postgres database. I noticed some
rows would not insert into my target table from a text file containing
my source table. So, I created a table like my desired target table but
without referential integrity. Then, on the table w/o RI I did the
above. But, as you can see, I do things as simply as possible.

With great regard for the pros out here in Postgres Land,

Jim Apsey