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

On Fri, 22 Jun 2007 18:23:44 -0300
"Daniel van Ham Colchete" <daniel(dot)colchete(at)gmail(dot)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.

(at)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(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

On Fri, 22 Jun 2007 18:23:44 -0300
"Daniel van Ham Concrete" <daniel(dot)colchete(at)gmail(dot)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(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

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(at)example(dot)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(at)example(dot)com');
INSERT INTO regsemail (rID, email) VALUES(3,'daniel(at)example(dot)com');
INSERT INTO regsemail (rID, email) VALUES(6,'daniel(at)example(dot)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;