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,
createthe 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
insertis 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