Re: a request for some experienced input.....

On Tue, 14 Nov 2000, Beth K. Gatewood wrote:

> Hi all-
>
> We are leaning toward using postgresql for a current project we are
> expecting at least 10 million data points. Currently the design has at
> least 20 tables (and we are expecting at least 5 more). Probably one
> table will hold at least 500,000 tuples (probably 5 columns the larges
> field being varchar2(250) ). Sorry-I don't have better descriptions of
> the data at this point. Honestly, we are especially new to the whole db
> admin issues from a project of this size.
>
> Could someone tell me the state of the referential integrity
> implementation. I have been reading the newsgroups for about 3 weeks
> and have seen various references to these issues. I have just read the
> todo list and they mention that 'MATCH PARTIAL' and 'PENDANT' are yet to
> be done....what is this????

MATCH PARTIAL is a specific match type which describes which rows are
considered matching rows for purposes of meeting or failing the
constraint. (In match partial, a fktable (NULL, 2) would match a pk
table (1,2) as well as a pk table (2,2). It's different from match
full in which case (NULL,2) would be invalid or match unspecified
in which case it would match due to the existance of the NULL in any
case). There are some bizarre implementation details involved with
it and it's different from the others in ways that make it difficult.
It's in my list of things to do, but I haven't come up with an acceptable
mechanism in my head yet.

PENDANT adds that for each row of the referenced table the values of
the specified column(s) are the same as the values of the specified
column(s) in some row of the referencing tables.

If all you need is match full or the default unspecified match and you
don't need pendant, then this part doesn't really concern you.

> Frankly, I am concerned when I read that there may be 'issues' with
> referential integrity especially since some of our data will be fleeting
> (i.e. there will be no files outside of the database from which we could
> re-capture the data). I am especially paranoid about losing this type
> of data (as anyone would be!)

The main issues in 7.0 are that older versions (might be fixed in
7.0.3) would fail very badly if you used alter table to rename tables that
were referenced in a fk constraint and that you need to give update
permission to the referenced table. For the former, 7.1 will (and 7.0.3
may) give an elog(ERROR) to you rather than crashing the backend and the
latter should be fixed for 7.1 (although you still need to have write
perms to the referencing table for referential actions to work properly)