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 <t...@sss.pgh.pa.us> wrote: > Steve Baldwin <steve.bald...@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 >
