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:
--
-- references defined in base table
--
create sequence a_ib_seq;
create table a(
id integer not null default nextval('a_ib_seq') primary key
);
create sequence b_ib_seq;
create table b(
id integer not null default nextval('b_ib_seq') primary key,
a_id integer
);
alter table b add constraint a_ib_fkey foreign key (a_id) references
a(id);
create table a_1 () inherits (c);
create table b_1 () inherits (d);
insert into a_1(id) values(nextval('a_ib_seq'));
insert into b_1(a_id) values((select id from a_1 limit 1));
insert into b_1(a_id) values(-1); -- should fail, but works!
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:
--
-- references defined in child tables
--
create sequence a_ib_seq;
create table a(
id integer not null default nextval('a_ib_seq') primary key
);
create sequence b_ib_seq;
create table b(
id integer not null default nextval('b_ib_seq') primary key,
a_id integer
);
create table a_1 () inherits (a);
create table b_1 () inherits (b);
alter table b_1 add constraint a_ib_fkey foreign key (a_id) references
a(id);
insert into a_1(id) values(nextval('a_ib_seq'));
insert into b_1(a_id) values((select id from a_1 limit 1)); -- should
work!
-- ERROR: insert or update on table "b_1" violates foreign key
constraint "a_ib_fkey"
-- DETAIL: Key (a_id)=(1) is not present in table "a".
--
-- postgres=# select * from a;
-- id
-- ----
-- 1
-- (1 row)
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.
Any ideas on how to get this working?
Thanks,
Dan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
