Task 3: Find duplicate tasks (by departure_station, arrival_station, departure_date, crawler_id) and propose a plan for safe removal from the harvester_tasks_queue table.

To find duplicates in the harvester_tasks_queue table based on the fields departure_station, arrival_station, departure_date, crawler_id, you can use the following SQL query:

SELECT departure_station, arrival_station, departure_date, crawler_id, COUNT(*) AS cnt
FROM harvester_tasks_queue
GROUP BY departure_station, arrival_station, departure_date, crawler_id
HAVING COUNT(*) > 1;

This will identify groups of records that are repeated.

Safe plan for deleting duplicates:

1. Create a backup of the table or database before deletion.
2. Define a criterion for keeping one of the duplicate records (e.g., the minimum or maximum id or creation date).
3. Use a CTE or subquery to delete all duplicates except one in each group. For example, if there is a unique identifier id:

WITH duplicates AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY departure_station, arrival_station, departure_date, crawler_id ORDER BY id) AS rn
  FROM harvester_tasks_queue
)
DELETE FROM harvester_tasks_queue
WHERE id IN (
  SELECT id FROM duplicates WHERE rn > 1
);

4. Check the deletion result to ensure only unique records remain.
5. If necessary, add a unique index on these fields to prevent future duplicates:

ALTER TABLE harvester_tasks_queue
ADD CONSTRAINT unique_task UNIQUE (departure_station, arrival_station, departure_date, crawler_id);

This approach ensures safe deletion of duplicates and maintains data integrity.