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

From: "Daniel van Ham Colchete" <daniel(dot)colchete(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Possible bug (or I don't understand how foreign keys should work with partitions)
Date: 2007-06-22 21:23:44
Message-ID: 8a0c7af10706221423p66345b8fvbeb0049970c92602@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tomasz brymora 2007-06-22 21:29:24 creating temporary table PostgreSql 8.1 and iBatis 2.3
Previous Message Jim Nasby 2007-06-22 21:10:51 Re: A problem in inheritance