On 08.09.21 18:37, Tom Lane wrote:
PG Doc comments form <nore...@postgresql.org> writes:
The docs mention "For example, a common use of advisory locks is to emulate
pessimistic locking strategies typical of so-called “flat file” data
management systems" which is exactly what I wanted to use to port some code
from using SQLite to using PostgreSQL. (The code in question requires
serializable transactions and cannot not handle retries.)

Hmm.  I'm afraid you're out of luck on that combination of requirements:

If it's not possible even with conservative locking using advisory locks, the current phrasing of emulating "flat file" databases certainly seems a bit misleading to me, as these are exactly the guarantees those offer (or at least several of them do).

The approximation is conservative in the sense
that it won't let any actual failures get by; but it may produce false
positives.  We haven't felt this is a problem, because if you're using
this stuff in the first place, you likely have *actual* anomaly hazards
and thus need the retry logic anyway.

Is the approximation so conservative that it can fail even when there is just a single transaction at a time? What about multiple "SERIALIZABLE READ ONLY DEFERRABLE" transactions concurrent to at most a single writing transaction? Those are question I now have and would love to see answered by the documentation.

My testing with session level advisory locks indicates that this works fine without serialization failures, but of course that testing is far from exhaustive.

The advice in the manual is thinking about READ COMMITTED mode, where
I think this should work fine.  It is a bit problematic in serializable
mode, because when you do "SELECT pg_advisory_lock...", the SELECT will
acquire the transaction snapshot before getting the lock.

Yes that's roughly what I assumed was happening (modulo not knowing any details of the implementation) given the behavior I saw, and how those failures did not happen when taking a session level lock before beginning a transaction.

What I'm inclined to think here is that maybe the docs are not
sufficiently vocal about the fact that you can't avoid serialization
failures altogether.

If even taking a session level lock before any writing transaction, combined with "SERIALIZABLE READ ONLY DEFERRABLE" for all reading transaction is not sufficient to avoid this, I would certainly agree. Again, especially because of the comparison to "flat file" databases where this can be avoided.

Thank you for the clarifications!

Best Regards,
Jannis


Reply via email to