[YSQL] Moving rows across partitions fails due to constraint check failure on source partition #25911
Description
Jira Link: DB-15223
Description
Setup two partitions of a parent partition table with different check constraints on each child partition. Insert a row in one of the child partitions.
inherit=# create table parpar(partid int, value int) partition by range (partid);
CREATE TABLE
inherit=# create table pardef partition of parpar default;
CREATE TABLE
inherit=# create table parchild partition of parpar for values from (0) to (10);
CREATE TABLE
inherit=# \d parchild
Table "public.parchild"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
partid | integer | | |
value | integer | | |
Partition of: parpar FOR VALUES FROM (0) TO (10)
inherit=# alter table parchild add constraint parchild_value_check CHECK (value < 100);
ALTER TABLE
inherit=# alter table pardef add constraint pardef_value_check CHECK (value < 1000);
ALTER TABLE
inherit=# insert into parpar values(1, 90);
INSERT 0 1
inherit=# select tableoid::regclass, * from parpar;
tableoid | partid | value
----------+--------+-------
parchild | 1 | 90
(1 row)
inherit=# select * from pardef;
partid | value
--------+-------
(0 rows)
Attempt to move the row to a different partition. This reports a constraint violation on the table it is being moved from. Inserting a fresh row with those values works fine.
inherit=# update parpar set partid = 20, value = 900 where partid = 1;
ERROR: new row for relation "parchild" violates check constraint "parchild_value_check"
DETAIL: Failing row contains (20, 900).
inherit=# insert into parpar values (20,900);
INSERT 0 1
inherit=# update parpar set partid = 21, value = 90 where partid = 1;
UPDATE 1
Issue Type
kind/bug
Warning: Please confirm that this issue does not contain any sensitive information
- I confirm this issue does not contain any sensitive information.