Fody keeps transactions open for too long #21
Description
While trying to debug a performance problem unrelated to Fody, but
involving a query in the events database, I ran into the problem that I
couldn't drop an index that I had created for testing purposes because
PostgreSQL needed a lock that was held by some other transaction. That
transaction turned out to be one of Fody's transactions. At least that
what seems most likely to me. There were two postgres processes that
were "idle in transaction" with a connection made from an apache
process, and restarting apache solved the problem.
My theory is that fody (at least the tickets_api) keeps its database
transactions open for too long. This seems obvious from the source code
which has no call to the commit method and the only calls of rollback
happen in case of errors.
While a transaction is running that has performed a SELECT on e.g. the
directives table, it's not possible to drop an index of that table as
long as that transaction has not been committed or rolled back. I have
tested that on my development database. I haven't investigated further
what kind of lock that is.