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 1kmLDb-0006hw-An for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:26:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmLDZ-0008NC-7I for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:26:37 +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 1kmLDY-0008N3-T9 for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:26:36 +0000 Received: from mail-oi1-x230.google.com ([2607:f8b0:4864:20::230]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kmLDS-0006K8-EY for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:26:35 +0000 Received: by mail-oi1-x230.google.com with SMTP id v85so5873818oia.6 for ; Mon, 07 Dec 2020 10:26:29 -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-transfer-encoding:content-language; bh=u+G8HzanJetNDiFRcH+6WaKwze5Gl3xaL/2O2HT45L8=; b=pKv0AbE5zUT+jliyE2c6YFnvIObES99wZL8vJgAWfwnX199sp2QjlOtCROZI4aTmmo CuObuFtlzEJPYNmIuBoW+5C77lBFMGKeVjjseGM9Ekw9s2RrKpB8d3tT8GMzLA1Gx71o JzQ/wTIqoc2caFWGoQfScOH4uijlkJ9gQWli7ZhHkpVy2BL4rTfbHJthHx8XqQ36TV5k Aq4cNidJoO6M9Dr2WlB75XqQ0CnImShiVScs7CtaODGhaUtHfhvetyHZLhMBeDiX5FQI 07SHpGt4sZYVHIZOhy+xpzgEdRx0iOKKRi99HWC16GdRab6gPsqB08uocRKe7xvyw5H3 EMXg== 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-transfer-encoding :content-language; bh=u+G8HzanJetNDiFRcH+6WaKwze5Gl3xaL/2O2HT45L8=; b=FSgs6cG5Kuj1zfnbKaBNtcp/B39FDC4hwAGZLlmMDIMBD/JV35a3TcAJ6Mqw2zAmIb 8e694Eyd2cwH5znFl95qI4jp3Ys4KLRNewYFbOMB+UqqOc1PkIJ9hSCNP1sxUT0etjcn Ve73/H/d9ItKH7Ue/5cLb+wtcv4hROHitqaUsX+8ucc7Xix9zBCWFtixrcHoWEqnJ50s ecfnjjeEKZW7KvObhc/i4MVnpf1xO6kgFeW8Uj15Utbv9WHvFu6He8oiDspi2Mk7nC99 eZ+sRsrP3oCb79FotmYAPyuy2APt7cPugnOMd++Uytp4izeBD0eGiRYR9wIdYS8WSKB2 OKgA== X-Gm-Message-State: AOAM533s73ZpCsGwcw7cyTJI9KhWirWHedKBHs0cAdxRrjHP7C55BI/A t7l+cyqHXUsDCXgvw6RqeSzh6EvZZRIIdQ== X-Google-Smtp-Source: ABdhPJwlmYfcHgpsBjtY/2ZeeYMglGNKv0IxE7RqeNMdXpJ5ZsZ2MRu6Cvh+3ZpAQeqeox/moWWxhg== X-Received: by 2002:aca:b4c2:: with SMTP id d185mr112077oif.124.1607365588352; Mon, 07 Dec 2020 10:26:28 -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 r25sm791269otp.23.2020.12.07.10.26.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Dec 2020 10:26:27 -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: Date: Mon, 7 Dec 2020 12:26:26 -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: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 OK.  For some reason, I thought INITIALLY DEFERRED was only applicable to INSERT and UPDATE statements. -- Angular momentum makes the world go 'round.