You would need to use serializable isolation for this to hold true. Any isolation level less than serializable will use the snapshot that was active at the time of the select.
In Postgres, even with the serializable isolation level, all transactions that touch the same rows must also be using the serializable isolation level or it's not really enforced. This is one aspect of serializable isolation in Postgres that seemed like a major gotcha for real world application development. There's no future proof solution: new code can be added that doesn't use the serializable isolation, and then the assumptions of isolation from the earlier code are no longer valid.
> which makes a very simple concept vastly more confusing than it needs to be.
Agreed. The concepts are all very simple. You can throw away all of the domain-specific terminology and reason about accounting theory with nothing but positive and negative numbers.
The utility of the confusing terminology and age old accounting frameworks isn't obvious unless you are a practitioner living "in it". It's not until you face the complexities of real world transactions (an accountant booking closing entries for a F500 company or something) that the strange left/right debit/credit way of thinking is very valuable.
It's the accounting equation being represented in canonical form. A chart of accounts is visualized in the minds of an accountant as:
Assets | Liabilities + Equity
Accounts classified as assets are debit accounts (left side), and accounts classified as liabilities or equity are credit accounts (right side).
The theory discussed everywhere in this thread is sound. You really don't need to use terminology like debit/credit for accounting.
What the discussion misses is the application of this framework. It is useful for a human to be able to visualize a complex transaction and work through missing pieces with the hints this framework provides. I'm missing something on the left? Oh yeah, I missed the deferred revenue debit.
If you use the Serializable level, it must be observed by all transactions of the application. When combined with other levels, Serializable behaves as Repeatable Read without any notice. So if you decide to use the Serializable level, it makes read sense to modify the default_transaction_isolation parameter value accordingly -- even though someone can still overwrite it by explicitly setting a different level.
I had a real "WTF?" moment when I read this the first time.
One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies.
This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transactions, regardless of their isolation level.
In Postgres, you may have a set of transactions all participating in SERIALIZABLE isolation today, but tomorrow someone adds another script without the SERIALIZABLE isolation level, and now your protected paths are no longer isolated.
In Postgres, even with the serializable isolation level, all transactions that touch the same rows must also be using the serializable isolation level or it's not really enforced. This is one aspect of serializable isolation in Postgres that seemed like a major gotcha for real world application development. There's no future proof solution: new code can be added that doesn't use the serializable isolation, and then the assumptions of isolation from the earlier code are no longer valid.
FOR UPDATE is the only real solution in my eyes