"Kevin Grittner" <kevin.gritt...@wicourts.gov> wrote: > Jignesh Shah <jks...@gmail.com> wrote: > >> The question is should the delete fail if it doesn't exist and >> cause a rollback or succeed with DELETE 0 ? > > I think existing behavior is consistent with both the standard and > the other behaviors of PostgreSQL at the READ COMMITTED isolation > level. I actually woke up in the middle of the night after sending this with the realization that I was wrong in that statement. Well, half wrong -- it *is* entirely consistent with the other behaviors of PostgreSQL at the READ COMMITTED isolation level, but it is *not* consistent with the standard. Since the row existed both before and after the first transaction; for the other transaction to see zero rows it has to violate atomicity, and see *part* of the work of the transaction but not *all* of it. This issue has been discussed on the list before, regarding other curious behaviors, and what came of it was that to prevent such behavior PostgreSQL would need to wrap each statement within a READ COMMITTED transaction with an implicit subtransaction and upon detecting that a transaction on which it was blocked due to a write had committed, it would need to roll back the subtransaction and acquire a new snapshot. Nobody seemed excited about either doing the work to make that happen or what the performance implications might be. The upshot was more or less that if you care about such anomalies you should be running at a more strict isolation level -- what you describe is not a problem at REPEATABLE READ or SERIALIZABLE isolation levels. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
