MIME-Version: 1.0
References: <CAEDh4nyqYHaG4dHT=p6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw@mail.gmail.com>
In-Reply-To: <CAEDh4nyqYHaG4dHT=p6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw@mail.gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Tue, 7 Oct 2025 14:51:12 -0600
Message-ID: <CAHyXU0wGxp0oLKU+65Su5-Q04Sz3=H2DZYK843XhguwEHvEkQA@mail.gmail.com>
Subject: Re: Are SQL-language function lock ordering deadlocks solved in
 PostgreSQL 18?
To: Bernice Southey <bernice.southey@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000061da56064097bbf5"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0wGxp0oLKU%2B65Su5-Q04Sz3%3DH2DZYK843XhguwEHvEkQA%40mail.gmail.com>
Precedence: bulk

--00000000000061da56064097bbf5
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Oct 6, 2025 at 10:54=E2=80=AFAM Bernice Southey <bernice.southey@gm=
ail.com>
wrote:

> Hi pgsql-general community,
>
> I tried the simple test from the original mail [1] and indeed got
> deadlocks in version 17, but not 18. Yet absence of evidence is not
> proof of no race conditions.
>
> This might be my favourite change in 18 [2], if I'm understanding it
> right. I much prefer the install-time checking of SQL-language
> functions, and the elegance of pure SQL. But I've been convinced the
> performance benefit of plan caching gave PL/pgSQL the edge. Now I'm
> not so sure.
>

It sure seems like the intent of the change was to modify exactly that
behavior.

If you look at the major built-in sql-esque languages, sql(old), sql(new),
PSM (new standard syntax) and pl/pgsql, I guess psm style and pure SQL
might be overlapped with PSM style functions, especially if PSM functions
can be inlined (I haven't looked yet)-- inlining of functions such as
'SELECT $1 + $2' being the only reason I write sql functions these days.
 I have always found the strict compile time checking to be highly
obnoxious except in the immutable/inlinable cases that come up now and
then.

Plan caching is not so important IMO, since execution time tends to greatly
exceed plan time in my experience and the real performance bugaboo is bad,
not uncached plans.   FIxing bad plans typically involves dipping into the
pl/pgsql toolbox, using variable temp tables, planner directives, and other
forbidden tools in PSM.  In simple terms, I also used to restrict server
side coding  views and pure sql functions for abstraction, but have long
since given up, and mainly write procedures :-).

I really wish PSM functions could do what pl/pgsql functions do, since I
like the creation syntax vs the 'string is body of code',but, alas.

merlin

--00000000000061da56064097bbf5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Oct 6, 2025 at 10:54=E2=80=AFAM B=
ernice Southey &lt;<a href=3D"mailto:bernice.southey@gmail.com">bernice.sou=
they@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_co=
ntainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi pgsql-general=
 community,<br><br>
I tried the simple test from the original mail [1] and indeed got<br>
deadlocks in version 17, but not 18. Yet absence of evidence is not<br>
proof of no race conditions.<br>
<br>
This might be my favourite change in 18 [2], if I&#39;m understanding it<br=
>
right. I much prefer the install-time checking of SQL-language<br>
functions, and the elegance of pure SQL. But I&#39;ve been convinced the<br=
>
performance benefit of plan caching gave PL/pgSQL the edge. Now I&#39;m<br>
not so sure.<br></blockquote><div><br></div><div>It sure seems like the int=
ent of the change was to modify exactly that behavior.=C2=A0</div><div><br>=
</div><div>If you look at the major built-in sql-esque languages, sql(old),=
 sql(new), PSM (new standard syntax) and pl/pgsql, I guess psm style and pu=
re SQL might=C2=A0be overlapped with PSM style functions, especially=C2=A0i=
f PSM functions can be inlined (I haven&#39;t looked yet)-- inlining of fun=
ctions=C2=A0such as &#39;SELECT $1=C2=A0+ $2&#39; being the only reason I w=
rite sql functions=C2=A0these days.=C2=A0 =C2=A0I have always found the str=
ict compile time checking to be highly obnoxious except in the immutable/in=
linable cases that come up now and then.=C2=A0</div><div><br></div><div>Pla=
n caching is not so important IMO, since execution time tends to greatly ex=
ceed plan time in my experience and the real performance bugaboo is bad, no=
t uncached plans.=C2=A0 =C2=A0FIxing bad plans typically involves dipping i=
nto the pl/pgsql toolbox, using variable temp tables, planner directives, a=
nd other forbidden tools in PSM.=C2=A0 In simple terms, I also used to rest=
rict server side coding=C2=A0 views and pure sql functions for abstraction,=
 but have long since given up, and mainly write procedures :-).</div><div><=
br></div><div>I really wish PSM functions could do what pl/pgsql functions =
do, since I like the creation syntax vs the &#39;string is body of code&#39=
;,but, alas.</div><div><br></div><div>merlin</div></div></div>

--00000000000061da56064097bbf5--