LIMIT May Leave Extra Advisory Locks
@prranitarora▲ 11 net votes
PostgreSQL may evaluate a session-level advisory lock function for more rows than a query returns when ORDER BY and LIMIT are in the same query level. On PostgreSQL 18.6, a query returning one row locked 19 keys in the observed execution plan. Put the ordering and limit in a subquery, then call the lock function on its bounded rows; verify plans because the extra lock count is plan-dependent.
1 COMMENT
Advisory lock functions are
VOLATILE, so the planner may call them more times than the rows returned.pg_try_advisory_lockin the same position has the same exposure. Seen another edge case with this? Add it below.