PostgreSQL, как генерировать случайное количество строк поддельных данных с ограничениями внешнего ключа?

Я хочу заполнить test_in_suites случайными значениями, чтобы каждый набор содержал случайное число (от 3 до 7) тестов, равномерно выбранных из тестов.

Это звучит как хороший вариант использования бокового соединения...

INSERT INTO tests_in_suites(suite_id,test_id)
SELECT suites.id, t.id
FROM suites
CROSS JOIN LATERAL (SELECT id, suites.id AS lol FROM tests ORDER BY random() LIMIT (3+random()*4)) t;

Боковое соединение повторно вычисляет объединенную таблицу для каждой строки таблицы с левой стороны соединения, что нам и нужно. Но postgres оптимизирует это, если подзапрос к присоединенной таблице выглядит так, как будто он не является зависимым подзапросом. Поэтому я добавил suites.id в объединенную таблицу, чтобы создать впечатление, что объединенная таблица действительно зависит от строки набора таблиц.

Вероятно, есть способ сделать это с помощью array_agg() и unnest().

Решение, которое я нашел, было вдохновлено несколькими рецептами, которые я видел в Интернете (особенно когда речь идет об использовании row_number() для получения случайного выбора строк), но оно содержит мое собственное понимание, и я нигде не видел, чтобы этот подход использовался.

Ключевой компонент — разбить сложную задачу генерации случайных строк на ряд более простых задач, где на каждом шаге я генерирую только случайные целые числа. Затем, чтобы сгенерировать строки, я использую рекурсивное CTE и, наконец, использую JOIN в оконной функции (row_number()), чтобы объединить строки в мою таблицу результатов.

Приведенное ниже решение было протестировано на PostgreSQL 10 и 12, но оно должно работать на любой версии, поддерживающей рекурсивные CTE и оконные функции. Также должно быть легко адаптироваться к любой другой СУБД, которая их поддерживает.

-- For each suite, add a random number (between 3 and 7) of tests
-- mapped. Because it's difficult to join a random number of rows
-- between two tables in SQL without violating data dependency rules
-- and/or having the optimiser lift it out into a constant, repeating
-- pattern, instead we do it in several steps:
--
-- * For each suite ID, generate a random number between 3 and 7
--   representing the number of tests we want to include
-- * Then, using a recursive CTE, for each suite ID generate rows,
--   each with a random integer no larger than the number of
--   tests. Limit the number of rows to the small integer generated in
--   the previous step
-- * Join the table generated in the above CTE with tests on row
--   number, using the random int generated as the row number to
--   pick. This gives us a table containing three values: suite_id,
--   test_id, random row number. By extracting only the IDs, we have
--   now generate the values to insert into tests_in_suies
INSERT INTO tests_in_suites
-- "+ id*0" serves to ensure the optimiser sees a dependency on the
-- current row and doesn't lift the random() out as a constant
WITH s(id, n_tests) AS (SELECT id, 2 + ceil(random() * 5) + id*0 FROM suites),
cnt AS (SELECT COUNT(*) FROM tests),
t AS (SELECT id, row_number() over (ORDER BY random()) AS rn FROM tests),
sr AS (SELECT * FROM
       (WITH RECURSIVE subtests(sid, n, rn) AS (
             SELECT s.id, n_tests + 1, NULL::bigint FROM s
             UNION
             SELECT sid, n - 1, ceil(random() * (SELECT * FROM cnt))::bigint
             FROM subtests
             WHERE n > 1)
        SELECT * FROM subtests) x
        WHERE rn IS NOT NULL
        ORDER BY sid)
SELECT sid, t.id FROM sr JOIN t USING(rn)
ORDER BY sid, t.id
-- The above will process generate a couple duplicates. They're not a
-- big deal, so just skip them
ON CONFLICT DO NOTHING;


SELECT seen, total, seen / total::double precision as "fraction used" FROM
        (SELECT count(*) AS seen FROM (SELECT DISTINCT test_id FROM tests_in_suites) t) x,
        (SELECT count(*) AS total FROM tests) y;

SELECT suite_id, count(suite_id) FROM tests_in_suites GROUP BY suite_id;

SELECT * FROM tests_in_suites;

Скрипт БД