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 wrote:
> Steve Baldwin writes:
> > Is there a chance that
Steve Baldwin 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
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 cl
Michael Lewis writes:
> If you move the limit 1 to the first CTE, does it not give you the same
> behavior in both versions?
Not sure if that's exactly the same, but certainly adding a traditional
optimization fence (OFFSET 0) to the first CTE should do the trick.
regards
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 versi
On Thu, Feb 11, 2021 at 5:07 PM Steve Baldwin
wrote:
> My 'dilemma' is that this functionality is packaged and the database it is
> bundled into could be running on a pre-12 version or 12+. Is there any way
> I can rewrite my view to achieve the same outcome (i.e. only creating 0 or
> 1 advisory
Hi,
I realise this is probably an edge case, but would appreciate some advice
or suggestions.
I have a table that has rows to be processed:
postgres=# create table lock_test (id uuid primary key default
gen_random_uuid(), lock_id bigint);
CREATE TABLE
postgres=# insert into lock_test (lock_id) v
