On Mar 19, 2008, at 12:54 PM, dan chak wrote:
I'm running into a problem with maintaining referential integrity
with inheritance-based partitioning. Imagine a situation where
partitions are based on time. Two tables A and B are partitioned,
and B references A. If records in B are added some time after
records in A, the insertion times may cross the partition boundary,
resulting in A records in last month's partition and B entries in
this month's partition. Thus the references need to point at the
base table, which (at least when selecting from it) appears to have
all the records.
I've tried two approaches to managing references pointing at a base
table. In the first approach, the references are all defined on the
inheritance base tables and point at other inheritance base tables.
In this case, anything goes when inserting into child tables. The
references don't appear to be checked at all. Example:
<snip>
I've also tried adding references to the child tables, with those
references pointing at the base tables of the referenced relation.
In this case, an insert that should work doesn't. True, the values
aren't physically in the base table, but it's the only express
what's needed... Example:
<snip>
It appears the only way to get referential integrity to flat out
work as expected is to maintain references between child tables and
not point at base tables at all. But then I'm back to square one.
If a record in b_2 (february) needs to reference a record either in
a_1 (january) or a_2 (february), based on when A was inserted
relative to B, then it's not going to work.
Right, as it stands now, foreign keys will not follow inheritance
relationships. I believe this is in the TODO list although I haven't
heard of anybody actually picking up on it, but then I don't follow -
hackers. What you can do is create your own foreign key functionality
that will scan both a_1 and a_2 via a trigger.
Erik Jones
DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
