Re: Deferrable FK not behaving as expected.

On 12/7/20 1:27 PM, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)com> writes:
>> Neither technique worked.
>> test=# ALTER TABLE sales_detail ALTER CONSTRAINT
>> fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED;
>> ALTER TABLE
> Hmm, reproduced here. There seems to be some kind of bug
> in ALTER CONSTRAINT --- it looks like it's updated all the
> child FKs successfully, but it actually hasn't. If you
> drop the FK constraint altogether, and recreate it having
> DEFERRABLE INITIALLY DEFERRED from the start, it should
> work --- at least it does for me.

That works *sometimes*, but not when we *really* want it to work.

test=# alter table sales_detail drop CONSTRAINT fk_sales_detail_sales_header;
ALTER TABLE

test=# ALTER TABLE sales_detail
test-#     ADD CONSTRAINT fk_sales_detail_sales_header
test-#     FOREIGN KEY (cust_id, order_ts)
test-#     REFERENCES sales_header (cust_id, order_ts) DEFERRABLE INITIALLY
DEFERRED;
ALTER TABLE
test=#
test=#

-- Works

test=# begin transaction;
BEGIN
test=# delete from sales_header where cust_id = 1;
DELETE 3
test=#
test=# rollback;
ROLLBACK

-- Does not work

test=# begin transaction;
BEGIN
test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001;
ERROR:  removing partition "sales_header_202001" violates foreign key
constraint "sales_detail_cust_id_order_ts_fkey"
DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still
referenced from table "sales_detail".
test=# rollback;
ROLLBACK

--
Angular momentum makes the world go 'round.