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

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

Responses

Browse pgsql-general by date

  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