public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bernice Southey <[email protected]>
To: [email protected]
Subject: Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18?
Date: Mon, 6 Oct 2025 15:15:20 +0100
Message-ID: <CAEDh4nyqYHaG4dHT=p6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw@mail.gmail.com> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18?
  In-Reply-To: <CAEDh4nyqYHaG4dHT=p6yfXh3CgTfKzvVPbm19ag-3oYuCB2PMw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox