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 bookids (book_id int, location_id bigint, checked_out_version int);
INSERT INTO bookids VALUES
  (12,  1, 10)
, (15,  1, 12)
, (12,  2,  0)  -- don't update this
, (66,  1,  0)  -- don't update this
RETURNING *;

CREATE TABLE




book_id
location_id
checked_out_version


12
1
10

15
1
12

12
2
0

66
1
0



INSERT 0 4


PREPARE upd1(int[], bigint[], int[]) AS
UPDATE bookids b
SET    checked_out_version = t.ver
FROM   unnest($1, $2, $3) AS t(book_id, loc_id, ver)
WHERE  b.location_id = t.loc_id
AND    b.book_id = t.book_id
RETURNING *  -- optional
;

PREPARE


EXECUTE upd1('{12,15}', '{1,1}', '{11,20}');


book_id
location_id
checked_out_version
book_id
loc_id
ver


12
1
11
12
1
11

15
1
20
15
1
20



UPDATE 2


TABLE bookids


book_id
location_id
checked_out_version


12
2
0

66
1
0

12
1
11

15
1
20



SELECT 4