| Version 1 (modified by , 3 weeks ago) ( diff ) |
|---|
This DML script simulates the real behavior of the application over time, rather than inserting all data at once. The key idea is that several parts of the system are interdependent, especially offer pricing, badges, and reviews.
The price of each offer is not purely random—it depends on the worker’s badge in the corresponding category. The badge itself is calculated based on the worker’s average ratings derived from reviews accumulated over time. In other words, there is a clear dependency chain: reviews → badges → offer price
Because of this dependency, the data cannot be generated in a single step. Instead, the script simulates the application timeline, where data is created in a realistic order:
TaskRequests are created first then Offers are generated one offer per task is accepted and Tasks are created Tasks are completed Reviews are added and finally, badges are recalculated
This process runs in monthly batches (e.g., February → March), allowing reviews to accumulate gradually and workers to progress naturally through badge levels.
For simplicity and performance reasons, badge recalculation is not triggered after every change (such as each new review). Instead, it is performed at the end of each month, which significantly reduces computational overhead while still preserving a realistic system behavior.
--++++++++++ helpers ++++++++++
CREATE TABLE female_names (
name VARCHAR(100)
);
CREATE TABLE male_names (
name VARCHAR(100)
);
CREATE TABLE male_surnames (
male_surname VARCHAR(100)
);
CREATE TABLE female_surnames (
female_surname VARCHAR(100)
);
CREATE TABLE cities (
city VARCHAR(25),
latitude DECIMAL,
longitude DECIMAL
);
--+++++++++++ location ++++++++++
INSERT INTO cities (city, latitude, longitude) VALUES
('Skopje', 41.9981, 21.4254),
('Bitola', 41.0314, 21.3347),
('Kumanovo', 42.1322, 21.7144),
('Prilep', 41.3451, 21.5550),
('Tetovo', 42.0097, 20.9716),
('Veles', 41.7156, 21.7756),
('Stip', 41.7458, 22.1958),
('Ohrid', 41.1231, 20.8016),
('Gostivar', 41.8000, 20.9167),
('Strumica', 41.4378, 22.6427),
('Kavadarci', 41.4331, 22.0119),
('Kocani', 41.9164, 22.4128),
('Kicevo', 41.5136, 20.9586),
('Struga', 41.1770, 20.6770),
('Radovis', 41.6383, 22.4647),
('Gevgelija', 41.1417, 22.5014),
('Debar', 41.5244, 20.5242),
('Kriva Palanka', 42.2009, 22.3317),
('Sveti Nikole', 41.8696, 21.9527),
('Negotino', 41.4839, 22.0892),
('Resen', 41.0889, 21.0122),
('Delcevo', 41.9672, 22.7694),
('Vinica', 41.8828, 22.5092),
('Berovo', 41.7031, 22.8578),
('Kratovo', 42.0784, 22.1800),
('Krusevo', 41.3689, 21.2489),
('Bogdanci', 41.2031, 22.5756),
('Demir Hisar', 41.2214, 21.2031),
('Demir Kapija', 41.4053, 22.2467),
('Pehcevo', 41.7633, 22.8892),
('Makedonska Kamenica', 42.0208, 22.5876),
('Probistip', 41.9985, 22.1786),
('Valandovo', 41.3174, 22.5619)
ON CONFLICT DO NOTHING;
INSERT INTO Location (city, latitude, longitude)
SELECT
'Skopje',
ROUND((41.97 + random() * 0.06)::numeric, 6),
ROUND((21.38 + random() * 0.08)::numeric, 6)
FROM generate_series(1, 4000)
ON CONFLICT DO NOTHING;
INSERT INTO Location (city, latitude, longitude)
SELECT
c.city,
ROUND((c.latitude - 0.03 + random() * 0.06)::numeric, 6),
ROUND((c.longitude - 0.05 + random() * 0.10)::numeric, 6)
FROM cities c
CROSS JOIN generate_series(1, 200)
WHERE c.city <> 'Skopje'
ON CONFLICT DO NOTHING;
-- ++++++++++ userAccount ++++++++++
CREATE OR REPLACE FUNCTION clean_text(input TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN TRANSLATE(input,
'абвгдѓежзѕијклљмнњопрстќуфхцчџшАБВГДЃЕЖЗЅИЈКЛЉМНЊОПРСТЌУФХЦЧЏШ', -- Cyrillic
'abvgdgezzsijkljmnjoprstkufhccdsABVGDGEZZSIJKLJMJNJOPRSTKUFHCCDS' -- Latin
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
WITH offset_cte AS (
SELECT COUNT(*) AS offset_val FROM UserAccount
),
all_data AS (
SELECT
name,
surname,
ROW_NUMBER() OVER () AS rn
FROM (
-- машки
SELECT clean_text(TRIM(m.name)) AS name,
clean_text(TRIM(s.surname)) AS surname
FROM male_names m
CROSS JOIN surnames s
UNION ALL
-- женски
SELECT clean_text(TRIM(f.name)) AS name, clean_text(TRIM(s.female_surname)) AS surname
FROM female_names f
CROSS JOIN female_surnames s
) t
ORDER BY random()
LIMIT 350000
)
INSERT INTO UserAccount (name, surname, email, phone_number, created_at)
SELECT
a.name,
a.surname,
LOWER(REGEXP_REPLACE(a.name, '[^a-zA-Z0-9]', '', 'g') || '.' ||
REGEXP_REPLACE(a.surname, '[^a-zA-Z0-9]', '', 'g') ||
(a.rn + o.offset_val) ||
'@gmail.com'),
'+3897'
|| (ARRAY['1','2','5','7','8'])[((a.rn + o.offset_val - 1) % 5) + 1]
|| LPAD((a.rn + o.offset_val)::text, 6, '0'),
TIMESTAMP '2017-01-01' + (random() * (NOW() - '2017-01-01'))
FROM all_data AS a
CROSS JOIN offset_cte AS o;
drop table female_names;
drop table female_surnames;
drop table male_names;
drop table surnames;
--++++++++++ client ++++++++++
INSERT INTO Client (user_id)
SELECT id
FROM UserAccount
WHERE id NOT IN (SELECT user_id FROM Client)
ORDER BY random()
LIMIT 100000;
--++++++++++ worker ++++++++++
WITH random_workers AS (
SELECT id, created_at, ROW_NUMBER() OVER (ORDER BY random()) AS rn
FROM UserAccount
WHERE id NOT IN (SELECT user_id FROM Client)
LIMIT 250000
),
random_locations AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY random()) AS rn
FROM Location
),
location_count AS (
SELECT COUNT(*) AS total FROM Location
)
INSERT INTO Worker (user_id, work_mode, service_radius_km, location_id, created_at)
SELECT
w.id,
(ARRAY['HYBRID','ONSITE','REMOTE'])[floor(random() * 3 + 1)::int],
15,
l.id,
w.created_at
FROM random_workers w
CROSS JOIN location_count lc
JOIN random_locations l ON ((w.rn - 1) % lc.total + 1) = l.rn;
--++++++++++ favourited ++++++++++
WITH worker_count AS (
SELECT COUNT(*) AS cnt FROM Worker
),
client_sample AS (
SELECT id, row_number() OVER () AS rn
FROM Client
ORDER BY random()
LIMIT 15000
),
worker_sample AS (
SELECT id, row_number() OVER () AS rn
FROM Worker
ORDER BY random()
LIMIT 15000
)
INSERT INTO Favourite (worker_id, client_id)
SELECT
w.id AS worker_id,
c.id AS client_id
FROM client_sample c
JOIN worker_sample w
ON w.rn = ((c.rn * 1000003) % 15000) + 1 -- prime number shuffle
ON CONFLICT DO NOTHING;
--++++++++++ category ++++++++++
CREATE TABLE category_staging (
category_name VARCHAR(100),
description VARCHAR(500),
parent_category_name VARCHAR(100)
);
INSERT INTO Category (category_name, description, parent_category_id)
SELECT category_name, description, NULL
FROM category_staging
WHERE parent_category_name IS NULL OR parent_category_name = ''
ON CONFLICT (category_name) DO NOTHING;
INSERT INTO Category (category_name, description, parent_category_id)
SELECT
s.category_name,
s.description,
p.id
FROM category_staging s
JOIN Category p ON p.category_name = s.parent_category_name
WHERE s.parent_category_name IS NOT NULL AND s.parent_category_name != ''
ON CONFLICT (category_name) DO NOTHING;
DROP TABLE category_staging;
--++++++++++ worker_category ++++++++++
--dodeluvame samo child categories
INSERT INTO WorkerCategory (worker_id, category_id)
SELECT worker_id, category_id
FROM (
SELECT
w.id AS worker_id,
c.id AS category_id,
ROW_NUMBER() OVER (
PARTITION BY w.id
ORDER BY random()
) AS rn
FROM Worker w
JOIN Category c
ON c.parent_category_id IS NOT NULL
) t
WHERE rn <= 3
ON CONFLICT DO NOTHING;
-- ++++++++++ badges ++++++++++
WITH tier_definitions AS (
SELECT * FROM (VALUES
(1, 'Beginner', 'Entry-level worker with basic experience in this category', 0.00, 0.20),
(2, 'Apprentice', 'Developing worker familiar with standard tasks in this category', 0.20, 0.40),
(3, 'Skilled', 'Competent worker with consistent positive ratings in this category', 0.40, 0.60),
(4, 'Advanced', 'Experienced worker handling complex tasks in this category', 0.60, 0.80),
(5, 'Expert', 'Top-rated professional trusted for high-demand work in this category', 0.80, 1.00)
) AS t(tier_level, tier_name, tier_description, min_factor, max_factor)
),
category_price_ranges AS (
SELECT * FROM (VALUES
('Home Cleaning', 5, 150),
('Plumbing', 20, 300),
('Electrical', 25, 400),
('General Handyman', 10, 250),
('Painting', 15, 350),
('Outdoor & Lawn', 10, 280),
('Moving & Hauling', 15, 250),
('Tech & Smart Home', 20, 350),
('Organization & Decluttering',10, 220),
('Errands & Delivery', 5, 150),
('Pet Care', 10, 200),
('Childcare & Tutoring', 10, 200),
('Cooking & Meal Prep', 10, 300),
('Vehicle & Auto', 10, 250),
('Administrative & Business', 10, 220),
('Remodeling & Renovation', 25, 600),
('Event & Party Setup', 10, 300),
('Personal Assistance', 10, 220)
) AS p(category_name, base_min, base_max)
)
INSERT INTO Badge (badge_name, description, category_id, min_price, max_price, tier_level)
SELECT
c.category_name || ' - ' || t.tier_name AS badge_name,
t.tier_description || ' (Category: ' || c.category_name || ')' AS description,
c.id AS category_id,
FLOOR(cpr.base_min + (cpr.base_max - cpr.base_min) * t.min_factor)::INT AS min_price,
FLOOR(cpr.base_min + (cpr.base_max - cpr.base_min) * t.max_factor)::INT AS max_price,
t.tier_level
FROM Category c
JOIN category_price_ranges cpr ON cpr.category_name = c.category_name
CROSS JOIN tier_definitions t
WHERE c.parent_category_id IS NULL -- only parent categories get badges
ORDER BY c.id, t.tier_level;
--++++++++++ worker_badge ++++++++++
-- not valid yet, needs review for tasks to calculate properly
--initially everyone gets begginer badge in their categories
INSERT INTO WorkerBadge (worker_id, badge_id, date_acquired,is_active)
SELECT DISTINCT
wc.worker_id,
b.id,
w.created_at,
true
FROM WorkerCategory wc
JOIN Worker w ON w.id = wc.worker_id
JOIN Category child ON child.id = wc.category_id
JOIN Badge b
ON b.category_id = child.parent_category_id
AND b.tier_level = 1
WHERE child.parent_category_id IS NOT NULL
ON CONFLICT (worker_id, badge_id) DO UPDATE
SET is_active = TRUE,
date_acquired = EXCLUDED.date_acquired;
--++++++++++ task requests ++++++++++
--5milioni taskrequests
DO $$
DECLARE
v_min_id BIGINT;
v_max_id BIGINT;
v_batch_size INT := 5000; -- clients per iteration
v_rows_per_cli INT := 50; -- task rows per client
v_offset BIGINT := 0;
v_batch_min BIGINT;
v_batch_max BIGINT;
v_rows_inserted BIGINT := 0;
BEGIN
SELECT MIN(id), MAX(id)
INTO v_min_id, v_max_id
FROM Client;
LOOP
v_batch_min := v_min_id + v_offset;
v_batch_max := v_min_id + v_offset + v_batch_size - 1;
EXIT WHEN v_batch_min > v_max_id;
INSERT INTO TaskRequest
(client_id, description, work_mode, status,
category_id, location_id, created_at)
SELECT
c.id,
td.task_description,
(ARRAY['HYBRID','ONSITE','REMOTE'])[floor(random()*3+1)::int],
'OPEN',
cat.id,
l.id,
u.created_at + INTERVAL '1 minute'
+ (random() * (TIMESTAMP '2026-01-01' - u.created_at - INTERVAL '1 minute'))
FROM Client c
JOIN UserAccount u ON c.user_id = u.id
-- only clients in this slice
JOIN generate_series(1, v_rows_per_cli) gs(n) ON TRUE
JOIN LATERAL (
SELECT task_description, category_name
FROM temp_task_desc
ORDER BY RANDOM()* gs.n
LIMIT 1
) td ON TRUE
JOIN Category cat ON cat.category_name = td.category_name
JOIN LATERAL (
SELECT id
FROM Location
ORDER BY RANDOM()* gs.n
LIMIT 1
) l ON true
WHERE c.id BETWEEN v_batch_min AND v_batch_max;
GET DIAGNOSTICS v_rows_inserted = ROW_COUNT;
RAISE NOTICE 'Batch offset=% | clients %-% | rows inserted=%',
v_offset, v_batch_min, v_batch_max, v_rows_inserted;
v_offset := v_offset + v_batch_size;
COMMIT;
END LOOP;
RAISE NOTICE 'Done. All batches complete.';
END;
$$ LANGUAGE plpgsql;
-- ================================================================
--helper procedura za badges
CREATE OR REPLACE PROCEDURE recalc_worker_badges(p_as_of TIMESTAMP, p_from TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
-- Само работници со нови reviews во овој batch
DROP TABLE IF EXISTS tmp_affected_workers;
CREATE TEMP TABLE tmp_affected_workers AS
SELECT DISTINCT o.worker_id
FROM Review r
JOIN Task t ON t.id = r.task_id
JOIN Offer o ON o.id = t.offer_id
WHERE r.created_at > p_from
AND r.created_at <= p_as_of;
CREATE INDEX ON tmp_affected_workers (worker_id);
IF NOT EXISTS (SELECT 1 FROM tmp_affected_workers) THEN
RETURN;
END IF;
-- ОПТ: GROUP BY оди директно — ист резултат, нема промена во логиката.
-- Не скенираме повеќе Review отколку што треба бидејќи
-- tmp_affected_workers е мал сет.
DROP TABLE IF EXISTS tmp_avg_ratings;
CREATE TEMP TABLE tmp_avg_ratings AS
SELECT
o.worker_id,
parent.id AS parent_category_id,
AVG(r.rating) AS avg_rating,
COUNT(*) AS review_count
FROM Review r
JOIN Task t ON t.id = r.task_id
JOIN Offer o ON o.id = t.offer_id
JOIN TaskRequest tr ON tr.id = o.task_request_id
JOIN Category child ON child.id = tr.category_id
JOIN Category parent ON parent.id = child.parent_category_id
WHERE o.worker_id IN (SELECT worker_id FROM tmp_affected_workers)
AND r.created_at <= p_as_of
GROUP BY o.worker_id, parent.id;
CREATE INDEX ON tmp_avg_ratings (worker_id, parent_category_id);
DROP TABLE IF EXISTS tmp_new_badges;
CREATE TEMP TABLE tmp_new_badges AS
SELECT DISTINCT ON (ar.worker_id, ar.parent_category_id)
ar.worker_id,
ar.parent_category_id,
b.id AS badge_id
FROM tmp_avg_ratings ar
JOIN Badge b ON b.category_id = ar.parent_category_id
ORDER BY
ar.worker_id,
ar.parent_category_id,
ABS(b.tier_level - ROUND((ar.avg_rating - 1) / 4.0 * 4 + 1));
CREATE INDEX ON tmp_new_badges (worker_id);
CREATE INDEX ON tmp_new_badges (badge_id);
-- Деактивирај стари badges
UPDATE WorkerBadge wb
SET is_active = FALSE
WHERE wb.is_active = TRUE
AND wb.worker_id IN (SELECT worker_id FROM tmp_affected_workers)
AND NOT EXISTS (
SELECT 1 FROM tmp_new_badges nb
WHERE nb.worker_id = wb.worker_id
AND nb.badge_id = wb.badge_id
);
-- Вметни/ажурирај точни badges
INSERT INTO WorkerBadge (worker_id, badge_id, date_acquired, is_active)
SELECT worker_id, badge_id, p_as_of::DATE, TRUE
FROM tmp_new_badges
ON CONFLICT (worker_id, badge_id) DO UPDATE
SET is_active = TRUE,
date_acquired = EXCLUDED.date_acquired;
END;
$$;
-------------------------------------------------------------------
DO $$
DECLARE
v_batch_start TIMESTAMP;
v_batch_end TIMESTAMP;
v_origin TIMESTAMP := TIMESTAMP '2023-02-01';
v_finish TIMESTAMP := TIMESTAMP '2023-03-01'; -- прошири колку сакаш, најполовно на 3-4 месеци,
v_cnt BIGINT;
v_tables_ready BOOLEAN := FALSE;
BEGIN
v_batch_start := v_origin;
LOOP
v_batch_end := v_batch_start + INTERVAL '1 month';
EXIT WHEN v_batch_start >= v_finish;
RAISE NOTICE '=== BATCH % → % ===', v_batch_start, v_batch_end;
-- ----------------------------------------------------------------
-- ОПТ 1: Создај temp табели САМО еднаш, потоа TRUNCATE
-- Избегнува каталошки lock + index rebuild на секоја итерација
-- ----------------------------------------------------------------
IF NOT v_tables_ready THEN
CREATE TEMP TABLE tmp_worker_sample (
category_id INT,
worker_id INT,
created_at TIMESTAMP
) ON COMMIT PRESERVE ROWS;
CREATE INDEX ON tmp_worker_sample (category_id, worker_id);
CREATE TEMP TABLE tmp_offers_to_make (
task_request_id INT,
worker_id INT,
worker_created TIMESTAMP,
tr_created TIMESTAMP,
client_created TIMESTAMP,
initiated_by TEXT,
price INT
) ON COMMIT PRESERVE ROWS;
CREATE INDEX ON tmp_offers_to_make (task_request_id);
CREATE TEMP TABLE tmp_batch_offers (
id INT,
task_request_id INT,
created_at TIMESTAMP
) ON COMMIT PRESERVE ROWS;
CREATE INDEX ON tmp_batch_offers (task_request_id);
CREATE INDEX ON tmp_batch_offers (id);
CREATE TEMP TABLE tmp_batch_tasks (
task_id INT,
updated_at TIMESTAMP,
worker_id INT,
worker_user_id INT,
client_user_id INT
) ON COMMIT PRESERVE ROWS;
CREATE INDEX ON tmp_batch_tasks (task_id);
v_tables_ready := TRUE;
ELSE
TRUNCATE tmp_worker_sample;
TRUNCATE tmp_offers_to_make;
TRUNCATE tmp_batch_offers;
TRUNCATE tmp_batch_tasks;
END IF;
-- ----------------------------------------------------------------
-- ОПТ 2: Земи max 20 работници по категорија ЕДНАШ
-- Истата логика, но RANDOM() се вика еднаш, не за секој TaskRequest
-- ----------------------------------------------------------------
INSERT INTO tmp_worker_sample (category_id, worker_id, created_at)
WITH numbered AS (
SELECT
wc.category_id,
w.id AS worker_id,
w.created_at,
ROW_NUMBER() OVER (
PARTITION BY wc.category_id
ORDER BY RANDOM()
) AS rn
FROM Worker w
JOIN WorkerCategory wc ON wc.worker_id = w.id
WHERE w.created_at < v_batch_end
)
SELECT category_id, worker_id, created_at
FROM numbered
WHERE rn <= 20;
-- ----------------------------------------------------------------
-- ОПТ 3: Генерирај парови (TaskRequest, Worker) ЕДНАШ во temp табела
-- Наместо да се прави lateral join двапати (worker + client offers)
-- со ORDER BY RANDOM() на секој ред — ова е главниот bottleneck.
-- Истиот број на offers (2–5 по TaskRequest), иста рандомност.
-- ----------------------------------------------------------------
INSERT INTO tmp_offers_to_make
(task_request_id, worker_id, worker_created, tr_created, client_created, initiated_by, price)
SELECT
tr.id,
w.worker_id,
w.created_at,
tr.created_at,
u.created_at,
'WORKER',
COALESCE(
FLOOR(RANDOM() * (b.max_price - b.min_price + 1) + b.min_price),
FLOOR(RANDOM() * (300 - 100 + 1) + 100)
)::INT
FROM TaskRequest tr
JOIN Client c ON c.id = tr.client_id
JOIN UserAccount u ON u.id = c.user_id
JOIN LATERAL (
-- ОПТ: tmp_worker_sample е веќе случаен → само земи 2–5
SELECT bw.worker_id, bw.created_at
FROM tmp_worker_sample bw
WHERE bw.category_id = tr.category_id
LIMIT (FLOOR(RANDOM()*4)+2) -- нема ORDER BY RANDOM() овде!
) w ON TRUE
JOIN Category child ON child.id = tr.category_id
JOIN Category parent ON parent.id = child.parent_category_id
LEFT JOIN WorkerBadge wb
ON wb.worker_id = w.worker_id
AND wb.is_active = TRUE
LEFT JOIN Badge b
ON b.id = wb.badge_id
AND b.category_id = parent.id
WHERE tr.status = 'OPEN'
AND tr.created_at >= v_batch_start
AND tr.created_at < v_batch_end;
-- ------------------------------------------------
-- 1. WORKER-INITIATED OFFERS
-- ------------------------------------------------
INSERT INTO Offer
(worker_id, task_request_id, price, offer_status, initiated_by, created_at)
SELECT
worker_id,
task_request_id,
price,
'PENDING',
'WORKER',
GREATEST(
tr_created + INTERVAL '1 minute',
worker_created + INTERVAL '1 hour',
client_created + INTERVAL '1 minute',
LEAST(
tr_created + (RANDOM() * INTERVAL '12 hours'),
v_batch_end - INTERVAL '1 hour'
)
)
FROM tmp_offers_to_make;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ' Worker offers inserted: %', v_cnt;
-- ------------------------------------------------
-- 2. CLIENT-INITIATED OFFERS (~50% од истите парови)
-- Ист ефект: RANDOM() < 0.5 филтер
-- ------------------------------------------------
INSERT INTO Offer
(worker_id, task_request_id, price, offer_status, initiated_by, created_at)
SELECT
worker_id,
task_request_id,
FLOOR(RANDOM() * (300 - 100 + 1) + 100)::INT,
'PENDING',
'CLIENT',
GREATEST(
tr_created + INTERVAL '1 minute',
worker_created + INTERVAL '1 hour',
client_created + INTERVAL '1 minute',
LEAST(
tr_created + (RANDOM() * INTERVAL '12 hours'),
v_batch_end - INTERVAL '1 hour'
)
)
FROM tmp_offers_to_make
WHERE RANDOM() < 0.5;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ' Client offers inserted: %', v_cnt;
-- ------------------------------------------------
-- 3. ACCEPT ЕДЕН OFFER PО TASK, REJECT ОСТАНАТИТЕ
-- ОПТ: Еден UPDATE со CASE наместо два одделни UPDATE
-- Ист резултат: 1 ACCEPTED, останатите REJECTED по TaskRequest
-- ------------------------------------------------
INSERT INTO tmp_batch_offers (id, task_request_id, created_at)
SELECT o.id, o.task_request_id, o.created_at
FROM Offer o
WHERE o.offer_status = 'PENDING'
AND o.created_at >= v_batch_start
AND o.created_at < v_batch_end;
WITH last_offer AS (
SELECT task_request_id,
MAX(created_at) AS last_offer_time
FROM tmp_batch_offers
GROUP BY task_request_id
),
ranked AS (
SELECT o.id,
o.task_request_id,
o.created_at AS offer_created_at,
lo.last_offer_time,
ROW_NUMBER() OVER (
PARTITION BY o.task_request_id
ORDER BY RANDOM()
) AS rn
FROM tmp_batch_offers o
JOIN last_offer lo ON lo.task_request_id = o.task_request_id
)
-- ОПТ: Еден UPDATE со CASE → половина I/O наспроти два UPDATE
UPDATE Offer o
SET
offer_status = CASE WHEN r.rn = 1 THEN 'ACCEPTED' ELSE 'REJECTED' END,
updated_at = CASE
WHEN r.rn = 1 THEN
GREATEST(
r.offer_created_at + INTERVAL '1 minute',
LEAST(
GREATEST(r.offer_created_at, r.last_offer_time)
+ (RANDOM() * INTERVAL '1 hour'),
v_batch_end - INTERVAL '1 hour'
)
)
ELSE
GREATEST(o.created_at,
GREATEST(
r.offer_created_at + INTERVAL '1 minute',
LEAST(
GREATEST(r.offer_created_at, r.last_offer_time)
+ (RANDOM() * INTERVAL '1 hour'),
v_batch_end - INTERVAL '1 hour'
)
)
)
END
FROM ranked r
WHERE o.id = r.id;
-- ------------------------------------------------
-- 4. CLOSE ACCEPTED TASK REQUESTS
-- ------------------------------------------------
WITH accepted_tasks AS (
SELECT DISTINCT task_request_id, updated_at
FROM Offer
WHERE offer_status = 'ACCEPTED'
AND created_at >= v_batch_start
AND created_at < v_batch_end
)
UPDATE TaskRequest tr
SET status = 'CLOSED',
updated_at = GREATEST(at2.updated_at, tr.created_at + INTERVAL '1 minute')
FROM accepted_tasks at2
WHERE tr.id = at2.task_request_id
AND tr.status = 'OPEN';
-- ------------------------------------------------
-- 5. CREATE TASKS
-- ------------------------------------------------
INSERT INTO Task (offer_id, status, created_at)
SELECT o.id,
'ACTIVE',
GREATEST(
o.updated_at,
o.created_at + INTERVAL '1 minute',
u.created_at + INTERVAL '1 minute'
)
FROM Offer o
JOIN TaskRequest tr ON tr.id = o.task_request_id
JOIN Client c ON c.id = tr.client_id
JOIN UserAccount u ON u.id = c.user_id
WHERE o.offer_status = 'ACCEPTED'
AND o.created_at >= v_batch_start
AND o.created_at < v_batch_end
AND NOT EXISTS (
SELECT 1 FROM Task t WHERE t.offer_id = o.id
);
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ' Tasks created: %', v_cnt;
-- ------------------------------------------------
-- 6. COMPLETE TASKS
-- ------------------------------------------------
UPDATE Task t
SET status = 'COMPLETED',
updated_at = GREATEST(
t.created_at + INTERVAL '1 hour',
LEAST(
t.created_at + (RANDOM() * INTERVAL '2 days'),
v_batch_end - INTERVAL '1 hour'
)
)
WHERE t.status = 'ACTIVE'
AND t.created_at >= v_batch_start
AND t.created_at < v_batch_end;
-- ------------------------------------------------
-- 7. REVIEWS
-- ------------------------------------------------
INSERT INTO tmp_batch_tasks
(task_id, updated_at, worker_id, worker_user_id, client_user_id)
SELECT
t.id,
t.updated_at,
o.worker_id,
w.user_id,
c.user_id
FROM Task t
JOIN Offer o ON o.id = t.offer_id
JOIN Worker w ON w.id = o.worker_id
JOIN TaskRequest tr ON tr.id = o.task_request_id
JOIN Client c ON c.id = tr.client_id
WHERE t.status = 'COMPLETED'
AND t.created_at >= v_batch_start
AND t.created_at < v_batch_end;
-- Client → Worker
INSERT INTO Review
(task_id, reviewer_id, reviewed_id, rating, created_at)
SELECT
bt.task_id,
bt.client_user_id,
bt.worker_user_id,
LEAST(5, GREATEST(1,
ROUND((random()+random()+random()+random())/4.0*4+1)::INT
)),
GREATEST(
bt.updated_at + INTERVAL '1 minute',
LEAST(
bt.updated_at + (RANDOM() * INTERVAL '12 hours'),
v_batch_end - INTERVAL '1 hour'
)
)
FROM tmp_batch_tasks bt
LEFT JOIN Review r ON r.task_id = bt.task_id
AND r.reviewer_id = bt.client_user_id
WHERE r.id IS NULL;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ' Reviews client->worker: %', v_cnt;
-- Worker → Client
INSERT INTO Review
(task_id, reviewer_id, reviewed_id, rating, created_at)
SELECT
bt.task_id,
bt.worker_user_id,
bt.client_user_id,
LEAST(5, GREATEST(1,
ROUND((random()+random()+random())/3.0*4+1)::INT
)),
GREATEST(
bt.updated_at + INTERVAL '1 minute',
LEAST(
bt.updated_at + (RANDOM() * INTERVAL '12 hours'),
v_batch_end - INTERVAL '1 hour'
)
)
FROM tmp_batch_tasks bt
LEFT JOIN Review r ON r.task_id = bt.task_id
AND r.reviewer_id = bt.worker_user_id
WHERE r.id IS NULL;
GET DIAGNOSTICS v_cnt = ROW_COUNT;
RAISE NOTICE ' Reviews worker->client: %', v_cnt;
-- ------------------------------------------------
-- 8. BADGE RECALC
-- ------------------------------------------------
CALL recalc_worker_badges(v_batch_end, v_batch_start);
RAISE NOTICE ' Badges updated.';
COMMIT;
v_batch_start := v_batch_end;
END LOOP;
RAISE NOTICE 'All batches done.';
END;
$$ LANGUAGE plpgsql;
Note:
See TracWiki
for help on using the wiki.
