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@example.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