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 tbl (
  id            text PRIMARY KEY
, top           bigint  NOT NULL
, top_timestamp bigint  NOT NULL
);

INSERT INTO tbl VALUES
  ('some-id', 121, 888)
, ('next-id', 100, 666)
;

CREATE TABLE

INSERT 0 2

-- optimized function
CREATE OR REPLACE FUNCTION f_upsert_tbl(_id text, _top bigint, _top_timestamp bigint, OUT old_row tbl)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      SELECT *
      FROM   tbl
      WHERE  id = _id
      AND    top >= _top   -- new value isn't bigger
      INTO   old_row;

      EXIT WHEN FOUND;

      INSERT INTO tbl AS t
             ( id,  top,  top_timestamp)
      VALUES (_id, _top, _top_timestamp)
      ON     CONFLICT (id) DO UPDATE
      SET    top = EXCLUDED.top                      -- !
           , top_timestamp = EXCLUDED.top_timestamp  -- !
      WHERE  t.top < EXCLUDED.top                    -- new value is bigger
      RETURNING (SELECT t_old FROM tbl t_old
                 WHERE  t_old.id = t.id
                 AND    t.xmax <> 0                  -- actually was an UPDATE!
                ).*
      INTO   old_row;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;

CREATE FUNCTION

-- returns old row & updates
SELECT * FROM f_upsert_tbl('some-id', 123, 999);
-- returns old row & does NOT update (again)
SELECT * FROM f_upsert_tbl('some-id', 123, 999);

TABLE tbl;

id
top
top_timestamp

some-id
121
888

SELECT 1

id
top
top_timestamp

some-id
123
999

SELECT 1

id
top
top_timestamp

next-id
100
666

some-id
123
999

SELECT 2

-- insert new id: returns null values
SELECT * FROM f_upsert_tbl('new-id', 1, 1);
-- update: returns old row
SELECT * FROM f_upsert_tbl('new-id', 2, 2);
-- NO update, becuase top isn't bigger: also returns old row
SELECT * FROM f_upsert_tbl('new-id', 2, 2);

TABLE tbl;

id
top
top_timestamp

null
null
null

SELECT 1

id
top
top_timestamp

new-id
1
1

SELECT 1

id
top
top_timestamp

new-id
2
2

SELECT 1

id
top
top_timestamp

next-id
100
666

some-id
123
999

new-id
2
2

SELECT 3

-- only get selected columns:
SELECT id, top FROM f_upsert_tbl('new-id', 3, 3);

id
top

new-id
2

SELECT 1