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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
