Re: Possible bug (or I don't understand how foreign keys should work with partitions) - Mailing list pgsql-general

On Fri, 22 Jun 2007 18:23:44 -0300
"Daniel van Ham Colchete" <daniel.colchete@gmail.com> wrote:


Hi,

As far as I read the documents(see below), it seems to be correct
that no error message occurred in your case.

http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
-- All check constraints and not-null constraints on a parent table are
-- automatically inherited by its children. Other types of constraints
-- (unique, primary key, and foreign key constraints) are not inherited.


For example, you probably need to declare CREATE TABLE like the below.

@CREATE TABLE regsemail (dID serial PRIMARY KEY,
                          rID integer, email text,
                          FOREIGN KEY(rID) REFERENCES regs(rID)
                          ON DELETE CASCADE);
  CREATE TABLE regsemail_00 (CHECK ( rID >= 0 AND rID < 10 ),
                          FOREIGN KEY(rID) REFERENCES regs_00(rID)
                          ON DELETE CASCADE) INHERITS(regsemail);


By the way, why will you have such a huge number of e-mail addresses ?

--
Masaru Sugawara




> People,
>
> either I don't understand how p
> 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