Migrating an SQLite based django app to PostgreSQL
stevelord.tumblr.com
stevelord.tumblr.com
SQLite doesn't provide much by way of schema changes, at least not in a manner that the Django ORM can make use of. I just run Postgres locally now, because I can be confident that any migrations I test locally will work on the production system.
When an embedded database is interacting with something like, say, Apache, which uses fork to create multiple processes, your locks have to be multi-process-safe as opposed to multi-thread-safe. For something that's designed with single-process, but multi-threaded access in mind, that can be a bit of a chore.
To resolve that you can pop up to the process tree, assuming you saved the PID along with the lock and see if that process still exists (and hope its PID hasn't been reused) and if not iterate over all locks held by that process and free them to break the deadlock, but that sort of overhead at something as fundamental as locking code can be annoying.
Also note that your semaphores then have to usually be implemented in assembly or you have to have a library that implements atomic increments of reference counts. Qt's QAtomicInt will do the trick if abused a little (just because it has an assembly implementations for multiple architectures). pthread_* doesn't have support for this as far as I know.
So, yeah, it's doable and some DBs do handle multi-process concurrent access, but it can be a pain in the ass. I believe SQLite does it by just locking complete tables on write access rather than using more fine-grained locks. MySQL embedded may also support multi-process access -- not sure.
man futexSee ma.gnol.ia for an example of what can happen with a DBMS that doesn't provide this. That's a big reason why I use Postgres instead of MySQL. With Postgres, the database won't get corrupted by a partially complete operation.
On the other hand, MySQL gets better performance by skipping the whole ACID part... so if you are willing to work with this (i.e. you have a good disaster recovery strategy) then you can theoretically handle a higher load by using MySQL.