On update cascade failing when moving data between partitions - Mailing list pgsql-bugs

I have a table that must be partitioned by date in order for it to have
unique dates related to something, this table is referenced by another
partitioned table, after adding ON UPDATE CASCADE to the FK I
was able to update the event date, and have it propagated to the other
table, but that fails as soon as the data has to move between partitions.

I've tested this on PG 13.2, so forgive me if it was already fixed or if this
is something that won't be supported.

CREATE SCHEMA tst;
CREATE TABLE tst.events (
    id SERIAL,
    date_time timestamp with time zone NOT NULL,
    PRIMARY KEY (id, date_time)
) PARTITION BY RANGE (date_time);

CREATE TABLE tst.events_2021_01 PARTITION OF tst.events
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE tst.events_2021_02 PARTITION OF tst.events
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

CREATE TABLE tst.event_tasks (
    id SERIAL,
    event_id integer NOT NULL,
    event_dt timestamp with time zone NOT NULL,
    PRIMARY KEY (id, event_dt),
    FOREIGN KEY (event_id, event_dt) REFERENCES tst.events (id,
date_time) ON UPDATE CASCADE
) PARTITION BY RANGE (event_dt);

CREATE TABLE tst.event_tasks_2021_01 PARTITION OF tst.event_tasks
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE tst.event_tasks_2021_02 PARTITION OF tst.event_tasks
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

CREATE TABLE tst.event_subtaks (
    event_id integer NOT NULL,
    event_dt timestamp with time zone NOT NULL,
    event_task_id integer NOT NULL,
    something integer NOT NULL,
    PRIMARY KEY (event_id, event_dt, something),
    FOREIGN KEY (event_id, event_dt) REFERENCES tst.events (id,
date_time) ON UPDATE CASCADE,
    FOREIGN KEY (event_task_id, event_dt) REFERENCES tst.event_tasks
(id, event_dt) ON UPDATE CASCADE
) PARTITION BY RANGE (event_dt);
CREATE INDEX ON tst.event_subtaks(event_task_id);

CREATE TABLE tst.event_subtaks_2021_01 PARTITION OF tst.event_subtaks
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE tst.event_subtaks_2021_02 PARTITION OF tst.event_subtaks
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

insert into tst.events (date_time ) values ('2021-01-20 09:50:00-03');

insert into tst.event_tasks (event_id, event_dt) values (1,
'2021-01-20 09:50:00-03');

insert into tst.event_subtaks (event_id, event_dt, event_task_id,
something) values (1, '2021-01-20 09:50:00-03', 1, 101);

-- Works
update tst.events set date_time = '2021-01-23 09:50:00-03';
UPDATE 1

-- Fails as the referring table would need to move data to another partition
=> update tst.events set date_time = '2021-02-20 09:50:00-03';
ERROR:  update or delete on table "events_2021_01" violates foreign
key constraint "event_tasks_event_id_event_dt_fkey1" on table
"event_tasks"
DETAIL:  Key (id, date_time)=(1, 2021-01-23 09:50:00-03) is still
referenced from table "event_tasks".

What's also interesting is that event_tasks_event_id_event_dt_fkey1
isn't listed in \d

Thanks.

-- 
Daniel Nicoletti

KDE Developer - http://dantti.wordpress.com