Re: Unintuitive behavior regarding inheritance

Ok

So what I am trying to do is:

1)  Paritioned tables
2)  Joins against the table partitions

The way I have this done is:

The first table is partitioned on the basis of one part of the primary
key.  So in theory since nobody has permission to insert into the base
table, all records should have unique primary keys in the inheritance
tree.  The join table against the partition table is more complex and
I had to partition it across two pieces of the primary key.  To be
clear I am joining a partitioned table against a partitioned table
(since that seems to be the only sane way of joining against a
partitioned table if referential integrity has to be enforced.

Basically imagine the following (the actual schema is quite a bit more complex):

create table invoice ( id serial primary key,
reference text,
....
);

create table order (id serial primary key,
reference text,
....
);

create table file_class ( id serial not null unique,
label text primary key
);

create table file_attachment (
id serial not null unique,
file_class int references file_class(id),
ref_key int,
file_name text,
primary key(file_class, ref_key, file_name)
);

create table invoice_attachment (
check (file_class = 1),
foreign key(ref_key) references invoice(id),
) inherits (file_attachment);

create table order_attachment (
check(file_class = 2),
foreign key (ref_key) references order(id),
) inherits (file_attachment);

create table order_to_invoice_file_map(
src_class int references invoice_attachment(id),
dest_class int references order_attachment(id),
file_id int references order_attachment(id),
....
); -- this is actually inherited in my schema too

It seems for this to work all primary and foreign key constraints have
to be redeclared on each child table.  OTOH check constraints are
additive.

The idea here is to provide a system where by a consistent relational
interface is provided for file attachments, and where neither higher
levels of the application nor pieces of the model have to be aware of
the existance or not of partitions.  Ideally given the fact that each
child table constrains part of the primary key to a reserved set of
values means I shouldn't have to worry about primary key collision
across the inheritance tree.  Unfortunately, it seems that to do this
a lot of copy/paste is required.

The major drawback with this approach is that since check constraints
are always inherited they can't be used to enforce the idea that base
tables should have no rows.  At the same time, since unique
constraints are never inherited they have to be redeclared on every
child.

I recognize that one of the big hurdles here is the need sometimes to
drop indexes while loading data.... but at the same time there are
several places where this sort of thing has been overlooked so far by
people far more knowledgeable about PostgreSQL than I am.

Hope this helps.
Chris Travers