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

From: Márcio Antônio Sepp <marcio(at)zyontecnologia(dot)com(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Weird behavior with update cascade on partitioned tables when moving data between partitions
Date: 2020-02-05 17:30:04
Message-ID: 00d801d5dc49$e7225d50$b56717f0$@com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2020-02-05 18:59:53 Re: calculating the MD5 hash of role passwords in C
Previous Message Chris Charley 2020-02-05 17:15:07 Re: Should I reinstall over current installation?