MIME-Version: 1.0
References: 
 <CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=Li6KNMqA@mail.gmail.com>
 <CA+HiwqGM6nvAV5O+=Nr+BXMPWOma0oeCRVzVP0XiLE8zX5TVAg@mail.gmail.com>
 <CA+HiwqGMaovCUgDbGxVGnK0Mrivr+ph3YE2Ws+47-ugyPb4f7g@mail.gmail.com>
 <CAFj8pRDaiBe_GOLk_yyYHTtPiDAAaLOM8u1-=Q3ZgXBTH+1igg@mail.gmail.com>
 <CA+HiwqGA5Ay_MR0eJEEbt4j6WrVh4F+AasTp8yCbs5aJLOJn6Q@mail.gmail.com>
 <CAEG8a3JM=NoqiTK0V6S9FNxZPvy1+C5F7rfafTtPKBVWnunL-g@mail.gmail.com>
 <CA+HiwqEyiLCY6MTLbOJXDdLNNQLaURYHvdW797MQgbjEK9od4Q@mail.gmail.com>
 <CAEG8a3+VBpwPf1Rm-ECD90whM9b3YnGhux5CVXdsL6khiBfzRQ@mail.gmail.com>
 <CA+HiwqF2UHzF0sKCp-F2a-U29rqh_9ZPy=f1h+Fh_=M8efj3pg@mail.gmail.com>
 <CAEG8a3L9Ew-WL8sxLROVOcypeaENPmd8qCmMvz4geoGL1TDGCA@mail.gmail.com>
 <CAEG8a3+nUFQo4sdPQF9xy0J73J8RFJ5U9A5+_kMosGDaZ+1sXA@mail.gmail.com>
 <2BE661BA-D909-4093-BF78-DB9B0C099337@gmail.com>
 <CAEG8a3JyKdizWvYsF+z_mA1BKy=dpW11iKVMOG=bk6Tbz6M1Bw@mail.gmail.com>
 <CAEG8a3+Hf4tvvbts29_k_AFhWQmRYfEo_SW4C5FY_140iKghBw@mail.gmail.com>
 <CA+HiwqFV-PY-3BxM6j5TaAiC3AwedDxo-6vwRSbvygg3zF+xAQ@mail.gmail.com>
 <CA+HiwqHpaisS-e+0gAgzh92qZAFxncAJMmmTRZZN=efoeTPgOg@mail.gmail.com>
 <CA+HiwqFwZ6WLRbY8R7VC7JVp5Jot6ktZOkr9wDxTjoK=W1SgdQ@mail.gmail.com>
 <CA+HiwqF_Kz=R8juHJBiOATvabWSOugK4VaGOxoJ_n=E2c7UM9w@mail.gmail.com>
 <CA+HiwqHCB7kcbspkhaLN9enoj5x=ehzhFM4PXDgWUUP8Px41GA@mail.gmail.com>
 <77FA04FE-1F84-4DA1-8855-8BBFD8CC889A@gmail.com>
 <CA+HiwqHpVtP485wEKuXdOkdoZWhvVvfFH40-og07Jp3MPx21eg@mail.gmail.com>
 <CAEG8a3JWHkJSXe9nNcVK7wnYKUEqWuMGFDhy5BynB_9tEjmEUg@mail.gmail.com>
 <CA+HiwqFjfumKrWy03q5M309xJJVYt0WgGfH6AZ8BjFhSwppwsQ@mail.gmail.com>
 <CAEG8a3JjP1LaKSv-r3AMJLRyLMzENJrKshWsDvDouMPM_sizmA@mail.gmail.com>
 <CA+HiwqFQ+ZA7hSOygv4uv_t75B3r0_gosjadetCsAEoaZwTu6g@mail.gmail.com>
 <CA+HiwqHdB0r8z6Mut13BxpYNq2W-os+Arju4mcZbCyU9PeaVog@mail.gmail.com>
 <CAEG8a3K5ayVNkCDnK9OtNb+4OY0chJtW6ObgEOSFjqyymQda8Q@mail.gmail.com>
 <CA+HiwqGJYCgEs_F-LBtrRdx-Y969LMr-OVogjFXU6U-0q5bOwQ@mail.gmail.com>
 <CA+HiwqF2Ma6R_QyjfPBvFreYbezFpGcwASJE0a2bM+Y0jvof+g@mail.gmail.com>
 <C3ACBAE1-CC5F-4688-9828-4668CDE37F83@gmail.com>
 <CA+HiwqGFRAH2O5bGpNMErFopFyn-2-Zu3+5y+BFQim9TE8z+Pw@mail.gmail.com>
 <C2133B47-79CD-40FF-B088-02D20D654806@gmail.com>
 <CA+HiwqFx=aciJYkkaviyTutUm303QXz6GtXSqzG7nfd4MAzddQ@mail.gmail.com>
 <CA+HiwqH39QU7vGVx65JH1e0nzVvQc5eVmuY7=qyj0T_+b-HO3A@mail.gmail.com>
 <72AA2663-B642-4FB1-BDC2-5FAFF2D2DF15@gmail.com>
