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

CREATE TABLE test (
  entity_id  serial PRIMARY KEY
);

CREATE TABLE test_registration (
  entity_id  int REFERENCES test(entity_id)
, row_id     serial PRIMARY KEY
, entry_name text
, code       text
);

CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.entry_name := NEW.code;
   RETURN NEW;
END
$func$;

CREATE TRIGGER test_insert_trigger
BEFORE INSERT ON test_registration
FOR EACH ROW
WHEN (pg_trigger_depth() = 0
      AND NEW.entry_name IS DISTINCT FROM NEW.code)
EXECUTE FUNCTION test_registration_entry_name();
INSERT INTO test VALUES (333) RETURNING entity_id;


entity_id


333


INSERT INTO test_registration (entity_id, code)
VALUES (333,'EB')
RETURNING *;


entity_id
row_id
entry_name
code


333
1
EB
EB


-- better yet: do it in one query:
WITH ins_test AS (
   INSERT INTO test DEFAULT VALUES RETURNING entity_id
   )
INSERT INTO test_registration (entity_id, code)
SELECT entity_id, 'EB'
FROM   ins_test
RETURNING *;
   


entity_id
row_id
entry_name
code


1
2
EB
EB


-- This cannot return a row ever:
WITH create_query AS (
   INSERT INTO test_registration (entity_id,code) 
   VALUES (333,'14')
   RETURNING *
   )
SELECT v.entity_id, v.row_id, b.entity_id, b.entry_name, b.row_id 
FROM   create_query v
JOIN   test_registration b USING (row_id);
-- Even though the row was inserted:
TABLE test;
TABLE test_registration;


entity_id


333

1




entity_id
row_id
entry_name
code


333
1
EB
EB

1
2
EB
EB

333
3
14
14