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 |