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?.
/*
  Tables
*/

CREATE TABLE files (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);

CREATE TABLE folders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text
);

CREATE TABLE file_folders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  file_id bigint NOT NULL REFERENCES files,
  folder_id bigint NOT NULL REFERENCES folders,
  UNIQUE (file_id, folder_id)
);

CREATE TABLE



CREATE TABLE



CREATE TABLE


/*
  Helper functions.
*/

-- random string generator
-- https://www.simononsoftware.com/random-string-in-postgresql/#combined-md5-and-sql
CREATE FUNCTION random_string(length integer)
RETURNS text
LANGUAGE SQL
AS $body$ 
  SELECT left(
    md5(
      CAST (random() AS text)
    ), 
    length
  );
$body$;

-- random number generator
CREATE FUNCTION random_number(
  min integer DEFAULT 0, 
  max integer DEFAULT 10
) 
RETURNS integer
LANGUAGE SQL
AS $body$
  SELECT 
    floor(random() * (max - min + 1) + min)
$body$;

CREATE FUNCTION random_array_element(arr anyarray)
RETURNS anyelement
LANGUAGE SQL
AS $body$
  SELECT 
    arr[random_number(1, cardinality(arr))]
$body$;
  
--sequence generator
CREATE FUNCTION create_series(amount integer)
RETURNS TABLE (
  index_id bigint
)
LANGUAGE SQL
AS $BODY$
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, amount)
$BODY$;

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


/*
  Inserts
*/

INSERT INTO files
  (
    name
  )
SELECT
  random_string(10) AS name
FROM
  create_series(17)
;

INSERT INTO folders
  (
    name
  )
SELECT
  random_string(10) AS name
FROM
  create_series(13)
;

WITH input_files AS (
  SELECT
    id
  FROM
    files TABLESAMPLE BERNOULLI (50)
),
input_folders AS (
  SELECT
    id
  FROM
    folders TABLESAMPLE BERNOULLI (50)
),
input_file_folders AS (
  SELECT
    input_files.id AS file_id,
    input_folders.id AS folder_id
  FROM
    input_files
    CROSS JOIN
    input_folders
)
INSERT INTO file_folders
  (
    file_id,
    folder_id
  )
SELECT
  file_id,
  folder_id
FROM
  input_file_folders
;

SELECT
  folders.id,
  count(file_folders.file_id) AS files
FROM
  folders
  INNER JOIN
  file_folders
  ON
    folders.id = file_folders.folder_id
GROUP BY
  folders.id
ORDER BY
  folders.id
;

INSERT 0 17



INSERT 0 13



INSERT 0 44




id
files


3
11

5
11

6
11

12
11



SELECT 4


/*
  Merges
*/

WITH targets AS (
  SELECT 
    ARRAY (
      SELECT
        id
      FROM
        folders TABLESAMPLE BERNOULLI (50)
      LIMIT 3
    ) AS folders
),
-- basically a setup to ensure unique target/folder pairs
-- and no targets in the merges
input_folders AS (
  SELECT
    folders.id AS folder_id,
    random_array_element(targets.folders) AS target_id
  FROM
    folders
    CROSS JOIN
    targets
  WHERE
    NOT ( 
      folders.id = ANY (targets.folders)
    ) 
),
input_files AS (
  SELECT
    file_folders.id,
    file_folders.folder_id,
    file_folders.file_id,
    input_folders.target_id
  FROM
    input_folders
    INNER JOIN
    file_folders
    ON
      input_folders.folder_id = file_folders.folder_id
      OR
      input_folders.target_id = file_folders.folder_id
),
deleted_files AS (
    WITH deletions AS (
    SELECT
      inputs.id
    FROM
      input_files AS inputs
      INNER JOIN
      input_files AS targets
      ON
        NOT (inputs.folder_id = targets.target_id)
        AND
        inputs.file_id = targets.file_id
  )
  DELETE
  FROM
    file_folders
  WHERE
    id IN (
      SELECT
        id
      FROM
        deletions
    )
),
merged_files AS (
  WITH merges AS (
    SELECT
      inputs.id,
      inputs.folder_id,
      inputs.target_id
    FROM
      input_files AS inputs
      INNER JOIN
      input_files AS targets
      ON
        NOT (inputs.folder_id = targets.target_id)
        AND
        NOT (inputs.file_id = targets.file_id)
  )
  UPDATE file_folders
  SET
    folder_id = merges.target_id
  FROM
    merges
  WHERE
    merges.id = file_folders.id
),
deleted_folders AS (
  DELETE
  FROM
    folders
  WHERE
    id IN (
      SELECT DISTINCT
        folder_id
      FROM
        input_folders
    )
)
SELECT
  folders AS targets
FROM
  targets
;

SELECT
  folders.id,
  count(file_folders.file_id) AS files
FROM
  folders
  INNER JOIN
  file_folders
  ON
    folders.id = file_folders.folder_id
GROUP BY
  folders.id
ORDER BY
  folders.id
;

ERROR:  duplicate key value violates unique constraint "file_folders_file_id_folder_id_key"
DETAIL:  Key (file_id, folder_id)=(3, 2) already exists.