In-Reply-To: <72AA2663-B642-4FB1-BDC2-5FAFF2D2DF15@gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Tue, 7 Apr 2026 11:12:15 +0900
Message-ID: 
 <CA+HiwqF+jAyHUiNzpR+vRBbpeCiVAdFU52-ffTGko9Zit317oA@mail.gmail.com>
Subject: Re: Eliminating SPI / SQL from some RI triggers - take 3
To: Chao Li <li.evan.chao@gmail.com>
Cc: Junwang Zhao <zhjwpku@gmail.com>, Haibo Yan <tristan.yim@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Tomas Vondra <tomas@vondra.me>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BHiwqF%2BjAyHUiNzpR%2BvRBbpeCiVAdFU52-ffTGko9Zit317oA%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 7, 2026 at 10:46=E2=80=AFAM Chao Li <li.evan.chao@gmail.com> wr=
ote:
> > On Apr 6, 2026, at 17:45, Amit Langote <amitlangote09@gmail.com> wrote:
> > On Fri, Apr 3, 2026 at 6:39=E2=80=AFPM Amit Langote <amitlangote09@gmai=
l.com> wrote:
> >> On Fri, Apr 3, 2026 at 5:58=E2=80=AFPM Chao Li <li.evan.chao@gmail.com=
> wrote:
> >>> I spent several hours debugging this patch today, and I found a probl=
em where the batch mode doesn't seem to handle deferred RI triggers, althou=
gh the commit message suggests that it should.
> >>>
> >>> I traced this scenario:
> >>> ```
> >>> CREATE TABLE pk (a int primary key);
> >>> CREATE TABLE fk (a int references pk(a) DEFERRABLE INITIALLY DEFERRED=
);
> >>> BEGIN;
> >>> INSERT INTO fk VALUES (1);
> >>> INSERT INTO pk VALUES (1);
> >>> COMMIT;
> >>> ```
> >>>
> >>> When COMMIT is executed, it reaches RI_FKey_check(), where AfterTrigg=
erIsActive() checks whether afterTriggers.query_depth >=3D 0. But in the de=
ferred case, afterTriggers.query_depth is -1.
> >>>
> >>> From the code:
> >>> ```
> >>>        if (ri_fastpath_is_applicable(riinfo))
> >>>        {
> >>>                if (AfterTriggerIsActive())
> >>>                {
> >>>                        /* Batched path: buffer and probe in groups */
> >>>                        ri_FastPathBatchAdd(riinfo, fk_rel, newslot);
> >>>                }
> >>>                else
> >>>                {
> >>>                        /* ALTER TABLE validation: per-row, no cache *=
/
> >>>                        ri_FastPathCheck(riinfo, fk_rel, newslot);
> >>>                }
> >>>                return PointerGetDatum(NULL);
> >>>        }
> >>> ```
> >>>
> >>> So this ends up falling back to the per-row path for deferred RI chec=
ks at COMMIT, even though the intent here seems to be only to bypass the AL=
TER TABLE validation case, where batch callbacks would never fire, and MyTr=
iggerDepth is 0. So, maybe we can just check MyTriggerDepth>0 in AfterTrigg=
erIsActive().
> >>>
> >>> I tried the attached fix. With it, deferred triggers go through the b=
atch mode, and all existing tests still pass.
> >>
> >> I think you might be right.  Thanks for the patch.  It looks correct
> >> to me at a glance, but I will need to check it a bit more closely
> >> before committing.
> >
> > Thinking about this some more, your fix is on the right track but
> > needs a bit more work -- MyTriggerDepth > 0 is too broad since it
> > fires for BEFORE triggers too. I have a revised version using a new
> > afterTriggerFiringDepth counter that I'll push shortly.
> >
> > Added an open item for tracking in the meantime:
> > https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items#Open_Issues
>
> V2 looks good to me. Besides the normal cases, I also traced an abnormal =
case to verify that afterTriggerFiringDepth is always reset to 0:
> ```
> evantest=3D# begin;
> BEGIN
> evantest=3D*# INSERT INTO fk VALUES (2);
> INSERT 0 1
> evantest=3D*# commit;
> ERROR:  insert or update on table "fk" violates foreign key constraint "f=
k_a_fkey"
> DETAIL:  Key (a)=3D(2) is not present in table "pk".
> ```

Thanks for checking.  Pushed.

--=20
Thanks, Amit Langote