Thanks Tom. This optimization fences concept is a new one to me, so great to know about.
This does indeed give me a nice version-independent solution, and make me a very happy camper ;-)
Steve
On Fri, Feb 12, 2021 at 11:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Baldwin <steve.baldwin@gmail.com> writes: > Is there a chance that the query optimiser should 'notice' the > pg_try_advisory_xact_lock function, and not be so clever when it sees it?
The general policy with respect to volatile functions in WHERE quals is "here be dragons". You don't have enough control over when a WHERE clause will be evaluated to be sure about what the semantics will be; and we don't want to tie the optimizer's hands to the extent that would be needed to make it fully predictable.
In this particular case, you can make it fairly safe by making sure there are optimization fences both above and below where the WHERE clause is. You have one above from the LIMIT 1, but (with the new interpretation of CTEs) not one below it. Adding a fence -- either OFFSET 0 or LIMIT ALL -- to the first CTE should fix it in a reasonably version-independent fashion.
