RE: PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed

Hello,

Before I raised this bug I tried also V11 notation but result was the same.

CREATE TABLE public.po (
po_id int8 NOT NULL,
remark varchar(1000) NULL,
archived bool NOT NULL
) PARTITION BY LIST (archived);

CREATE TABLE pot PARTITION OF po FOR VALUES IN ('TRUE');
CREATE TABLE pof PARTITION OF po FOR VALUES IN ('FALSE');

ALTER TABLE po ADD CONSTRAINT po_pkey PRIMARY KEY (po_id, archived);

CREATE TABLE public.pol (
pol_id int8 NOT NULL,
po_id int8 NOT NULL,
remark varchar(1000) NULL,
archived bool NOT NULL
) PARTITION BY LIST (archived);

CREATE TABLE polt PARTITION OF pol FOR VALUES IN ('TRUE');
CREATE TABLE polf PARTITION OF pol FOR VALUES IN ('FALSE');

ALTER TABLE pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id, archived);

ALTER TABLE polf ADD CONSTRAINT polf_pof_id_fk FOREIGN KEY (po_id, archived) REFERENCES pof(po_id, archived) ON DELETE CASCADE;
ALTER TABLE polt ADD CONSTRAINT polt_pot_id_fk FOREIGN KEY (po_id, archived) REFERENCES pot(po_id, archived) ON DELETE CASCADE;

INSERT INTO po VALUES (1, '1', FALSE);
INSERT INTO pol VALUES (1, 1, '1', FALSE);

SELECT count(1) FROM po; -- 1
SELECT count(1) FROM pol; -- 1

UPDATE po
SET archived = TRUE
WHERE po_id = 1;

SELECT count(1) FROM po; -- 1
SELECT count(1) FROM pol; -- 0 - records were deleted and not moved to correct partition

That way as you suggested to create PKs and FKs cannot be done because PK must contain partition key and foreign keys referencing partitioned tables are not supported.

Anyway data cannot be deleted from child table in both cases (V10 or V11 notation).

Regards,
Robert

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)2ndquadrant(dot)com]
Sent: Thursday, June 20, 2019 20:01
To: Rozboril, Robert <robert(dot)rozboril(at)dxc(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: PostgreSQL v11.3 - Records are deleted from child table if partition key of parent table is changed

On 2019-Jun-20, Rozboril, Robert wrote:

> ALTER TABLE polf ADD CONSTRAINT polf_po_id_fk FOREIGN KEY (po_id)
> REFERENCES pof(po_id) ON DELETE CASCADE; ALTER TABLE polt ADD
> CONSTRAINT polt_po_id_fk FOREIGN KEY (po_id) REFERENCES pot(po_id) ON
> DELETE CASCADE;

Hmm, yeah, I wouldn't expect this to work well -- the UPDATEs done by the cascade action would not see that there is another partition. If you were declaring the PK and FK constraints on the parent tablej, it should work correctly:

ALTER TABLE pol ADD CONSTRAINT pol_pkey PRIMARY KEY (pol_id); ALTER TABLE pol ADD CONSTRAINT pol_po_id_fk FOREIGN KEY (po_id) REFERENCES po(po_id) ON DELETE CASCADE; (similarly for the "po" table).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

DXC Technology Company -- This message is transmitted to you by or on behalf of DXC Technology Company or one of its affiliates. It is intended exclusively for the addressee. The substance of this message, along with any attachments, may contain proprietary, confidential or privileged information or information that is otherwise legally exempt from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. If you have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardless of content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. --.