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 1q5U27-0000iT-7W for pgsql-bugs@arkaria.postgresql.org; Sat, 03 Jun 2023 16:23:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q5U25-0001fU-Ry for pgsql-bugs@arkaria.postgresql.org; Sat, 03 Jun 2023 16:23:13 +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 1q5U25-0001fL-Df for pgsql-bugs@lists.postgresql.org; Sat, 03 Jun 2023 16:23:13 +0000 Received: from mail-qk1-x730.google.com ([2607:f8b0:4864:20::730]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q5U21-0000Jr-NJ for pgsql-bugs@lists.postgresql.org; Sat, 03 Jun 2023 16:23:12 +0000 Received: by mail-qk1-x730.google.com with SMTP id af79cd13be357-75d4094f9baso45005485a.1 for ; Sat, 03 Jun 2023 09:23:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=j-davis-com.20221208.gappssmtp.com; s=20221208; t=1685809387; x=1688401387; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=PUVV8GWilL1grbPHMJ4BXp/ox8pPUezgy5mpgaPmyFk=; b=eHKCC3S4D0JxRcon4Jr1Xsb21z8t8MA2ndX8/4Pzs1+Z0FA/mov1aOHd26RDkcclU1 a4haJUyesJGUCwUROSdUuop84pRgCykEJRN+ON0j37J5u1AqAeZsIq3GFL6PmcwI8uso Cgi60wTpqg41QK3Rp+s3bnAbWf14Uje+Bh5wzpZJXZrJXaxLB/guRnDc3WWOlmI+vmsC B0Sly+FxIofbYYIbJJCLRdYYDF3ummj6IbSkhot2BZjx5FtlG0WqSDd8tcE0aOdKs8pe zK493iZ3ZXQ6MJrjaHm2o0UnhNQ1BB9VSXtkN7llsaBUP8XyJwhtnwhv61M3gVLkdHj8 2sWg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685809387; x=1688401387; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=PUVV8GWilL1grbPHMJ4BXp/ox8pPUezgy5mpgaPmyFk=; b=fOLy65XRxZUkAwPEYDOaR1N7ByZDWS8aFLZLKYkPWy5k33nvoW//kSB0cHl49xse/Y piX2Gh7mt5h+HWbWxwZ1KdKqyPR5FupG7Sxx3uQTSkH4PfIyKZeBk/pjaVK7e0ki1Qz+ 0Mft+PejW/p8T5jhtdKCX1dZBC/ycG1BcpBlBVU6O0iJBaKLTwE/Egai0YdiTSkB1QWj 6fPYxRlA4nLV37CbFOJWJC5bqsoERnIrHgjQcpO9cfDPEKFOtdBBvShKh1KysdZD7eZI Fws7M7UG6xdltt1FBK7wXDNQPCDQdT6stPb/SYv2WJLpyJAdXCP32OKQSdIe465ueIj7 1o6A== X-Gm-Message-State: AC+VfDxW+TnReig3tc25JtudEIuigzKdnDJmQuAowEI+nAoJ1v0NzaOI JGtMI0d/2Z4pv3k7w90C+UW1NA== X-Google-Smtp-Source: ACHHUZ4wYe2cjZKIUNMv238HkiJCUL+OggUZycJSLzHdeESt1r3lHcJ2+FG2SpYABhETr1RU3KhHUA== X-Received: by 2002:a05:620a:470d:b0:75b:23a1:3661 with SMTP id bs13-20020a05620a470d00b0075b23a13661mr18062521qkb.34.1685809387582; Sat, 03 Jun 2023 09:23:07 -0700 (PDT) Received: from [10.75.14.145] (cpe00180a4e2dfd-cm00fc8dce3020.cpe.net.cable.rogers.com. [174.114.113.8]) by smtp.gmail.com with ESMTPSA id h17-20020a05620a10b100b0075cec860842sm2009686qkk.27.2023.06.03.09.23.06 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 03 Jun 2023 09:23:07 -0700 (PDT) Message-ID: <59f0c5d1812ed2b83381fd575c78907541f5cc08.camel@j-davis.com> Subject: Re: BUG #17945: Different order of definition of a constraint causes constraint violation From: Jeff Davis To: yannsalaun1@gmail.com, pgsql-bugs@lists.postgresql.org Date: Sat, 03 Jun 2023 09:23:06 -0700 In-Reply-To: <17945-e6eb09170cd63524@postgresql.org> References: <17945-e6eb09170cd63524@postgresql.org> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.44.4-0ubuntu1 MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2023-05-25 at 14:39 +0000, PG Bug reporting form wrote: > This is a bug report with a reproducible case where different orders > of > definition of a SQL constraint causes different behavior. Thank you for the report and repro! Yes, I believe this is a bug. > - Then delete main_dir (this sets t.main_dir_id and t.main_file_id to > NULL > via ON DELETE SET NULL) > DELETE FROM node WHERE id =3D 1; >=20 > -- The second DELETE statement returns the following error. >=20 > -- ERROR:=C2=A0 23503: insert or update on table "t" violates foreign key > constraint "t_main_file_id_fkey" > -- DETAIL:=C2=A0 Key (main_file_id)=3D(2) is not present in table "file". Here's what's happening:=20 DELETE FROM node WHERE id =3D 1; -- queues: DELETE FROM dir WHERE id =3D 1; -- queues: DELETE FROM file WHERE id =3D 1; -- queues: DELETE FROM node WHERE parent_id =3D 1; DELETE FROM dir WHERE id =3D 1; -- queues: UPDATE t SET main_dir_id =3D NULL WHERE main_dir_id =3D 1; DELETE FROM file WHERE id =3D 1; DELETE FROM node WHERE parent_id =3D 1; -- queues: DELETE FROM dir WHERE id =3D 2; -- queues: DELETE FROM file WHERE id =3D 2; -- queues: DELETE FROM node WHERE parent_id =3D 2; UPDATE t SET main_dir_id =3D NULL WHERE main_dir_id =3D 1; -- queues RI_FKey_check_upd for t.main_file_id DELETE FROM dir WHERE id =3D 2; DELETE FROM file WHERE id =3D 2; -- queues: UPDATE t SET main_file_id =3D NULL WHERE main_file_id =3D 2; -- queues: UPDATE t SET other_file_id =3D NULL WHERE other_file_id =3D 2; DELETE FROM node WHERE parent_id =3D 2; -- perform RI_FKey_check_upd for t.main_file_id, and fails The problem is that it's performing the check on the tuple in "t" before all of the pending actions have completed. In this particular case, we may be able to supress that check entirely, but I don't think that would be correct in general (e.g. overlapping FKs). If the constraints are declared in a different order, they end up queuing in a different order, and all of the SET NULL events fire before the check, so there's no error. One idea is to to have the check events re-queue themselves if there are still actions remaining in the queue. That might violate the rules about executing triggers in order by name, though. Regards, Jeff Davis