Thread: Weird behavior with update cascade on partitioned tables when moving data between partitions

Hi all,


Update cascade apparently doesn't work as well on partiotioned tables (when
moving data between different partitions).
Please, look at example below:


-- Create master partitioned table;
CREATE TABLE users (
    id             serial NOT NULL,
    username       text NOT NULL,
    password       text,
    primary key (id)
)
PARTITION BY RANGE ( id );

CREATE TABLE users_p0
    partition OF users
    FOR VALUES FROM (MINVALUE) TO (10);
CREATE TABLE users_p1
    partition OF users
    FOR VALUES FROM (10) TO (20);


-- Create detail table;
create table logs ( id serial not null,
    user_id integer not null,
    primary key (id),
    foreign key (user_id) references users (id) on update cascade
);


test=# insert into users (id, username, password) values (1, 'user',
'pass');
INSERT 0 1

test=# insert into logs (id, user_id) values (1, 1);
INSERT 0 1

-- update without changing partition
test=# update users set id = 8 where id = 1;
UPDATE 1

-- Lets see the log table
test=# select * from logs;
 id | user_id
----+---------
  1 |       8
(1 row)

-- update changing partition;
test=# update users set id = 12 where id = 8;
ERROR:  update or delete on table "users_p0" violates foreign key constraint
"logs_user_id_fkey1" on table "logs"
DETAIL:  Key (id)=(8) is still referenced from table "logs".


IF create the foreign key with option "on update cascade on delete cascade"
after running this command: update users set id = 12 where id = 8; the
Record on logs table Will be deleted.

Is this the expectec behavior?


--
Att
Márcio A. Sepp