Files
mtusi2026lab2/scripts/generate_100k.sql

43 lines
1023 B
PL/PgSQL

\set ON_ERROR_STOP on
BEGIN;
DO $$
BEGIN
IF (SELECT count(*) FROM chats c WHERE EXISTS (
SELECT 1 FROM chat_members cm
WHERE cm.chat_id = c.id AND cm.user_id = c.created_by
)) < 2 THEN
RAISE EXCEPTION 'Need at least two chats with creator membership';
END IF;
END $$;
WITH selected AS (
SELECT
c.id AS chat_id,
c.created_by AS sender_id,
ROW_NUMBER() OVER (ORDER BY c.created_at, c.id) AS rn
FROM chats c
WHERE EXISTS (
SELECT 1 FROM chat_members cm
WHERE cm.chat_id = c.id
AND cm.user_id = c.created_by
)
ORDER BY c.created_at, c.id
LIMIT 2
)
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
SELECT
gen_random_uuid(),
s.chat_id,
s.sender_id,
'Benchmark message ' || g.n,
NOW() - INTERVAL '1 day' + g.n * INTERVAL '1 millisecond'
FROM selected s
CROSS JOIN LATERAL generate_series(
1,
CASE WHEN s.rn = 1 THEN 90000 ELSE 10000 END
) AS g(n);
COMMIT;
ANALYZE messages;