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

-- Configurations
CREATE TABLE config (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    current_revision_id INT
);

-- Have multiple revisions
CREATE TABLE revision (
    id INT PRIMARY KEY,
    config_id INT NOT NULL REFERENCES config(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    description VARCHAR,
    foo INT NOT NULL,
    bar BOOLEAN NOT NULL,
    deployed BOOLEAN NOT NULL DEFAULT FALSE
);

-- A configuration has one _current_ revision
ALTER TABLE config
  ADD CONSTRAINT current_revision_id_fk
  FOREIGN KEY (current_revision_id)
  REFERENCES revision(id);
  
-- Revisions are automatically numbered in a view
CREATE VIEW numbered_revision AS (
    SELECT *,
    row_number() OVER (
        PARTITION BY config_id
        ORDER BY created_at, id
    ) AS number
    FROM revision
);

-- Configurations have multiple 'public names'
CREATE TABLE public_name (
    id INT PRIMARY KEY,
    revision_id INT NOT NULL REFERENCES revision(id),
    name VARCHAR(100),
    
    UNIQUE (revision_id, name)
);

CREATE TABLE



CREATE TABLE



ALTER TABLE



CREATE VIEW



CREATE TABLE


INSERT INTO config (id, name) VALUES
  (17, 'config_foo'),
  (42, 'config_bar');
  
INSERT INTO revision (id, config_id, created_at, description, foo, bar) VALUES
  (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, TRUE),
  (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, TRUE),
  (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, FALSE);

INSERT INTO public_name (id, revision_id, name) VALUES
  -- public names for foo configuration, first draft
  (83, 11, 'some.name'),
  (84, 11, 'other.name'),
  -- public names for foo configuration, second draft
  (85, 19, 'revised.name'),
  (86, 19, 'other.name'),
  (87, 19, 'third.name'),
  -- public names for bar configuration, first draft;
  -- some of the names here are the same used by foo configurations
  (88, 23, 'some.name'),
  (89, 23, 'unique.name'),
  (90, 23, 'other.name');
  
-- Foo configuration has a current, published revision:
UPDATE config SET current_revision_id = 19 WHERE id = 17;
UPDATE revision SET deployed = TRUE WHERE id in (11, 19);

INSERT 0 2



INSERT 0 3



INSERT 0 8



UPDATE 1



UPDATE 2


SELECT
  c.name AS config,
  rev.number AS revision,
  rev.deployed,
  CASE WHEN c.current_revision_id = rev.id 
    THEN 'ACTIVE'
    ELSE ''
  END AS status,
  string_agg(p.name, ', ' ORDER BY p.name) AS names
FROM config c
JOIN numbered_revision AS rev ON c.id = rev.config_id
JOIN public_name p ON p.revision_id = rev.id
GROUP BY c.id, rev.id, rev.number , rev.deployed
ORDER BY c.id, rev.number;


config
revision
deployed
status
names


config_foo
1
t

other.name, some.name

config_foo
2
t
ACTIVE
other.name, revised.name, third.name

config_bar
1
f

other.name, some.name, unique.name



SELECT 3

config	revision	deployed	status	names
config_foo	1	t		other.name, some.name
config_foo	2	t	ACTIVE	other.name, revised.name, third.name
config_bar	1	f		other.name, some.name, unique.name