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 1kmPQz-0000i2-Fn for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 22:56: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 1kmPQw-0001kI-I7 for pgsql-general@arkaria.postgresql.org; Mon, 07 Dec 2020 22:56:42 +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 1kmPQw-0001kB-8Y for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 22:56:42 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kmPQs-0008Vb-A9 for pgsql-general@lists.postgresql.org; Mon, 07 Dec 2020 22:56:40 +0000 Received: by mail-ot1-x329.google.com with SMTP id y24so14188603otk.3 for ; Mon, 07 Dec 2020 14:56:38 -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=6aGvxzCHS+/k/J5WkBkk2BXM3t7l03ZyzpTY8V5sIS4=; b=b3bjzzGZEPXa47UtdMHThvgl7sWw0zEDuBjcbEC/l+febXxsPDTkmREATHslx5IDkF N2s0VR3yj5a9DwIHPcACr7LJXQziTK7Y5FtrMVrixEE4drDYMpsQU3gFAZLNufYj/gFb y9gxKny+PNcSYqE2HdrZlFaZcRTv5774V5zhDosPlOLrAJ+PzbMBXrZSasrUyO4PcLHo izM03HMC6yavgCTLezZ1KF1zyW8RupijNfrhOcWEmbuiGKWqnanN1PJ36ofAiM2GstyK HaMEUBCCLesIvEgyhhX99cUiBYKzVwVY/fpWKRgWsBZDRao5K4Ny8ZWUcXSGdT7FGep5 t3fQ== 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=6aGvxzCHS+/k/J5WkBkk2BXM3t7l03ZyzpTY8V5sIS4=; b=d37ZLJSpzr88+doFu+SyqDr+pv3n8vRqQdh2rO/JLDiIJoefaGA6kodpj1VP4+7uHh NXkd8DVFkJbTVNxUfZI3GGEIKUY0SqNdBxLCqikcq/j9OrtC9cGRRTKvBZorBEkuldKP bwFT1J86KexAfAopaoKBk03ZPyWOPiS98BdZpQ7Mh9wL2QjIM4ATajxPJDLf55q4LdAE phnJPRXf24tSKNFsQXYjZ3Awx7Y8wlv85ARighMCvXVfMee5535A9SlhlYgiB9sQ0DTV hDl9oR6JLWWex/x7/y2teyHnhNhU1Sh5d3+PCk22zELhyEK/1ddER6sXIrkS71pDKVQ3 jNlA== X-Gm-Message-State: AOAM532wnn4KqX0ZsTPNuVj28siFTP5FB4uWmDwY33imndrSRJlOmb+A KLQzaTseEm/Yay3F1W1HB9/GaxRenC7z4Q== X-Google-Smtp-Source: ABdhPJz7VeObEHqACGLFSD9jt+snH6ThNo8B2BEUD50SD6zQCwhAa6OD/eouGYeCaKbi2swCjnrWaw== X-Received: by 2002:a9d:6208:: with SMTP id g8mr4937217otj.165.1607381797068; Mon, 07 Dec 2020 14:56: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 g3sm3284943oif.26.2020.12.07.14.56.36 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Dec 2020 14:56: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> <9c844ed1-e9e3-57c9-3f34-3b09479078e6@gmail.com> <3159381.1607377202@sss.pgh.pa.us> From: Ron Message-ID: <3e71cfa9-77d5-06c0-f435-9baffc55ea94@gmail.com> Date: Mon, 7 Dec 2020 16:56: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: <3159381.1607377202@sss.pgh.pa.us> 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 3:40 PM, Tom Lane wrote: > Ron writes: >> That works *sometimes*, but not when we *really* want it to work. >> test=# begin transaction; >> BEGIN >> test=# ALTER TABLE sales_header DETACH PARTITION sales_header_202001; >> ERROR:  removing partition "sales_header_202001" violates foreign key >> constraint "sales_detail_cust_id_order_ts_fkey" > That I think you're gonna have to live with. DETACH PARTITION isn't > something we can defer to end of transaction. Very sad, since it would simplify the archiving of the 24x365 system we're porting from Oracle to RDS Postgresql. -- Angular momentum makes the world go 'round.