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 1kmNrF-0005GL-Tm for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:15:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kmNrC-0006KP-DU for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 21:15:42 +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 1kmNrC-0006KH-4E for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:15:42 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kmNr9-0001Yf-RD for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 21:15:41 +0000 Received: by mail-ot1-x332.google.com with SMTP id y24so13931095otk.3 for ; Mon, 07 Dec 2020 13:15:39 -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=EEWeX9Jr4CBkf8WTeNtQLcD5I/6y8IlJShXV+l78Dfc=; b=FL5q2fvGCm09sfgj9nCf27hZIBB8IUSrP4kk+d5p7VWn6HlzSH5+80ViBpCub4Q2Cd AreZp7qxCMXsAnSkUsZbeO4WA04YzDfp2s3UZlR80dcCbxpgPlnPA4j07iuPkni7Pjlm /pS41ODUKv8ZBK9iYl3ftQZTTZj2yzyUgpwsI+52HDTr6g42+e11FJJyCWOnD5XNtQ8e 6ct+fus0UlUlwPI7jEyiMSwmyAagsv/0c0UB7glE30WWrq085rkh8ZGtOKyZZGY6xHpu K9cZu2WrDMeTv9+63ZtFCgZK2R2cVYiBRwA5b4mwp3TfQrLOvGl5tKj2Xf9EP7c13l7c 29Aw== 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=EEWeX9Jr4CBkf8WTeNtQLcD5I/6y8IlJShXV+l78Dfc=; b=ZWB5sjALUueyzVDnJkXX8NAbcYoL0EUImjNgv/33yYU8QMNWlWp+HA6nFtJ1Tj1LKf 76ahW84E0XQgE5QcJQpV96XgCYuHcyEgTlZvG5vG7SO1YJKvfTGMfzlwZWk2dAlOWO/x ngj2rTBn8XqyNTta5H7ha3n2uoGaVHF/165BWgfWpBRzlouSBGZgd5IbUaeBjoIYoYtK NoB5FVo/TwIeQ5+qpy6eHiattURm5RsndDuQ9er773v+lS01fMq9AzfPITEskxa3KRIh v6FxUcFwWxrrdLGxUKT2mFrdjRV+8FvubCHNeqesLVJ7nw8f60eQM8AzQbd/G8JvBqZh Wo1A== X-Gm-Message-State: AOAM533xGXbGPl7RZCJLo7l/TVO0OqXYoBHa0BMZDlAHM/U/i7I+92Fd tiOUlF21s7PdbDCizpu5cu3niYepz3YXRw== X-Google-Smtp-Source: ABdhPJxmcGbLGYEuCLR4BBh/X70z21WACkZ3wgSds7T00uMOEHYq3lsoA/URLmHAV/CSNa2iAnFWgA== X-Received: by 2002:a9d:73d1:: with SMTP id m17mr14204514otk.187.1607375737007; Mon, 07 Dec 2020 13:15:37 -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 s206sm1816758oie.24.2020.12.07.13.15.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Dec 2020 13:15:36 -0800 (PST) Subject: Re: Deferrable FK not behaving as expected. To: 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> From: Ron Message-ID: <76fc2246-e88f-a97d-c601-8e68cf45e6dc@gmail.com> Date: Mon, 7 Dec 2020 15:15:35 -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: <3144564.1607369236@sss.pgh.pa.us> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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? > 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. -- Angular momentum makes the world go 'round.