Re: Bit by "commands ignored until end of transaction block" again
От | Richard Huxton |
---|---|
Тема | Re: Bit by "commands ignored until end of transaction block" again |
Дата | |
Msg-id | 4A67F5B6.4080409@archonet.com обсуждение исходный текст |
Ответ на | Bit by "commands ignored until end of transaction block" again (Glenn Maynard <glenn@zewt.org>) |
Ответы |
Re: Bit by "commands ignored until end of transaction block"
again
(Glenn Maynard <glenn@zewt.org>)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления:
Предыдущее
От: Glenn MaynardДата:
Сообщение: Bit by "commands ignored until end of transaction block" again
Следующее
От: Glenn MaynardДата:
Сообщение: Re: Bit by "commands ignored until end of transaction block" again