public inbox for [email protected]
help / color / mirror / Atom feedAre SQL-language function lock ordering deadlocks solved in PostgreSQL 18?
2+ messages / 2 participants
[nested] [flat]
* Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18?
@ 2025-10-06 14:15 Bernice Southey <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Bernice Southey @ 2025-10-06 14:15 UTC (permalink / raw)
To: [email protected]
Hi pgsql-general community,
I recently ran into deadlocks in a SQL-language function when,
confusingly, a first-line advisory lock didn't prevent them. I
leveled-up my troubleshooting skills on this one, but of course it was
well explained a decade ago [1]. As best I understand it, the later
locks are acquired first, during the upfront full parsing of
SQL-language functions. The fix is to use PL/pgSQL.
I'm wondering if the change in 18 to "Improve SQL-language function
plan caching" [2] fixes this? "for old-style SQL functions, it will
now work much as it does with PL/pgSQL functions, because we delay
parse analysis and planning of each query until we're ready to run
it."
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.
Best regards, Bernice
[1] https://www.postgresql.org/message-id/flat/20150728162823.25043.27625%40wrigleys.postgresql.org
[2] https://postgr.es/c/0dca5d68d
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18?
@ 2025-10-07 20:51 Merlin Moncure <[email protected]>
parent: Bernice Southey <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Merlin Moncure @ 2025-10-07 20:51 UTC (permalink / raw)
To: Bernice Southey <[email protected]>; +Cc: [email protected]
On Mon, Oct 6, 2025 at 10:54 AM Bernice Southey <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-07 20:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-06 14:15 Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18? Bernice Southey <[email protected]>
2025-10-07 20:51 ` Merlin Moncure <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox