Add a record then delete account blocks the scheduled tasks

[johnvanbreda]

If a record is added, then before the cache builder gets a chance to create the cache records, the account is deleted, then the insert of cache_occurrences_functional never happens as it relies on an inner join to users where deleted=false. This means the spatial index work queue tasks gets stuck in the queue causing other scheduled tasks to get blocked at this point and never proceed.

[johnvanbreda]

There is no need to limit to undeleted users in this join, as we want the record to appear in the cache anyway and the data fetched from the user table at this point has no privacy implications.

[JimBacon]

I've just been trying to unblock scheduled tasks because records were absent from cache_occurrences_functional. These were the occurrences where sample_id in (23102630, 23121128, 23126525, 23126526).

It is true, that the 2 users concerned, 69864 and 122944, are deleted. However, the users were last updated on 8/11/2022 and 17/6/2016, suggesting to me that we were being sent records by users that were already deleted. Yes, user 69864 submitted sample 23121128 at 2023-07-29 13:48:51 and sample 23126525 came 3 hours later, so I don't think it was a race condition.

[johnvanbreda]

All the examples are samples from an app - so an account may have been deleted from the main website, but the user continued using the app?