Обсуждение: 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 //