Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmL2o-0006LB-OL for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:15:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmL2n-0007dz-J4 for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:15:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmL2n-0007ds-7K for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:15:29 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kmL2k-000078-5m for pgsql-general@postgresql.org; Mon, 07 Dec 2020 18:15:28 +0000 Received: by mail-ot1-x334.google.com with SMTP id b62so13360415otc.5 for ; Mon, 07 Dec 2020 10:15:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=to:from:subject:message-id:date:user-agent:mime-version :content-language; bh=2ha2ZQea9cv8cUqU7qmpKdVEmxEwQcg5/U/Qhx7TOXE=; b=f+W4jnJkwVvKSmYiqIW0/dOKD1g/geTGdllRAlk+GGI9NeB+gB/fysxypLAhqtzTtk 7K+CxOgXPFYKE9MBpgzDBq9LVPadHw3airLRbcIcxbBl0VTK0Y42lvpk3FEqpMj1oivB gi8as8O83Ok/ZO55k7LsFT8DUTnIToXW9w30B77/jKckIEDbo8+mXpmSy7UIQ/ATojbh kmSRS6Z7tDZSCg/8X70vV5Ls2SE+b1hvHTWGvbs815INRpmntEMF3hvXGaEfFI221jLa HWBZQ8dGAPbaDTnmy1ugXOneSsRvgYQivFjrpWCBotZ/odL4gUl+0Ixqex0ldQuRP43T xqvw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-language; bh=2ha2ZQea9cv8cUqU7qmpKdVEmxEwQcg5/U/Qhx7TOXE=; b=rj8pA7LhsDhZecrF1BRSK45XK6c5rlB6O0P4SBTITFvbd0Us9oesAHnqfCIPCRFD1B asBOLRngwldXk+FNjo8Tm0TRBxKrTG62RvyDWn59OXAjH6beYCW4wU6oQ0afyl7t1pZy 30CejRMd9XOiTJFuKMeBOwv43reXLgIfdbHsoQA9u4J59EK3vflknTRxR329GLPEze4y aoW5NK88zf/jdHxS7evJBGObYnxi/LEfpr4jvtmjJkgnZb1DOig1Sq10fB6woc/OvYoU Yzk9dxvLPwlJK3XqljOFG1jfekJ340LGnRPp77GfAesAZ1WDvQL2JtjZ2KQU5MqGlnVs 4kGA== X-Gm-Message-State: AOAM532+OmTM0fAl5by0qCI7oYauSIRA+UGDFJi1Wa0BGcrR6PFUutzm qdPms0tGmRCykYAH1oASwn+aKqLNsczNMA== X-Google-Smtp-Source: ABdhPJy3+N5ZdZ0JuFPDalWJwpc1pHrHEJuDwYw3RfIQwaDXTK9Tr+MrQRZcGiSKI6USvF03Ri3C+w== X-Received: by 2002:a9d:6450:: with SMTP id m16mr13500834otl.344.1607364922524; Mon, 07 Dec 2020 10:15:22 -0800 (PST) Received: from [192.168.1.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id n3sm2805647otj.46.2020.12.07.10.15.20 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Dec 2020 10:15:21 -0800 (PST) To: pgsql-general From: Ron Subject: Deferrable FK not behaving as expected. Message-ID: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> Date: Mon, 7 Dec 2020 12:15:20 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------1D2849CF9D2419F6F3D67237" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------1D2849CF9D2419F6F3D67237 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit v12.5 I added a deferrable FK constraint on sales_detail to ensure that no one can delete records from sales_header when an associated sales_detail record exists. That works perfectly. The problem is that try to delete parent records before the child records *inside a transaction* also fails. Here are sample tables, sample data and the failed delete statement. What am I missing? test=# \d sales_header                     Partitioned table "public.sales_header"     Column     |            Type             | Collation | Nullable | Default ---------------+-----------------------------+-----------+----------+---------  order_num     | integer                     | |          |  cust_id       | integer                     |           | not null |  order_ts      | timestamp without time zone |           | not null |  shipping_addr | text                        | |          | Partition key: RANGE (order_ts) Indexes:     "sales_header_pkey" PRIMARY KEY, btree (cust_id, order_ts)     "sales_header_i1" btree (order_num) Referenced by:     TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"           FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* test=# \d sales_detail                    Partitioned table "public.sales_detail"     Column    |            Type             | Collation | Nullable | Default --------------+-----------------------------+-----------+----------+---------  cust_id      | integer                     |           | not null |  order_ts     | timestamp without time zone |           | not null |  seq_no       | integer                     |           | not null |  inventory_id | integer                     | |          |  quantity     | numeric(10,2)               | |          |  price        | numeric(10,2)               | |          |  tax_rate     | numeric(3,3)                | |          | Partition key: RANGE (order_ts) Indexes:     "sales_detail_pkey" PRIMARY KEY, btree (cust_id, order_ts, seq_no) Foreign-key constraints:     "fk_sales_detail_sales_header" FOREIGN KEY (cust_id, order_ts)            REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* Number of partitions: 12 (Use \d+ to list them.) INSERT INTO sales_header VALUES (1, 1, '2020-01-05 13:05:42.567', '123 Main St, Anytown, Iowa'); INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 1, 12345, 5.8, 28.40, 0.092); INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 2, 23456, 6.0, 98.40, 0.092); INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 3, 34567, 1.8, 67.00, 0.092); INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 4, 45678, 450,  2.00, 0.092); INSERT INTO sales_header VALUES (2, 1, '2020-02-05 13:05:42.567', '234 Main St, Anytown, Iowa'); INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 1, 6575, 5.2, 567, 0.045); INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 2, 4565, 456, 545, 0.045); INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 3, 7899, 768, 432, 0.045); INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 4, 2354, 556, 890, 0.045); INSERT INTO sales_header VALUES (3, 1, '2020-03-05 13:05:42.567', '345 Main St, Anytown, Iowa'); INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 1, 6575, 5.2, 567, 0.045); INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 2, 4565, 456, 545, 0.045); INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 3, 7899, 768, 432, 0.045); test=# begin transaction; BEGIN test=# delete from sales_header where cust_id = 1; ERROR:  update or delete on table "sales_header_202001" violates foreign key constraint "sales_detail_cust_id_order_ts_fkey" on table "sales_detail" DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still referenced from table "sales_detail". test=# test=# rollback; -- Angular momentum makes the world go 'round. --------------1D2849CF9D2419F6F3D67237 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
v12.5

I added a deferrable FK constraint on sales_detail to ensure that no one can delete records from sales_header when an associated sales_detail record exists. That works perfectly.

The problem is that try to delete parent records before the child records inside a transaction also fails.

Here are sample tables, sample data and the failed delete statement.

What am I missing?

test=# \d sales_header
                    Partitioned table "public.sales_header"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 order_num     | integer                     |           |          |
 cust_id       | integer                     |           | not null |
 order_ts      | timestamp without time zone |           | not null |
 shipping_addr | text                        |           |          |
Partition key: RANGE (order_ts)
Indexes:
    "sales_header_pkey" PRIMARY KEY, btree (cust_id, order_ts)
    "sales_header_i1" btree (order_num)
Referenced by:
    TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sales_header"
          FOREIGN KEY (cust_id, order_ts) REFERENCES sales_header(cust_id, order_ts) DEFERRABLE


test=# \d sales_detail
                   Partitioned table "public.sales_detail"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 cust_id      | integer                     |           | not null |
 order_ts     | timestamp without time zone |           | not null |
 seq_no       | integer                     |           | not null |
 inventory_id | integer                     |           |          |
 quantity     | numeric(10,2)               |           |          |
 price        | numeric(10,2)               |           |          |
 tax_rate     | numeric(3,3)                |           |          |
Partition key: RANGE (order_ts)
Indexes:
    "sales_detail_pkey" PRIMARY KEY, btree (cust_id, order_ts, seq_no)
Foreign-key constraints:
    "fk_sales_detail_sales_header" FOREIGN KEY (cust_id, order_ts)
           REFERENCES sales_header(cust_id, order_ts) DEFERRABLE

Number of partitions: 12 (Use \d+ to list them.)

INSERT INTO sales_header VALUES (1, 1, '2020-01-05 13:05:42.567', '123 Main St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 1, 12345, 5.8, 28.40, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 2, 23456, 6.0, 98.40, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 3, 34567, 1.8, 67.00, 0.092);
INSERT INTO sales_detail VALUES (1, '2020-01-05 13:05:42.567', 4, 45678, 450,  2.00, 0.092);

INSERT INTO sales_header VALUES (2, 1, '2020-02-05 13:05:42.567', '234 Main St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 1, 6575, 5.2, 567, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 2, 4565, 456, 545, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 3, 7899, 768, 432, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-02-05 13:05:42.567', 4, 2354, 556, 890, 0.045);

INSERT INTO sales_header VALUES (3, 1, '2020-03-05 13:05:42.567', '345 Main St, Anytown, Iowa');
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 1, 6575, 5.2, 567, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 2, 4565, 456, 545, 0.045);
INSERT INTO sales_detail VALUES (1, '2020-03-05 13:05:42.567', 3, 7899, 768, 432, 0.045);

test=# begin transaction;
BEGIN
test=# delete from sales_header where cust_id = 1;
ERROR:  update or delete on table "sales_header_202001" violates foreign key constraint "sales_detail_cust_id_order_ts_fkey" on table "sales_detail"
DETAIL:  Key (cust_id, order_ts)=(1, 2020-01-05 13:05:42.567) is still referenced from table "sales_detail".
test=#
test=# rollback;



--
Angular momentum makes the world go 'round.
--------------1D2849CF9D2419F6F3D67237--