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 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;