add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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
FOR UPDATE SKIP LOCKED ---------------------explicit locking
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-01-10 19:37:47.061062+00 1 ae8839dd-2337-498c-801d-6e78ebddfcde
2025-01-10 19:37:47.061728+00 2 56a657a4-db61-447c-9302-27b6f1ae6268
2025-01-10 19:37:47.075121+00 3 6b0fd931-297e-46ea-9e92-73be3c2b146b
2025-01-10 19:37:47.078847+00 4 86921011-767f-4109-851f-d7d413e774e0
2025-01-10 19:37:47.079047+00 5 1c51c7db-4fc4-4887-9aa6-5450dd893532
2025-01-10 19:37:47.08718+00 6 91ba0cd7-44be-4413-8c53-4b691a0f5bbd
2025-01-10 19:37:47.091804+00 8 fe98622c-c3a4-450e-8fec-87cdca004f0a
2025-01-10 19:37:47.092002+00 7 e14ae601-80e7-41aa-b64e-3351843a8168
2025-01-10 19:37:47.095996+00 9 29e3d8e5-8471-44b0-8eb5-e51105cfbf7f 
SELECT 9
command_id active claim_id execution_heartbeat sid started_on status tenant_id
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 ae8839dd-2337-498c-801d-6e78ebddfcde 2025-01-10 19:37:47.061026+00 1 null 0 null
2 null 56a657a4-db61-447c-9302-27b6f1ae6268 2025-01-10 19:37:47.06171+00 2 null 0 null
3 null 6b0fd931-297e-46ea-9e92-73be3c2b146b 2025-01-10 19:37:47.07509+00 3 null 0 null
4 null 86921011-767f-4109-851f-d7d413e774e0 2025-01-10 19:37:47.078835+00 4 null 0 null
5 null 1c51c7db-4fc4-4887-9aa6-5450dd893532 2025-01-10 19:37:47.079036+00 5 null 0 null
6 null 91ba0cd7-44be-4413-8c53-4b691a0f5bbd 2025-01-10 19:37:47.087166+00 6 null 0 null
8 null fe98622c-c3a4-450e-8fec-87cdca004f0a 2025-01-10 19:37:47.091785+00 8 null 0 null
7 null e14ae601-80e7-41aa-b64e-3351843a8168 2025-01-10 19:37:47.091983+00 7 null 0 null
9 null 29e3d8e5-8471-44b0-8eb5-e51105cfbf7f 2025-01-10 19:37:47.095976+00 9 null 0 null 
SELECT 15