Re: Deferrable FK not behaving as expected.

[Ron <ronljohnsonjr@xxxxxxxxx>]

    On 12/7/20 1:27 PM, Tom Lane wrote:
> Ron <ronljohnsonjr@xxxxxxxxx> 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.