Postgres 16

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 task_pool (
  task_id serial PRIMARY KEY
, order_id integer
, user_id integer
, expire_time timestamp
, status integer DEFAULT 0
);

INSERT INTO task_pool VALUES
  (50, 80, 1, '2024-03-01 11:52:44', -1)
, (51, 81, 1, '2024-03-01 16:00:44', -1)
, (52, 80, 2, '2024-04-19 12:31:22',  0)
, (53, 81, 4, '2024-03-01 16:52:44', -1)
, (54, 81, 6, '2024-03-01 16:51:44', -1)
RETURNING *;
   
CREATE TABLE reactivate_task (
  id serial PRIMARY KEY
, order_id integer
, amount_to_reactivate integer
);

INSERT INTO reactivate_task VALUES
  (100, 80, 3)
, (101, 81,	2)
RETURNING *;

CREATE TABLE




task_id
order_id
user_id
expire_time
status


50
80
1
2024-03-01 11:52:44
-1

51
81
1
2024-03-01 16:00:44
-1

52
80
2
2024-04-19 12:31:22
0

53
81
4
2024-03-01 16:52:44
-1

54
81
6
2024-03-01 16:51:44
-1



INSERT 0 5



CREATE TABLE




id
order_id
amount_to_reactivate


100
80
3

101
81
2



INSERT 0 2


UPDATE task_pool t
SET    status = 0
     , expire_time = current_timestamp + (interval '2 days')
FROM   reactivate_task r
CROSS  JOIN LATERAL (
   SELECT t1.task_id
   FROM   task_pool t1
   WHERE  t1.order_id = r.order_id
   AND    t1.status = -1
   ORDER  BY t1.expire_time
   LIMIT  r.amount_to_reactivate
   ) t1
WHERE t.task_id = t1.task_id
RETURNING *;


task_id
order_id
user_id
expire_time
status
id
order_id
amount_to_reactivate
task_id


50
80
1
2024-03-22 02:47:10.113596
0
100
80
3
50

51
81
1
2024-03-22 02:47:10.113596
0
101
81
2
51

54
81
6
2024-03-22 02:47:10.113596
0
101
81
2
54



UPDATE 3


TABLE task_pool ORDER BY task_id;


task_id
order_id
user_id
expire_time
status


50
80
1
2024-03-22 02:47:10.113596
0

51
81
1
2024-03-22 02:47:10.113596
0

52
80
2
2024-04-19 12:31:22
0

53
81
4
2024-03-01 16:52:44
-1

54
81
6
2024-03-22 02:47:10.113596
0



SELECT 5

task_id	order_id	user_id	expire_time	status
50	80	1	2024-03-01 11:52:44	-1
51	81	1	2024-03-01 16:00:44	-1
52	80	2	2024-04-19 12:31:22	0
53	81	4	2024-03-01 16:52:44	-1
54	81	6	2024-03-01 16:51:44	-1

task_id	order_id	user_id	expire_time	id	order_id	amount_to_reactivate	task_id
50	80	1	2024-03-22 02:47:10.113596	100	80	3	50
51	81	1	2024-03-22 02:47:10.113596	101	81	2	51
54	81	6	2024-03-22 02:47:10.113596	101	81	2	54