Re: Consequence of changes to CTE's in 12

Thanks all. The fact that this is a view is not really relevant. I only
bundled as a view here to make testing simpler. The underlying query still
behaves differently pre-12 and 12+.

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?

It makes me wonder what other queries we might have that are inadvertently
relying on the default materializing behaviour of pre-12.

Steve

On Fri, Feb 12, 2021 at 11:24 AM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> This functionality seems more a candidate for a set-returning function
> rather than a view, but I like my views to be side effect free and read
> only. It would be trivial to implement in plpgsql I believe.
>
> If you move the limit 1 to the first CTE, does it not give you the same
> behavior in both versions?
>
>>