The following bug has been logged on the website:
Bug reference: 16676
Logged by: David Christensen
Email address: david@endpoint.com
PostgreSQL version: 13.0
Operating system: OS X
Description:
Test case (found when demonstrating a queue mechanism for batches):
create table queue (id int generated always as identity, batch_id int not
null);
insert into queue (batch_id) values (1),(1),(2),(3);
postgres=# select * from queue;
id | batch_id
----+----------
1 | 1
2 | 1
3 | 2
4 | 3
(4 rows)
-- backend 1:
postgres=# begin;
BEGIN
postgres=*# select * from queue order by batch_id fetch first row with ties
for update skip locked;
id | batch_id
----+----------
1 | 1
2 | 1
(2 rows)
-- backend 2:
postgres=# select * from queue order by batch_id fetch first row with ties
for update skip locked;
id | batch_id
----+----------
4 | 3
(1 row)
-- QED
As you can see, the row id 3 with batch_id 2 is not returned as would be
implied by the strict ordering and the skipped locks. The working theory
here is the FETCH FIRST ROW WITH TIES locks the rows before deciding if they
should be included or not.
