RE: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed
От | Rozboril, Robert |
---|---|
Тема | RE: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed |
Дата | |
Msg-id | SN6PR01MB4559C457FF745D12CF79CCEBE5E70@SN6PR01MB4559.prod.exchangelabs.com обсуждение исходный текст |
Ответ на | Re: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed (Alvaro Herrera <alvherre@2ndquadrant.com>) |
Ответы |
Re: PostgreSQL v11.3 - Records are deleted from child table ifpartition key of parent table is changed
(Alvaro Herrera <alvherre@2ndquadrant.com>)
|
Список | pgsql-bugs |
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 referencingpartitioned 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@2ndquadrant.com] Sent: Thursday, June 20, 2019 20:01 To: Rozboril, Robert <robert.rozboril@dxc.com> Cc: pgsql-bugs@lists.postgresql.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 anotherpartition. 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, maycontain 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 recipientof this message, you are not authorized to read, print, retain, copy or disseminate any part of this message. Ifyou have received this message in error, please destroy and delete all copies and notify the sender by return e-mail. Regardlessof content, this e-mail shall not operate to bind DXC Technology Company or any of its affiliates to any orderor other contract unless pursuant to explicit written agreement or government initiative expressly permitting the useof e-mail for such purpose. --.
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Михаил ДенисовДата:
Сообщение: segfault during SELECT using && ANY (ARRAY[NULL]::BOX2D).
Следующее
От: Pavan DeolaseeДата:
Сообщение: Re: BUG #15724: Can't create foreign table as partition