Consequence of changes to CTE's in 12

[Steve Baldwin <steve.baldwin@xxxxxxxxx>]

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) values (10),(10),(20),(30),(30),(30);
INSERT 0 6

postgres=#* select * from lock_test;

                  id                  | lock_id
--------------------------------------+---------
 326a2d34-ecec-4c01-94bb-40f43f244d40 |      10
 8ed1d680-6304-4fb2-a47c-9427c6d48622 |      10
 04482ba1-7193-4e7f-a507-71fe6a351781 |      20
 34003468-e959-4c7b-a48c-97195c43982e |      30
 2d20394b-c79b-4867-8d0a-72044c370543 |      30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |      30
(6 rows)

My business rule says I need to process rows by lock_id in descending order of the number of rows. In my test data, that would mean rows with a lock_id of 30 would be processed first.

If another 'processor' wakes up while lock_id 30 is being processed, it moves on to lock_id 10, etc.

My pre-12 solution was a view something like this:

postgres=# create or replace view lock_test_v

as

with g as (

  select lock_id, count(*) as n_rows

  from lock_test

  group by lock_id

  order by n_rows desc

  ), l as (

  select lock_id

  from g

  where pg_try_advisory_xact_lock(lock_id)

  limit 1)

select t.*

from lock_test as t

join l on t.lock_id = l.lock_id

;
CREATE VIEW

This works fine, and only creates one advisory lock (or zero) when querying the view:

postgres=# begin;
BEGIN
postgres=#* select classid, objid from pg_locks where locktype = 'advisory' and pid = pg_backend_pid();
 classid | objid
---------+-------
(0 rows)

postgres=#* select * from lock_test_v;
                  id                  | lock_id
--------------------------------------+---------
 34003468-e959-4c7b-a48c-97195c43982e |      30
 2d20394b-c79b-4867-8d0a-72044c370543 |      30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |      30
(3 rows)

postgres=#* select classid, objid from pg_locks where locktype = 'advisory' and pid = pg_backend_pid();
 classid | objid
---------+-------
       0 |    30
(1 row)

However in 12, the same view returns the same data, but generates multiple advisory locks:

sns_publisher=# begin;
BEGIN
sns_publisher=#* select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 12.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
(1 row)

sns_publisher=#* select classid, objid from pg_locks where locktype = 'advisory' and pid = pg_backend_pid();
 classid | objid
---------+-------
(0 rows)

sns_publisher=#* select * from lock_test_v;
                  id                  | lock_id
--------------------------------------+---------
 1a9f3f77-fcdc-4779-8fd9-30f274825e15 |      30
 ac670997-9c23-44da-8eb8-e055f02a5f19 |      30
 b5f939ac-7c7d-4975-811a-9af26aaa3a31 |      30
(3 rows)

sns_publisher=#* select classid, objid from pg_locks where locktype = 'advisory' and pid = pg_backend_pid();
 classid | objid
---------+-------
       0 |    20
       0 |    30
       0 |    10
(3 rows)

If I use 'as materialized' for my 'g' cte, I get the same outcome as with pre-12 versions.

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 locks) regardless of the server version? I realise I could have two installation scripts but if it is installed into a pre-12 DB and that DB is subsequently upgraded to 12+, my behaviour is broken.

Any suggestions greatly appreciated.

Steve