By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table command(
command_id int,
active boolean,
claim_id uuid,
execution_heartbeat timestamptz,
sid int,
started_on timestamptz,
status int,
tenant_id int);
insert into command (command_id,sid,status) select n,n,0
from generate_series(1,15)_(n) returning *;
create table debug_ as
select clock_timestamp(), command_id, claim_id from command limit 0;
CREATE TABLE
| command_id | active | claim_id | execution_heartbeat | sid | started_on | status | tenant_id |
|---|---|---|---|---|---|---|---|
| 1 | null | null | null | 1 | null | 0 | null |
| 2 | null | null | null | 2 | null | 0 | null |
| 3 | null | null | null | 3 | null | 0 | null |
| 4 | null | null | null | 4 | null | 0 | null |
| 5 | null | null | null | 5 | null | 0 | null |
| 6 | null | null | null | 6 | null | 0 | null |
| 7 | null | null | null | 7 | null | 0 | null |
| 8 | null | null | null | 8 | null | 0 | null |
| 9 | null | null | null | 9 | null | 0 | null |
| 10 | null | null | null | 10 | null | 0 | null |
| 11 | null | null | null | 11 | null | 0 | null |
| 12 | null | null | null | 12 | null | 0 | null |
| 13 | null | null | null | 13 | null | 0 | null |
| 14 | null | null | null | 14 | null | 0 | null |
| 15 | null | null | null | 15 | null | 0 | null |
INSERT 0 15
SELECT 0
do $do$ declare client_op text:=$dyn_sql$
copy (select) to program $sh$ psql -c '
start transaction; select pg_sleep(2);
WITH cte AS (
UPDATE command AS c
SET execution_heartbeat =clock_timestamp(),
claim_id = gen_random_uuid()
FROM (
SELECT c0.command_id, c0.active, c0.claim_id, c0.execution_heartbeat, c0.sid, c0.started_on, c0.status, c0.tenant_id
FROM command AS c0
WHERE c0.status = 0 AND (c0.claim_id IS NULL)
ORDER BY c0.sid
LIMIT 1 --@__p_0
) AS t
WHERE c.command_id = t.command_id
RETURNING c.command_id,c.claim_id)
INSERT INTO debug_ SELECT clock_timestamp(),command_id,claim_id FROM cte;
select pg_sleep(2);
commit;' & $sh$;
$dyn_sql$;
i int;
begin
for i in 1..9 loop
execute client_op;
end loop;
end $do$;
DO
select pg_sleep(13);
select * from debug_;
select * from command;
| pg_sleep |
|---|
SELECT 1
| clock_timestamp | command_id | claim_id |
|---|---|---|
| 2025-02-14 02:21:14.190458+00 | 1 | 4eedd922-d63a-4157-ab2f-d0aa30ada83e |
| 2025-02-14 02:21:16.198135+00 | 1 | 6d132812-c875-4009-a342-878d612d6c9a |
| 2025-02-14 02:21:18.201204+00 | 1 | f93030bc-0eef-468f-8407-69f494bb6301 |
| 2025-02-14 02:21:20.204292+00 | 1 | ded860ea-4cbb-4b93-9092-a420bddc0441 |
| 2025-02-14 02:21:22.207713+00 | 1 | 44c9038b-a9ee-4460-a043-bdda3c6dda0e |
SELECT 5
| command_id | active | claim_id | execution_heartbeat | sid | started_on | status | tenant_id |
|---|---|---|---|---|---|---|---|
| 2 | null | null | null | 2 | null | 0 | null |
| 3 | null | null | null | 3 | null | 0 | null |
| 4 | null | null | null | 4 | null | 0 | null |
| 5 | null | null | null | 5 | null | 0 | null |
| 6 | null | null | null | 6 | null | 0 | null |
| 7 | null | null | null | 7 | null | 0 | null |
| 8 | null | null | null | 8 | null | 0 | null |
| 9 | null | null | null | 9 | null | 0 | null |
| 10 | null | null | null | 10 | null | 0 | null |
| 11 | null | null | null | 11 | null | 0 | null |
| 12 | null | null | null | 12 | null | 0 | null |
| 13 | null | null | null | 13 | null | 0 | null |
| 14 | null | null | null | 14 | null | 0 | null |
| 15 | null | null | null | 15 | null | 0 | null |
| 1 | null | 44c9038b-a9ee-4460-a043-bdda3c6dda0e | 2025-02-14 02:21:22.207686+00 | 1 | null | 0 | null |
SELECT 15