"Save code now" may fail with "OperationalError: database is locked"

SQLite only has some limited support for concurrency, and it looks like it's not enough for us, as it caused an error while @moranegg was doing a demo yesterday: https://sentry.softwareheritage.org/share/issue/7b8cc0d9f4134051979b9b8cb166605c/

My guess is, she clicked "Save code now" right when an other transaction was being committed to the DB.

---

Migrated from T2891 (view on Phabricator)

added System administration Web app labels

added priority:Normal label

I guess it is time to migrate the webapp database from SQLite to PostgreSQL.

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

I will check locally if the migration is straightforward or not.

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Are you sure that's an SQLite issue? Looking at the models declared in swh/web/common/models.py, there doesn't seem to be any index at all.

-> D4815

Thanks for the indexes ! Regarding the OperationalError, maybe we could mitigate it with a retry process as in the storage ?

Other solution, increase timeout value for waiting on the database lock.

This really feels like the wrong solution to the problem... postgres is the right one

Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Turns out the slow requests was not due to the database but rather an implementation flaw while retrieving a save requests page. The fix is short but I realized that code is not covered so more work to do before submitting a diff.

! In #2891 (closed), @anlambert wrote: Also the Save code now requests table is constantly growing and now contains more than 50000 lines, making the select requests quite slow.

Turns out the slow requests was not due to the database but rather an implementation flaw while retrieving a save requests page. The fix is short but I realized that code is not covered so more work to do before submitting a diff.

--> D4816

marked this issue as related to #2945 (closed)

! In #2891 (closed), @vlorentz wrote: This really feels like the wrong solution to the problem... postgres is the right one

I have created #2945 (closed) on the subject.

marked this issue as related to swh/devel/swh-web#3082

Heads up, the save code now feature is using a pgsql db now. So closing.

closed

mentioned in issue #2945 (closed)