nuttingd

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.

FOR UPDATE is the only real solution in my eyes

  import my_module

This is compatible with `__all__` if you have your code broken down into smaller sub-modules and collect them in the main module as follows:

  # my_module/__init__.py
  from .submodule import *
  from .another_submodule import *

> 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.

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.

> (Newspeak “Sprint and Goals”)

Sprint "commitments" in my world. A single word with major psychological impacts.

The check constraint solution isn't a solution to the concurrency issue: it only prevents a negative balance.

The balance would still be wrong for the whole range where balance > 0. Two (positive) deposits would not store the correct ending balance.

Pagination is harder than it seems to get right.

I think pagination is only predictable under these conditions:

1) The offset used for the next fetch must be based on a pointer to a unique key. We can't rely on the number of rows previously seen.

With this rule, deletes which occur to rows in previous pages will not cause unpredictable contractions.

2) The paged result set must be sorted based on a monotonically increasing field, like created_at, plus enough other fields to create a unique key. You could lean on the PK for this, i.e.: ORDER BY (created_at, id) ASC.

With this rule, new inserts which occur during page enumeration will only affect unseen pages (and we'll see them eventually)

The API call looks roughly like this:

  /orders/?region=US&offset=(2022-03-12T07:05:58Z&ord_1234)&limit=100

The DB query looks roughly like this:

  SELECT *
  FROM orders
  WHERE (created_at, id) > (:offset_created_at, :offset_id)
  OR (
    :offset_created_at IS NULL 
    AND :offset_id IS NULL
  )
  ORDER BY (created_at, id) ASC
  LIMIT :page_size

EDIT: formatting