Re: Consequence of changes to CTE's in 12

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Steve Baldwin <steve(dot)baldwin(at)gmail(dot)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
>