table references with partitioning

Поиск
Список
Период
Сортировка
От dan chak
Тема table references with partitioning
Дата
Msg-id 927B9879-BEA6-469B-B975-313C0B513EC4@mit.edu
обсуждение исходный текст
Ответы Re: table references with partitioning  (Erik Jones <erik@myemma.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Less than two weeks before PostgreSQL Conference: East
Следующее
От: Alex Vinogradovs
Дата:
Сообщение: SPI_execute_plan(): how to make a Datum to insert type inet ?