Обсуждение: 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@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

 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.

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

On Fri, 22 Jun 2007 18:23:44 -0300
"Daniel van Ham Concrete" <daniel.colchete@gmail.com> wrote:
> >
> > # 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


Hello,

I could make child tables inherit an external reference
by the following queries. Actually, There appears to be referring
to foreign keys.

test=# INSERT INTO regsemail (rID, email) VALUES(6,'daniel@example.com');
ERROR:  insert or update on table "regsemail_00" violates foreign key constraint
 "regsemail_00_rid_fkey"

--
Regard,
Masaru Sugawara




-- First, make regs_xx inherit the primary key constraint on regs.
CREATE TABLE regs (rID serial PRIMARY KEY, name text, number int);
CREATE TABLE regs_00 (CHECK (number >= 00 AND number < 10),
                 PRIMARY KEY (rID)) INHERITS (regs);
CREATE OR REPLACE 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),
                 PRIMARY KEY (rID)) INHERITS (regs);
CREATE OR REPLACE 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);

-- Second, make regsemail_xx inherit the foreign key constraint
-- on regsemail.
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);
CREATE OR REPLACE RULE insert_regsemail_00 AS ON INSERT TO regsemail
                 WHERE (rID >= 0 AND rID < 10)
                 DO INSTEAD INSERT INTO regsemail_00
                    VALUES (NEW.dID, NEW.rID, NEW.email);
CREATE TABLE regsemail_10 (CHECK (rID >= 10 AND rID < 20 ),
                 FOREIGN KEY (rID) REFERENCES regs_10(rID)
                 ON DELETE CASCADE) INHERITS (regsemail);
CREATE OR REPLACE RULE insert_regsemail_10 AS ON INSERT TO regsemail
                 WHERE (rID >= 10 AND rID < 20)
                 DO INSTEAD INSERT INTO regsemail_10
                    VALUES (NEW.dID, NEW.rID, NEW.email);


-- Insert four rows in regs
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);
SELECT * FROM regs_00;
SELECT * FROM regs_10;
SELECT r.*, p.relname AS "inherited table" FROM regs r, pg_class p
       WHERE r.tableoid = p.oid;


-- Test for foreign key constraint
INSERT INTO regsemail (rID, email) VALUES(1,'daniel@example.com');
INSERT INTO regsemail (rID, email) VALUES(3,'daniel@example.com');
INSERT INTO regsemail (rID, email) VALUES(6,'daniel@example.com');
SELECT * FROM regsemail_00;
SELECT * FROM regsemail_10;
SELECT r.*, p.relname AS "inherited table" FROM regsemail r, pg_class p
      WHERE r.tableoid = p.oid;


-- Test for ON DELETE CASCADE
DELETE FROM regs WHERE rID = 3;
SELECT * FROM regs_00;
SELECT * FROM regs_10;
SELECT r.*, p.relname AS "inherited table" FROM regs r, pg_class p
       WHERE r.tableoid = p.oid;
SELECT * FROM regsemail_00;
SELECT * FROM regsemail_10;
SELECT r.*, p.relname AS "inherited table" FROM regsemail r, pg_class p
       WHERE r.tableoid = p.oid;