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
