[YSQL] Moving rows across partitions fails due to constraint check failure on source partition

[iSignal]

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.