Postgres 13

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?.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,
    context    VARCHAR(128) NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    orderCol   INTEGER      NOT NULL DEFAULT (0)
);

CREATE INDEX Ix_Position ON keys (context, orderCol);
CREATE FUNCTION insertNew() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
  NEW.orderCol := COALESCE((SELECT MAX(orderCol) FROM keys WHERE context = NEW.context), 0) + 1; 
  return NEW;
END;
$$
CREATE TRIGGER checkOrderInsert
  BEFORE INSERT ON keys
  FOR EACH ROW EXECUTE PROCEDURE insertNew();
CREATE FUNCTION updateExisting() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
  
  IF NEW.orderCol < OLD.orderCol THEN
    UPDATE keys 
    SET orderCol = orderCol + 1 
    WHERE context = NEW.context 
          AND orderCol >= NEW.orderCol
          AND orderCol < OLD.orderCol;
  ELSEIF NEW.orderCol > OLD.orderCol THEN
    UPDATE keys 
    SET orderCol = orderCol - 1 
    WHERE context = NEW.context 
          AND orderCol > OLD.orderCol;
  END IF;
  return NEW;
END;
$$
CREATE TRIGGER checkOrderUpdate
  BEFORE UPDATE ON keys
  FOR EACH ROW 
  WHEN (pg_trigger_depth() < 1)
  EXECUTE PROCEDURE updateExisting();
INSERT INTO keys (key_name, context)
VALUES
('A.1', 'ctx_A'), ('A.2', 'ctx_A'), ('A.3', 'ctx_A'), ('A.4', 'ctx_A'),
('B.1', 'ctx_B'), ('B.2', 'ctx_B'), ('B.3', 'ctx_B'), ('B.4', 'ctx_B');

SELECT * FROM keys ORDER BY context, orderCol;
8 rows affected



key_name
context
created_at
ordercol


A.1
ctx_A
2021-10-28 21:35:23.900572+01
1

A.2
ctx_A
2021-10-28 21:35:23.900572+01
2

A.3
ctx_A
2021-10-28 21:35:23.900572+01
3

A.4
ctx_A
2021-10-28 21:35:23.900572+01
4

B.1
ctx_B
2021-10-28 21:35:23.900572+01
1

B.2
ctx_B
2021-10-28 21:35:23.900572+01
2

B.3
ctx_B
2021-10-28 21:35:23.900572+01
3

B.4
ctx_B
2021-10-28 21:35:23.900572+01
4


-- new > old  2 => 4
UPDATE keys SET orderCol = 4 WHERE context = 'ctx_A' AND orderCol = 2;

SELECT * FROM keys WHERE context = 'ctx_A' ORDER BY context, orderCol;
1 rows affected



key_name
context
created_at
ordercol


A.1
ctx_A
2021-10-28 21:35:23.900572+01
1

A.3
ctx_A
2021-10-28 21:35:23.900572+01
2

A.4
ctx_A
2021-10-28 21:35:23.900572+01
3

A.2
ctx_A
2021-10-28 21:35:23.900572+01
4


-- new < old 4 => 2
UPDATE keys SET orderCol = 2 WHERE context = 'ctx_B' AND orderCol = 4;

SELECT * FROM keys WHERE context = 'ctx_B' ORDER BY context, orderCol;
1 rows affected



key_name
context
created_at
ordercol


B.1
ctx_B
2021-10-28 21:35:23.900572+01
1

B.4
ctx_B
2021-10-28 21:35:23.900572+01
2

B.2
ctx_B
2021-10-28 21:35:23.900572+01
3

B.3
ctx_B
2021-10-28 21:35:23.900572+01
4

key_name	context	created_at	ordercol
A.1	ctx_A	2021-10-28 21:35:23.900572+01	1
A.2	ctx_A	2021-10-28 21:35:23.900572+01	2
A.3	ctx_A	2021-10-28 21:35:23.900572+01	3
A.4	ctx_A	2021-10-28 21:35:23.900572+01	4
B.1	ctx_B	2021-10-28 21:35:23.900572+01	1
B.2	ctx_B	2021-10-28 21:35:23.900572+01	2
B.3	ctx_B	2021-10-28 21:35:23.900572+01	3
B.4	ctx_B	2021-10-28 21:35:23.900572+01	4