Postgres 12

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 12.8 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_ter 
(
  key_name   VARCHAR(128),

  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,  -- changed to "pos" - position is a keyword
                                  -- https://www.postgresql.org/docs/12/sql-keywords-appendix.html
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

  marker TEXT NOT NULL,

  key_alpha TEXT GENERATED ALWAYS AS (LEFT(key_name, STRPOS(key_name, '.'))) STORED,
  key_num   INT  GENERATED ALWAYS AS (SPLIT_PART(key_name, '.', 2)::INT) STORED,

  CONSTRAINT keys_ter_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ter_ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);
INSERT INTO keys_ter (key_name, context, pos, marker)
VALUES
('A.1', 'ctx_A', 0, 'marker 1 initial'), 
('A.2', 'ctx_A', 1, 'marker 2 initial'), 
('A.3', 'ctx_A', 2, 'marker 3 initial'), 
('A.4', 'ctx_A', 3, 'marker 4 initial'),
('B.1', 'ctx_B', 0, 'marker 5 initial'), 
('B.2', 'ctx_B', 1, 'marker 6 initial'), 
('B.3', 'ctx_B', 2, 'marker 7 initial'), 
('B.4', 'ctx_B', 3, 'marker 8 initial');
8 rows affected

SELECT * FROM keys_ter ORDER BY key_alpha, key_num;


key_name
context
pos
created_at
marker
key_alpha
key_num


A.1
ctx_A
0
2021-10-28 17:26:43.433905+01
marker 1 initial
A.
1

A.2
ctx_A
1
2021-10-28 17:26:43.433905+01
marker 2 initial
A.
2

A.3
ctx_A
2
2021-10-28 17:26:43.433905+01
marker 3 initial
A.
3

A.4
ctx_A
3
2021-10-28 17:26:43.433905+01
marker 4 initial
A.
4

B.1
ctx_B
0
2021-10-28 17:26:43.433905+01
marker 5 initial
B.
1

B.2
ctx_B
1
2021-10-28 17:26:43.433905+01
marker 6 initial
B.
2

B.3
ctx_B
2
2021-10-28 17:26:43.433905+01
marker 7 initial
B.
3

B.4
ctx_B
3
2021-10-28 17:26:43.433905+01
marker 8 initial
B.
4


BEGIN TRANSACTION;  -- INSERTING new record between pos at 2 new record - pos = 2
  UPDATE keys_ter
    SET 
      pos = pos + 1
  WHERE key_num > 2 AND context = 'ctx_A';              -- we know the key_num = 2!
  INSERT INTO keys_ter (key_name, context, pos, marker)
    VALUES('A.43', 'ctx_A', 2, 'marker_new_A.43');
COMMIT;
2 rows affected

1 rows affected

SELECT * FROM keys_ter ORDER BY context, pos;


key_name
context
pos
created_at
marker
key_alpha
key_num


A.1
ctx_A
0
2021-10-28 17:26:43.433905+01
marker 1 initial
A.
1

A.2
ctx_A
1
2021-10-28 17:26:43.433905+01
marker 2 initial
A.
2

A.43
ctx_A
2
2021-10-28 17:26:43.447281+01
marker_new_A.43
A.
43

A.3
ctx_A
3
2021-10-28 17:26:43.433905+01
marker 3 initial
A.
3

A.4
ctx_A
4
2021-10-28 17:26:43.433905+01
marker 4 initial
A.
4

B.1
ctx_B
0
2021-10-28 17:26:43.433905+01
marker 5 initial
B.
1

B.2
ctx_B
1
2021-10-28 17:26:43.433905+01
marker 6 initial
B.
2

B.3
ctx_B
2
2021-10-28 17:26:43.433905+01
marker 7 initial
B.
3

B.4
ctx_B
3
2021-10-28 17:26:43.433905+01
marker 8 initial
B.
4

key_name	context	pos	created_at	marker	key_alpha	key_num
A.1	ctx_A	0	2021-10-28 17:26:43.433905+01	marker 1 initial	A.	1
A.2	ctx_A	1	2021-10-28 17:26:43.433905+01	marker 2 initial	A.	2
A.3	ctx_A	2	2021-10-28 17:26:43.433905+01	marker 3 initial	A.	3
A.4	ctx_A	3	2021-10-28 17:26:43.433905+01	marker 4 initial	A.	4
B.1	ctx_B	0	2021-10-28 17:26:43.433905+01	marker 5 initial	B.	1
B.2	ctx_B	1	2021-10-28 17:26:43.433905+01	marker 6 initial	B.	2
B.3	ctx_B	2	2021-10-28 17:26:43.433905+01	marker 7 initial	B.	3
B.4	ctx_B	3	2021-10-28 17:26:43.433905+01	marker 8 initial	B.	4