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 1kmL6S-0006Sx-9t for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:19:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmL6Q-0004wq-Rz for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 18:19:14 +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 1kmL6Q-0004wj-IN for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:19:14 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmL6O-0006Gp-9l for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 18:19:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=badeba3b8450; t=1607365149; bh=kfbHPrpblV1cyED6/djhtWEkaJVODE9qp7s4FgkVd1E=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=Guq/ORBKGI5UMNznOIoF8xJPM5LectA0Rq2rY4aaTqYFuLGdGw17/ZvG7FyATKT9Q t9PdiP6UK+tXepOgK4TWnvtUbPbRToC/bcaKP5SDTHccpUDLK41lZycFFC6uj8RvEe jIvhhVqWAxy9MMeWT1ps37dSbwhIDk9JtnMFzCq8= X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c Received: from [192.168.178.20] ([83.171.171.129]) by mail.gmx.com (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1N5VHG-1k615T0QdW-016vn7 for ; Mon, 07 Dec 2020 19:19:09 +0100 Subject: Re: Deferrable FK not behaving as expected. To: pgsql-general@lists.postgresql.org References: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> From: Thomas Kellerer Message-ID: Date: Mon, 7 Dec 2020 19:19:07 +0100 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666 MIME-Version: 1.0 In-Reply-To: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:UEf0OiQMkaYYI2PVwn+DNQTHg7cDS19oflPrPNp/RqujQYnDrNG SeCZjdOeZY17VdYKDSHAsunbJyoD3tQHSGk1DFaLC9AqVEtfG6L62ewjEaxQPormPf33qkL SM/1EOC5Xs+G5FxHnddI3UAuZAVboZvIP8+E5/OJ/we0vbi6Awgv6MbqiqEAssSDhW8wcsg FDwoKxCs7Ut1ErK/2lFRg== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:wYwtYIt1dDU=:LqU2SDTtuBvr0/2p9BmslO 890n6EB6RrWqoq41+TKtDEG6km2TrovWHC1rmUkTgBp7CozK9utZ6i7urjFjSaN3Ju03X5nRu ZgQEYIn27iCfhpG3bFQqztZGDGLCR8f2JKuu9AwVDsWvM3AFbfzmd1UEYT8CrUXnPkv7riywO SbBD33PmFJYKs83hPk053ueWd4QqBFwwMYpIvBkFl0IV2EMsr+L+HfbwjnSh6re8UdcQO1HXh dHDHDaWQJPMbcDNglWBfVc2+l5Z5KZaJAIwmj0gEK67VcthHmMPKmhN32c8DZzQW2ATlEWOdK xwJOJB8RavMInt49VhYTvdMSGatuflDoAkRk7mRftD9HRnKNStsBzZGuq6RJMaCgySL6nuRvT B8l62xdkCW1P/lQRgrUE0UNirddk/9pOsYkrKVAPFtc2TSuDAJWBCS4P3H86drn2n87gF1xTC 6k+f7tMtWtr8lroqZqpfBu/CaQqBdJfwPESj/R0jibLh4PEAnh0EXr/Z1r1Cm4OGMz8xgHgqd c1b+f/Gw/L+KUJ5+vvBBnDyIaMhQZkB7JDriN3mi49Ep3ypxdchZA5aXje0iro9r4Im5Nwdtp w5mfLde1wktkAYjDt8Cn8QRSU8V95/w8nP4Xy5YrkqTWeMmQtGn2HuOg5TAd/OTKOEwobicaD 214OskgR+XRHglG21BmjHyIxdqGr9gzyBfzB6oPRaffftjzkMf7uWoI1BCkELdDnzzwJgbMAT hMvJ7qxnXfH/xxzezKQaLq/Ppw5ezMdDLVN2q6y2Q8w8lgPRX4lv4/u8HCZ0cBhqBBHhEYy8G 9jI7jgLJggfzPyR3vhHJEBFqLa+EgONPDNU//1CM9ar1/K1lZ1dATI1cGh3tLXAFFffygFGVS ZbrtEjQQy+wN6FQhM3uQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Ron schrieb am 07.12.2020 um 19:15: > Referenced by: > =C2=A0=C2=A0=C2=A0 TABLE "sales_detail" CONSTRAINT "fk_sales_detail_sal= es_header" > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FOREIGN KEY (cus= t_id, order_ts) REFERENCES sales_header(cust_id, order_ts) *DEFERRABLE* I think if you only mention "deferrable" this is the same as "deferrable i= nitially immediate", so you will need to make them deferrable in your transaction: SET CONSTRAINTS ALL DEFERRED; or create the constraint with deferrable initially deferred Thomas