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, '[EMAIL PROTECTED]'); # 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