Glenn Maynard wrote:
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.
Ah, it's usually "syntax errors" wrt interactive sessions.
> 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.
Yep.
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.
Well, all errors are considered fatal. But same difference.
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.)
True. Been proposed. Fiddly to implement for all use-cases if I remember
correctly.
- Let me use SAVEPOINT outside of a transaction,
You are never outside a transaction. All queries are executed within 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.)
I think this is the root of your problem - all queries are within a
transaction so either:
1. You have a transaction that wraps a single statement. If you get an
error then only that statement was affected.
2. You have an explicit BEGIN...COMMIT transaction which could use a
savepoint.
Nothing to stop you setting savepoints in #1 (although they're not much
use).
Typically, if you're in a plpgsql function you would just catch "unique"
exception codes from your insert. Or, update, see if any rows were
affected, if not try an insert and if that gives a duplicate go back and
try the update. You might want the second approach if 99% of the time
the cache is already populated.
- Let me disable this error. I don't want it. (We're grownups; we
can decide for ourselves which errors are fatal.)
You could always try submitting a patch. However, that's really what
savepoints do - let you decide whether an error can be worked around.
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.
The ORM can't control transactions, can't call functions or can't set
savepoints?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
