From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Arne Roland <A(dot)Roland(at)index(dot)de> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: a misbehavior of partition row movement (?) |
Date: | 2020-12-15 03:45:19 |
Message-ID: | CA+HiwqE1a+=sj4M9sdvuPzYxtDxzK4y-Jk=DuWoEMo0m6=8C6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Dec 15, 2020 at 12:43 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> Quoting your original example:
>
> drop table a, b;
> create table a (id serial, primary key (id)) partition by range (id);
> create table b (id serial, primary key (id)) partition by range (id);
> alter table b add constraint a_fk foreign key (id) references a (id)
> on delete cascade;
> create table a1 partition of a for values from (1) to (2);
> create table a2 partition of a for values from (2) to (3);
> create table b1 partition of b for values from (1) to (2);
> create table b2 partition of b for values from (2) to (3);
> insert into a (id) values (1);
> insert into b (id) values (1);
>
> -- correctly errors out instead of silently performing the ON DELETE CASCADE
> update a set id=2;
> ERROR: update or delete on table "a" violates foreign key constraint
> "a_fk" on table "b"
> DETAIL: Key (id)=(1) is still referenced from table "b".
>
> select * from b;
> id
> ----
> 1
> (1 row)
>
> Changing the example to specify ON UPDATE CASCADE:
>
> drop table a, b;
> create table a (id serial, primary key (id)) partition by range (id);
> create table b (id serial, primary key (id)) partition by range (id);
> alter table b add constraint a_fk foreign key (id) references a (id)
> on delete cascade;
Oops, I copy-pasted the wrong block of text from my terminal. I meant:
alter table b add constraint a_fk foreign key (id) references a (id)
on delete cascade on update cascade;
> create table a1 partition of a for values from (1) to (2);
> create table a2 partition of a for values from (2) to (3);
> create table b1 partition of b for values from (1) to (2);
> create table b2 partition of b for values from (2) to (3);
> insert into a (id) values (1);
> insert into b (id) values (1);
>
> -- correctly applies ON UPDATE CASCADE action
> update a set id=2;
> UPDATE 1
>
> select * from b;
> id
> ----
> 2
> (1 row)
--
Amit Langote
EDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2020-12-15 04:16:18 | Re: Proposed patch for key managment |
Previous Message | Amit Langote | 2020-12-15 03:43:11 | Re: a misbehavior of partition row movement (?) |