Possible bug (or I don't understand how foreign keys should work with partitions)

People,

either I don't understand how partitions works or I think I found a bug
here.

I'm using PostgreSQL-8.2.4 with Gentoo.

The code explains:

# First I create the table regs with 2 partitions:
create table regs (rID serial primary key, name text, number int);
create table regs_00 ( CHECK ( number >= 00 AND number < 10 )) INHERITS
(regs);
create rule insert_regs_00 AS ON INSERT TO regs WHERE ( number >= 00 AND
number < 10 ) DO INSTEAD INSERT INTO regs_00 VALUES ( NEW.rID, NEW.name,
NEW.number );
create table regs_10 ( CHECK ( number >= 10 AND number < 20 )) INHERITS
(regs);
create rule insert_regs_10 AS ON INSERT TO regs WHERE ( number >= 10 AND
number < 20 ) DO INSTEAD INSERT INTO regs_10 VALUES ( NEW.rID, NEW.name,
NEW.number );

# Them I create the table regsemail also with 2 partitions but with a
foreign key:
create table regsemail (dID serial primary key, fk_regs_id integer
REFERENCES regs (rID) ON DELETE CASCADE, email text);
create table regsemail_00 ( CHECK ( fk_regs_id >= 0 AND fk_regs_id < 10 ))
INHERITS (regsemail);
CREATE RULE insert_regsemail_00 AS ON INSERT TO regsemail WHERE ( fk_regs_id
>= 0 AND fk_regs_id < 10 ) DO INSTEAD INSERT INTO regsemail_00 VALUES (
NEW.dID, NEW.fk_regs_id, NEW.email );
create table regsemail_10 ( CHECK ( fk_regs_id >= 10 AND fk_regs_id < 20 ))
INHERITS (regsemail);
CREATE RULE insert_regsemail_10 AS ON INSERT TO regsemail WHERE ( fk_regs_id
>= 10 AND fk_regs_id < 20 ) DO INSTEAD INSERT INTO regsemail_10 VALUES (
NEW.dID, NEW.fk_regs_id, NEW.email );

# Insert four rows in regs (rID will go from 1 to 4):
insert into regs (name, number) values ('Daniel', 4);
insert into regs (name, number) values ('Daniel', 14);
insert into regs (name, number) values ('Daniel', 5);
insert into regs (name, number) values ('Daniel', 15);

# Insert a 'invalid' row in regsemail
insert into regsemail (fk_regs_id, email) values (6, 'daniel(at)example(dot)com');
# END!

I should get an error saying something like "...violates foreign key
constraint..." but I'm not getting anything. That's the bug. If I don't have
the partitions them I get the error message (as I think I should).

The problem I'm trying to solve is: I'll have a 1.8 billion rows table
(regs) and another one having at least one row to each row from the first
one. The solution is very simple: partitions. The 1.8 billion rows is
distributed uniformly in the days of the year, so I'll create one partition
for each day. But I have to do something similar with the second table as
well otherwise I wouldn't win much if I had to do a JOIN. I was testing how
foreign keys would work in this case and ran into this.

Is this really a bug? If not, what am I doing wrong please?

Best regards,
Daniel

I was testing how
> foreign keys would work in this case and ran into this.
>
> Is this really a bug? If not, what am I doing wrong please?

Here is what the postgresql manual says about this:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and
foreign key constraints only apply to single tables, not to their inheritance children. This is
true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms
of the above example:

http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS

Overcoming this limitation is on the todo list, but as far as I know it isn't slated to be fixed
in the upcomming 8.3. Perhaps 8.4 will include this feature.

Regards,
Richard Broersma Jr.