The creation of the spr_obsolete index is problematic for the PIP service and any of the admin lookup workers running in the importers.
Because there are always multiple processes doing admin lookup, and each one tries to create the index if it doesn't exist, most end up failing. SQLite only allows a single write lock on the DB, so all but one will fail to achieve it.
However, in my testing, all it takes to solve this gracefully is to wrap the index creation in a try/catch block. The index will be created by one of the worker processes, and all subsequent queries appear to have the performance improvements of the index (about 30-50% faster time to
load admin data).
The creation of the
spr_obsoleteindex is problematic for the PIP service and any of the admin lookup workers running in the importers.Because there are always multiple processes doing admin lookup, and each one tries to create the index if it doesn't exist, most end up failing. SQLite only allows a single write lock on the DB, so all but one will fail to achieve it.
However, in my testing, all it takes to solve this gracefully is to wrap the index creation in a try/catch block. The index will be created by one of the worker processes, and all subsequent queries appear to have the performance improvements of the index (about 30-50% faster time to load admin data).
Supersedes https://github.com/pelias/whosonfirst/pull/431 Fixes https://github.com/pelias/whosonfirst/issues/454 Connects https://github.com/pelias/whosonfirst/issues/453 (we should not actually remove the index creation quite yet, the
whosonfirst-data-latestfile does not have the index)