MIME-Version: 1.0
From: Bernice Southey <bernice.southey@gmail.com>
Date: Mon, 6 Oct 2025 15:15:20 +0100
Message-ID: <CAEDh4nyqYHaG4dHT=p6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw@mail.gmail.com>
Subject: Are SQL-language function lock ordering deadlocks solved in
 PostgreSQL 18?
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nyqYHaG4dHT%3Dp6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw%40mail.gmail.com>
Precedence: bulk

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