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.
regards, tom lane