From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Renan Alves Fonseca <renanfonseca@gmail.com>,
        David Rowley <dgrowleyml@gmail.com>,
        Evgeny Morozov <postgresql4@realityexists.net>,
        pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Querying one partition in a function takes locks on all partitions
In-reply-to: <f29565cb8b0cea781e6334c94c89d9b575a8023a.camel@cybertec.at>
References: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com> <CAN_p2Qi_zie4aZqMcYuT-Y5CxyvUJ+nBAomQNJSPbBjrCPEXJw@mail.gmail.com> <01020195dd9e8337-c912f36d-54ef-4d3c-a00d-5e697b308d33-000000@eu-west-1.amazonses.com> <CAN_p2QghEHKeyTvb6Gc8F3y6wNm+dJXaCdrAnrV7TEq15Oh-pg@mail.gmail.com> <CAApHDvp3EDrVhGjmb21bceJ5-Y7iXKOn2UGG3-ngp_9ob_mpLw@mail.gmail.com> <CAN_p2QjXkBzyx55QAEGLfvWF0_UdusvxNmTykq_6jQsaaqjwTw@mail.gmail.com> <f29565cb8b0cea781e6334c94c89d9b575a8023a.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Mon, 31 Mar 2025 19:08:02 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4119651.1743443913.1@sss.pgh.pa.us>
Date: Mon, 31 Mar 2025 13:58:33 -0400
Message-ID: <4119652.1743443913@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4119652.1743443913%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2025-03-31 at 18:41 +0200, Renan Alves Fonseca wrote:
>> I would add another Note below like:
>> "Except when inlined, an SQL function is always executed with a
>> generic plan. This behavior may not be desired in some situations, and
>> it will be fixed in future versions."

> But that is not true, as far as I can tell: just like any other prepared
> statement, it may keep using custom plans.

No, it is a generic plan in the current code.  See functions.c's
init_execution_state, which calls pg_plan_query without passing
any boundParams.  That's kind of necessary in the current scheme
of things, because the SQL function holds onto the plan for the
duration of the current query and re-uses the plan during subsequent
calls with (possibly) different parameter values.  So it's sort of
a poor man's plan cache, but implemented in about the stupidest
way possible.  Jacking that up and rolling the plancache.c
infrastructure underneath has been on my to-do list for years.

			regards, tom lane