Обсуждение: foreign key constraints with inhertiance, hack suggestions?
Hi, First, yes I have read the "5.8.1. Caveats" section that this support does not exist. I agree with the document that this is "a serious limitation of the inheritance feature" Has there been any effort to support this in the near future versions of postgresql? I searched the mailing lists and didn't quite find a straight answer on that, only that there will be some sort of stronger inheritance in 8.2 Second, I was wondering if anyone has any hack suggestions at doing some integrity checks? Basically I have a master 'flows' table which is partitioned and has non-overlapping CHECK constraints on each partition. Each record in the partitions have a unique pair of attributes: interval, flow_id When inserting in to another table 'flow_labels', these two attributes are given, I want to ensure that there exists a single flow in some partition in the 'flows' table that has these two values. I'm no DB expert, but could there be some sort of rule or trigger on insert into 'flow_labels' that does a count() where these two values are exact to ensure there exists 1 flow? Problem is my 'flows' table is on the order of billions of flows, each partition having hundreds of thousands. Suggestions? Thanks! George
George Nychis wrote: > Basically I have a master 'flows' table which is partitioned and has > non-overlapping CHECK constraints on each partition. Each record in the > partitions have a unique pair of attributes: interval, flow_id > > When inserting in to another table 'flow_labels', these two attributes > are given, I want to ensure that there exists a single flow in some > partition in the 'flows' table that has these two values. > > I'm no DB expert, but could there be some sort of rule or trigger on > insert into 'flow_labels' that does a count() where these two values are > exact to ensure there exists 1 flow? Problem is my 'flows' table is on > the order of billions of flows, each partition having hundreds of > thousands. I think count() would be a last resort solution. If possible, I think it would help to add the interval data to your 'flow_labels' table and join on the combined key. That way the information that divides your 'flows' into different tables is available at join time, and constraint exclusion could do its work. That'd still require some way to inherit constraints, but it's a start. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //