Postgres kills a transaction when an error happens. This is a pain; it assumes that all SQL errors are unexpected and fatal to the transaction.
There's a very simple case where it's not: UNIQUE checks. I'm generating a cache, with a simple flow: - Search for the cache key; if it exists, return its value. - If it didn't exist, create the data based on the key, insert it into the table, and return it. This has an obvious race: another thread looks up the same key and creates it between the search and the insert. Both threads will create the cached data, thread A will insert it into the table, and thread B will get an integrity error when it tries to insert it, since it duplicates the unique key. Here, by far the simplest fix is simply to ignore the integrity error. Both threads generated the same data; the failed insert is expected and harmless. Postgres is turning this into a fatal error. There's so much that could make this trivially easy: - SQLite has the handy ON CONFLICT IGNORE, but Postgres has nothing like that. (ON CONFLICT REPLACE is great, too.) - Let me use SAVEPOINT outside of a transaction, with the effect of starting a transaction with the savepoint and ending it when it's committed. Then, I could use savepoints without needing to know whether I'm already in a transaction or not; one would simply be started and committed for me if necessary. (That's by far my biggest issue with savepoints: they force me to either specify "a transaction must be open when this function is called", or need to be able to query whether one is running to decide whether to start a transaction or a savepoint. My function's use of transactions should be invisible to the caller.) - Let me disable this error. I don't want it. (We're grownups; we can decide for ourselves which errors are fatal.) The first two are cleaner, since ignoring the error means I might ignore some other integrity error from the same statement, but I can live with that. Lacking anything better, I'll probably end up dropping out of the ORM and using some uglier SQL to work around this, but this is so trivial that it's silly to have to do that. I can't do it within the ORM; it doesn't have the vocabulary. Any tricks I'm missing? It feels like Postgres is fighting me at every turn with this one, and this isn't the first time I've had this problem. -- Glenn Maynard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
