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 1kmLHk-0006uE-8Z for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:30:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmLHj-00033A-3J for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:30:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmLHi-000333-Nr for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:30:54 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kmLHf-0006M2-Gr for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:30:53 +0000 Received: by mail-oi1-x22b.google.com with SMTP id l207so13559113oib.4 for ; Mon, 07 Dec 2020 10:30:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=SbUvNYmuWF5/XiB5FqPOkyc4F/fLbi6CgPQfj2Yw/Zc=; b=RX4sSDZqxjkEQ3+v15FjYqmlE97ens76W84pVW/TohguuiBgEonKe18NI0+rOYErRZ /U9vDmXNf4cdN0F0YkSHycc91XcdOxA/KFTj0i8jI935J4ClYZZzmYAfnhu02VfUlAOf jG3FJqgBjnr6sGyDyBl8mkDxg2jD6LTfmXyvU0RCSxu8XDmuDKY7wTJEhLKOjw47Psn6 +/PitHipIbpNOaf6ZG5UB+tRYTwDOow+MvIR/3LGOXtN3KE2vrPCN7KuwRRXhT7E91Ms F/7JW2lKOKi5orS05iRsD53eG917DmK42Ver1BY60lRTyIN8zQcFFTcr/RYgv7B0ekIT 1qPg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=SbUvNYmuWF5/XiB5FqPOkyc4F/fLbi6CgPQfj2Yw/Zc=; b=VG52jpkP6/YVSfsECnGQDxTE7JVJkG+vYKcb+X0fDqSgMK6JRhtanI2aEpvD7545qA vIgS3gPwdYREJNpdpUFbns0RtTcnqZgO4afaW+DFx+YZC2TURKK2c1t0LzNoiu/+nlzN 7AF0IQiVWUNiTvN/kBjsPLHrYugNJNuxPQEG8EBXhaW/hakLwjX4NT2P2vi9WUz6ZQHi Tun7wqTwTEXj2qltTzGdrgGrxN4GW3/mRLfxvR8kgrivOtVq7jClgpsBorw1035YabFq pOLrwSd6HDPH3RRIp7lzTTbyL1RcOXHk42pch/xImQHt5bXIiAm3cxB8puQUiaH1Rcsd ON4Q== X-Gm-Message-State: AOAM533xSZnLnFcmbRFXTyTR/ax1Z0G23ACTfkUxjHtLOzgd2YmdIEMM PtfSjdT9FIg1wc2ncrvKK9pE4V3TUz9TLA== X-Google-Smtp-Source: ABdhPJz7nyzRiQtov/CLienQvUGS7syvOtwUNpbcY3CdhdmJiDf/HHoiTYNxLEjXhv02Ewdtz9GzhA== X-Received: by 2002:aca:4815:: with SMTP id v21mr128368oia.96.1607365850526; Mon, 07 Dec 2020 10:30:50 -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 g188sm3087884oia.19.2020.12.07.10.30.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Dec 2020 10:30:49 -0800 (PST) Subject: Re: Deferrable FK not behaving as expected. To: pgsql-general@lists.postgresql.org References: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> From: Ron Message-ID: <3c32cc9c-1589-7f22-b9e3-f4bcccbaf87e@gmail.com> Date: Mon, 7 Dec 2020 12:30:48 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------5F49BFE57B7414E347DBCE8F" 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. --------------5F49BFE57B7414E347DBCE8F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 12/7/20 12:19 PM, Thomas Kellerer wrote: > Ron schrieb am 07.12.2020 um 19:15: >> 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* > > I think if you only mention "deferrable" this is the same as "deferrable > initially immediate", > so you will need to make them deferrable in your transaction: > >     SET CONSTRAINTS ALL DEFERRED; > > or create the constraint with > >     deferrable initially deferred Neither technique worked. test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED; ALTER TABLE 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 INITIALLY DEFERRED* Number of partitions: 12 (Use \d+ to list them.) 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=# rollback; ROLLBACK -- Angular momentum makes the world go 'round. --------------5F49BFE57B7414E347DBCE8F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 12/7/20 12:19 PM, Thomas Kellerer wrote:
Ron schrieb am 07.12.2020 um 19:15:
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*

I think if you only mention "deferrable" this is the same as "deferrable initially immediate",
so you will need to make them deferrable in your transaction:

    SET CONSTRAINTS ALL DEFERRED;

or create the constraint with

    deferrable initially deferred

Neither technique worked.

test=# ALTER TABLE sales_detail ALTER CONSTRAINT fk_sales_detail_sales_header DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE

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 INITIALLY DEFERRED

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

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=# rollback;
ROLLBACK



--
Angular momentum makes the world go 'round.
--------------5F49BFE57B7414E347DBCE8F--