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.
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
SELECTon e.g. thedirectivestable, 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.