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.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE job
(
  job_id      SERIAL PRIMARY KEY,
  job_type_lu TEXT
);
CREATE TABLE phase 
(
  phase_id      SERIAL PRIMARY KEY,
  job_id_fk     INTEGER references job (job_id),
  phase_type_lu TEXT DEFAULT 'RESEARCH',
  
  CONSTRAINT phase_type_ck CHECK (phase_type_lu IN ('RESEARCH', 'SURVEY'))  
);
CREATE TABLE phase_step 
(
  phase_step_id SERIAL PRIMARY KEY,
  phase_id_fk   INTEGER REFERENCES phase (phase_id),
  step_type_lu  TEXT
);
CREATE OR REPLACE FUNCTION setup_phase_bis()
RETURNS TRIGGER AS 
$$
BEGIN
  INSERT INTO phase (job_id_fk) VALUES
    (NEW.job_id);
    RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;
CREATE TRIGGER setup_phase AFTER INSERT ON job
FOR EACH ROW EXECUTE PROCEDURE setup_phase_bis();
INSERT INTO job (job_type_lu) VALUES ('NT-TYP');
1 rows affected

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;


job_id
job_type_lu


1
NT-TYP




phase_id
job_id_fk
phase_type_lu


1
1
RESEARCH


CREATE OR REPLACE FUNCTION setup_phase_steps() 
RETURNS TRIGGER AS 
$$
BEGIN

  IF NEW.job_id_fk IS NULL THEN
    RAISE EXCEPTION 'You must supply a job_id for this phase...';
  END IF;

  IF (NEW.phase_type_lu = 'RESEARCH') THEN
    INSERT INTO phase_step (phase_id_fk,step_type_lu) VALUES
      (NEW.phase_id, 'RESEARCH: JOB_INPUT'),
      (NEW.phase_id, 'RESEARCH: WATER'),
      (NEW.phase_id,'RESEARCH: OTHER'),
      (NEW.phase_id,'RESEARCH: REVISION_ATEAM'),
      (NEW.phase_id,'RESEARCH: REVISION_CLIENT');
    RETURN NEW;
  END IF;
        
  IF (NEW.phase_type_lu = 'SURVEY') THEN
    INSERT INTO phase_step (phase_id_fk,step_type_lu) VALUES
      (NEW.phase_id,'SURVEY: SCHEDULING'),
      (NEW.phase_id,'SURVEY: FIELDING'),
      (NEW.phase_id,'SURVEY: REVISION_ATEAM'),
      (NEW.phase_id,'SURVEY: REVISION_CLIENT');
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE PLpgSQL;
CREATE TRIGGER setup_test_phase_steps AFTER INSERT ON phase
FOR EACH ROW execute procedure setup_phase_steps();
INSERT INTO job (job_type_lu) VALUES ('XXXXX');
1 rows affected

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;


job_id
job_type_lu


1
NT-TYP

2
XXXXX




phase_id
job_id_fk
phase_type_lu


1
1
RESEARCH

2
2
RESEARCH




phase_step_id
phase_id_fk
step_type_lu


1
2
RESEARCH: JOB_INPUT

2
2
RESEARCH: WATER

3
2
RESEARCH: OTHER

4
2
RESEARCH: REVISION_ATEAM

5
2
RESEARCH: REVISION_CLIENT


INSERT INTO phase (phase_type_lu) VALUES ('SURVEY');
ERROR:  You must supply a job_id for this phase...
CONTEXT:  PL/pgSQL function setup_phase_steps() line 5 at RAISE


INSERT INTO phase (job_id_fk, phase_type_lu) VALUES (2, 'SURVEY');
1 rows affected

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;


job_id
job_type_lu


1
NT-TYP

2
XXXXX




phase_id
job_id_fk
phase_type_lu


1
1
RESEARCH

2
2
RESEARCH

4
2
SURVEY




phase_step_id
phase_id_fk
step_type_lu


1
2
RESEARCH: JOB_INPUT

2
2
RESEARCH: WATER

3
2
RESEARCH: OTHER

4
2
RESEARCH: REVISION_ATEAM

5
2
RESEARCH: REVISION_CLIENT

6
4
SURVEY: SCHEDULING

7
4
SURVEY: FIELDING

8
4
SURVEY: REVISION_ATEAM

9
4
SURVEY: REVISION_CLIENT


INSERT INTO job (job_type_lu) VALUES ('YYYY');
1 rows affected

SELECT * FROM job;
SELECT * FROM phase;
SELECT * FROM phase_step;


job_id
job_type_lu


1
NT-TYP

2
XXXXX

3
YYYY




phase_id
job_id_fk
phase_type_lu


1
1
RESEARCH

2
2
RESEARCH

4
2
SURVEY

5
3
RESEARCH




phase_step_id
phase_id_fk
step_type_lu


1
2
RESEARCH: JOB_INPUT

2
2
RESEARCH: WATER

3
2
RESEARCH: OTHER

4
2
RESEARCH: REVISION_ATEAM

5
2
RESEARCH: REVISION_CLIENT

6
4
SURVEY: SCHEDULING

7
4
SURVEY: FIELDING

8
4
SURVEY: REVISION_ATEAM

9
4
SURVEY: REVISION_CLIENT

10
5
RESEARCH: JOB_INPUT

11
5
RESEARCH: WATER

12
5
RESEARCH: OTHER

13
5
RESEARCH: REVISION_ATEAM

14
5
RESEARCH: REVISION_CLIENT
phase_step_id	phase_id_fk	step_type_lu
1	2	RESEARCH: JOB_INPUT
2	2	RESEARCH: WATER
3	2	RESEARCH: OTHER
4	2	RESEARCH: REVISION_ATEAM
5	2	RESEARCH: REVISION_CLIENT