WorkBench generates non-performant queries when validating

[grantfitzsimmons]

Describe the bug
The WorkBench is generating non-performant database queries when validating datasets that contain a high number of one-to-many relationships (specifically Collectors/Agents/Preparations) mapped within a single row.

When a user maps approximately 10–16 Collectors to a single Collecting Event, the validator constructs a single SELECT statement containing dozens of INNER JOINs (referencing collector, agent, determination, etc.). This query fails to complete within a reasonable timeframe (observed running for 5+ hours on 2026-02-13 for herb_rbge), locking database resources and causing a hosting outage.

Attempting to cancel the import via the UI does not successfully kill the backend database process, leading to a "cycling" state where the UI attempts to recover but gets stuck in a loop, effectively locking the user out of the database.

To Reproduce
Steps to reproduce the behavior:

1. Create a Workbench data set for Collection Object.
2. Map Collecting Event > Collectors repeated 10 to 15 times (mapping 10+ distinct agents for a single event).
3. Include standard Determination and Preparation mappings.
4. Upload a dataset (row count appears less relevant than column complexity).
5. Run the Validator.
6. Observe that the progress bar stalls.
7. Attempt to close/cancel the import.
8. Result: The database experiences high load/outage; the query continues running in the background until manually killed by a sysadmin.

Upload Plan: herb_large_upload_plan.json

Expected behavior
The Workbench validator should handle complex one-to-many relationships efficiently, perhaps by batching validation checks or splitting queries, rather than constructing a single monolithic join that exceeds database timeout limits. Canceling an import in the UI should immediately terminate the associated database query.

Crash Report
The system hangs/times out.

Container logs at the time for specify7:

[13/Feb/2026 07:36:47] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:37:42] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:38:36] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:39:35] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:41:00] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:43:50] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:48:35] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 07:52:37] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/
[13/Feb/2026 08:06:53] [ERROR] [django.request:246] Service Unavailable: /api/workbench/abort/466/

Constructed Query:
The following query ran for 5 hours 16 min 27 seconds before being killed manually. Note the excessive joining of collector and agent tables (T12 through T73).

herb_rbge_2026_02_13.sql

[grantfitzsimmons]

This was encountered by KU Herpetology last week as well. It did not result in the server becoming unavailable, but the query did run for 17.5 hours. The solution here was also to split the data set and include fewer relationships:

KUHerps_LongRunningQuery.sql