Thread: a request for some experienced input.....
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???? 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!) Since we are just starting this...we would be in a position to upgrade to 7.1 when it is released (although we do have concerns about having to upgrade when every possible release comes available to make sure that we have the strongest implementation of referential constraints). Although these admin issues may be mitigated by the standpoint that we hear that Oracle is a bear for administration....since we have never done this we don't really know what this means....Can anyone comment on this? Our other option would be to go with Oracle in which we 'think' that we could just use once single release (maybe we are naive here?) I would be extremely thankful if someone could please discuss these issues....and how worried I should be about referential integrity issues and about upgrade issues? Thanks a lot- Beth
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)
"Beth K. Gatewood" <bethg@mbt.washington.edu> writes:
> 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.
We been implementing and maintaining a portal site for a client on
PostgreSQL since May. This site has about 40 tables and couple of them have
500K-1500K rows. Performance and stability has been very good, no data
losses and no problems with our use of foreign keys.
The learning curve for PostgreSQL has also been much faster than it has
been for similar projects we've had on Sybase earlier.
All in all I'm very impressed with PostgreSQL, but I still miss a good
fulltext engine and better tools for the design stage(like PowerDesigner).
Regards,
Gunnar
> Stephan- Thank you so much for taking the effort to answer this these questions. You help is truly appreciated.... I just have a few points for clarification. > > 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. Does this mean, currently that I can not have foreign keys with null values? > > > 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. I am not sure I know what you mean here.....Are you saying that the value for the FK column must match the value for the PK column? > > > 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) Are the steps to this outlined somewhere then? Thanks alot! Beth
On Tue, 14 Nov 2000, Beth K. Gatewood wrote:
> >
>
> Stephan-
>
> Thank you so much for taking the effort to answer this these questions. You
> help is truly appreciated....
>
> I just have a few points for clarification.
>
> >
> > 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.
>
> Does this mean, currently that I can not have foreign keys with null values?
Not exactly...
Match full = In FK row, all columns must be NULL or the value of each
column must not be null and there is a row in the PK table where
each referencing column equals the corresponding referenced
column.
Unspecified = In FK row, at least one column must be NULL or each
referencing column shall be equal to the corresponding referenced
column in some row of the referenced table
Match partial is similar to match full except we ignore the null columns
for purposes of the each referencing column equals bit.
For example:
PK Table Key values: (1,2), (1,3), (3,3)
Attempted FK Table Key values: (1,2), (1,NULL), (5,NULL), (NULL, NULL)
(hopefully I get this right)...
In match full, only the 1st and 4th fk values are valid.
In match partial, the 1st, 2nd, and 4th fk values are valid.
In match unspecified, all the fk values are valid.
The other note is that generally speaking, all three are basically the
same for the single column key. If you're only doing references on one
column, the match type is mostly meaningless.
> > 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.
>
> I am not sure I know what you mean here.....Are you saying that the value for
> the FK column must match the value for the PK column?
I haven't really looked at PENDANT, the above was just a small rewrite of
some descriptive text in the sql99 draft I have. There's a whole bunch
of rules in the actual text of the referential constraint definition.
The base stuff seems to be: (Rf is the referencing columns, T is the
referenced table)
3) If PENDANT is specified, then:
a) For a given row in the referencing table, let pendant
reference designate an instance in which all Rf are
non-null.
b) Let number of pendant paths be the number of pendant
references to the same referenced row in a referenced table
from all referencing rows in all base tables.
c) For every row in T, the number of pendant paths is equal to
or greater than 1.
So, I'd read it as every row in T must have at least one referencing row
in some base table.
There are some details about updates and that you can't mix PENDANT and
MATCH PARTIAL or SET DEFAULT actions.
> > 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)
>
> Are the steps to this outlined somewhere then?
The permissions stuff is just a matter of using GRANT and REVOKE to set
the permissions that a user has to a table.