From: | Masaru Sugawara <rk73(at)ghost(dot)plala(dot)or(dot)jp> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | daniel(dot)colchete(at)gmail(dot)com |
Subject: | Re: Possible bug (or I don't understand how foreign keys should work with partitions) |
Date: | 2007-06-28 13:25:00 |
Message-ID: | 20070628210010.CEAE.RK73@ghost.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-06-28 14:00:40 | Re: AutoVacuum Behaviour Question |
Previous Message | Vincenzo Romano | 2007-06-28 12:15:34 | Re: Execution variability |