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
