MIME-Version: 1.0
References: 
 <CA+HiwqFGkMSge6TgC9KQzde0ohpAycLQuV7ooitEEpbKB0O_mg@mail.gmail.com>
 <CAA-aLv6NiPNMzeOv4g_bZdyzSvBHZLqabKv1vgrf8CmJHRqJ-w@mail.gmail.com>
 <178042284979.1017.17926732655528064040.pgcf@coridan.postgresql.org>
In-Reply-To: 
 <178042284979.1017.17926732655528064040.pgcf@coridan.postgresql.org>
From: Amit Langote <amitlangote09@gmail.com>
Date: Thu, 4 Jun 2026 09:25:51 +0900
Message-ID: 
 <CA+HiwqG52GWbG1Fcra1GEFoop-JrCSmU4UHhbFNVwEVXpjhprw@mail.gmail.com>
Subject: Re: generic plans and "initial" pruning
To: Ilmar Yunusov <tanswis42@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BHiwqG52GWbG1Fcra1GEFoop-JrCSmU4UHhbFNVwEVXpjhprw%40mail.gmail.com>
Precedence: bulk

Hi Ilmar,

On Wed, Jun 3, 2026 at 2:55=E2=80=AFAM Ilmar Yunusov <tanswis42@gmail.com> =
wrote:
>
> I looked at v13, focusing on apply/build status and relation-lock behavio=
r for
> reused generic plans after initial partition pruning.
>
> I used the v13 series from Amit's 2026-05-29 message, on origin/master at
> 4b0bf0788b066a4ca1d4f959566678e44ec93422.
>
> The series applies cleanly with git am, and git diff --check reports no
> issues.
>
> I first built with:
>
> ./configure --prefix=3D"$PWD/pg-install" --without-readline --without-zli=
b --without-icu
> make -s -j8
> make -s install
>
> make -C src/test/regress check
>
> passed; all 245 tests passed, including plancache and partition_prune.
>
> I also built a cassert/debug tree with:
>
> ./configure --prefix=3D"$PWD/pg-install" --without-readline --without-zli=
b --without-icu --enable-cassert --enable-debug 'CFLAGS=3D-O0 -g'
> make -s -j8
> make -s install
>
> and ran:
>
> make -C src/test/regress check
>
> which also passed; all 245 tests passed.
>
> For the lock behavior, I used a list-partitioned table with force_generic=
_plan.
> After the generic plan had been built and then reused, EXECUTE held only =
the
> matching child partition lock. For example, EXECUTE q(1) held only the
> following child lock:
>
> manual_prunelock_p1
>
> EXPLAIN EXECUTE behaved the same way on a reused generic plan; EXPLAIN EX=
ECUTE
> q(2) removed the other subplans and held only the following child lock:
>
> manual_prunelock_p2
>
> With enable_partition_pruning =3D off and a newly prepared statement, exe=
cuting
> the same SELECT held all child partition locks:
>
> manual_prunelock_p1, manual_prunelock_p2, manual_prunelock_p3
>
> I also ran a bounded cassert/debug stress check around plan invalidation.=
 It
> did 20 cycles where a child index was created and dropped before EXECUTE,=
 and
> 20 similar cycles before EXPLAIN EXECUTE. In each cycle, the first execut=
ion
> after invalidation/replanning held all child partition locks, and the nex=
t
> execution reusing the generic plan held only the matching child partition=
 lock.
> That matches my reading that the patch is reducing locks for reused gener=
ic
> plans, not for the execution that has to rebuild the plan.

Thanks for thorough testing.

> One behavior I wanted to confirm: prepared UPDATE execution still held al=
l
> child partition locks in my manual check, including on the second executi=
on
> where the generic plan was being reused.
>
> The test was:
>
> prepare upd(int, text) as
>   update stress_prunelock_p set b =3D $2 where a =3D $1;
>
> Then both:
>
> execute upd(3, 'updated-row-3');
>
> and an all-pruned value:
>
> execute upd(99, 'no-row');
>
> held:
>
> stress_prunelock_p1, stress_prunelock_p2, stress_prunelock_p3,
> stress_prunelock_p4
>
> pg_prepared_statements showed generic_plans increasing for this prepared
> statement, so this was not a custom-plan case.
>
> Is this expected for ModifyTable/result relations in v13, or did I miss a=
n
> eligibility condition that prevents pruning-aware locking from being used=
 for
> this prepared UPDATE case? I saw the recent firstResultRels discussion, b=
ut I
> was not sure whether those changes are intended only to make pruned
> result-relation initialization safe, or whether actual prepared DML execu=
tion
> is expected to see reduced child partition locking as well.

Yes, this is expected; the pruning-aware path currently only kicks in
for the portal strategy used by SELECT. I hadn't noticed that
UPDATE/DELETE ends up on a different strategy that bypasses the new
pruning-aware locking path. I need to think about how best to handle
this; the DML portal strategies defer executor startup to a later
point, so it may require some restructuring.

--=20
Thanks, Amit Langote