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
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?