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 1kmNt8-0005NH-46 for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:17:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmNt7-0002nT-3S for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:17:41 +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 1kmNt5-0002nL-I8 for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:17:40 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kmNt1-0001Zj-M2 for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:17:39 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 6D5DD5C009F; Mon, 7 Dec 2020 16:17:33 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Mon, 07 Dec 2020 16:17:33 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= subject:to:references:from:message-id:date:mime-version :in-reply-to:content-type:content-transfer-encoding; s=fm1; bh=K 11ktOa0bueXtW9v9iPGwxid8NXK3O0uTLnzQpzLnWg=; b=Ua94Fjzwc2/OxAQEM zhyAhHlfs4a8k/H8h9j+ZW25OcUgh52RonDkijuSiTneiYNI5rJz40eQriwxwyY/ /X5Lcinsj/3vp7tKtszyvgrHryfEK+/HO691bzQphUqxrehr/w8K4j1OUhrwgzzt 3U3kUWTxqqRITX9eyx8NqIqMTGNau6tRVLwI22rYvWIE1vJJmphBhkk7tX7aK3Mu +eK4bgf85IZnt565UnO0XCCi0edxBGfqLSQZQ8dAoZ500sa2DsS1enakrmxxfPcq NrAKcmgLsri+155FowyyvQWudmgRkEnLZwEYoJTOgFaLeMWLZiZQh2RYVMoSSo/J lEfKw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=K11ktOa0bueXtW9v9iPGwxid8NXK3O0uTLnzQpzLn Wg=; b=QDcehv+YU/8IFK9JuGC/eedc4BfpQy9xZ7nF5vj4tBbrnt1dBPNqlSYFR 4uewgzW6P1pDBtabq7kI70S6CuXs6GeUEDw/PQSmGNee2p6jWXhxO+4B0K7+uP/J Ea/jDxjH4LwywCMs9qq0DC+tLpTrwECM6IpsErCn4YvzlVLtVCaouK70nct1NPrq a52hOb77lZw4HbZlYXby20mT8hIXlODc1fPP5wrVxzo2Vg5R6GEcFWqa/oCGowp0 M+/THAGz9gZsM3jPheO3gELfuof/r6C/rjA/43tQzjJdTPmBUwI8E94FWy1auhb0 DGC4ACNR1d/xxrhdNs7q+13E+9N/A== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrudejgedgudegjecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepuffvfhfhkffffgggjggtgfesth ekredttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeduudefje etveffhefgudehtdfhvdetkeeiueehfeevveduteekleeuheegudehhfenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecukfhppeejhedrudejvddruddrieelnecuve hluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghn rdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Received: from [192.168.1.4] (75-172-1-69.tukw.qwest.net [75.172.1.69]) by mail.messagingengine.com (Postfix) with ESMTPA id BE043108005C; Mon, 7 Dec 2020 16:17:32 -0500 (EST) Subject: Re: Deferrable FK not behaving as expected. To: Ron , pgsql-general@lists.postgresql.org References: <75fe0761-a291-86a9-c8d8-4906da077469@gmail.com> <3c32cc9c-1589-7f22-b9e3-f4bcccbaf87e@gmail.com> <3144564.1607369236@sss.pgh.pa.us> <76fc2246-e88f-a97d-c601-8e68cf45e6dc@gmail.com> From: Adrian Klaver Message-ID: Date: Mon, 7 Dec 2020 13:17:31 -0800 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.5.1 MIME-Version: 1.0 In-Reply-To: <76fc2246-e88f-a97d-c601-8e68cf45e6dc@gmail.com> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 12/7/20 1:15 PM, Ron wrote: > On 12/7/20 1:27 PM, Tom Lane wrote: >> Ron 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 --- > > Do I need to file a bug report? No: https://www.postgresql.org/message-id/3144850.1607369633%40sss.pgh.pa.us > >>   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 worked. > -- Adrian Klaver adrian.klaver@aklaver.com