openlang
Storage and queries

count(*) OVER () or Repeatable Read for a paged report

The conversation continues 2 ↓Explore the sources ↓

PostgreSQL 18, Read Committed, two sessions. The reporting screen counts 12 matching orders. Another session inserts a matching order and commits. The screen then fetches the first page.

Each SELECT takes its snapshot when its own statement starts. The total describes the earlier set, and the page includes the order committed in between. Wrapping both SELECTs in a transaction changes nothing here, because Read Committed still gives each statement a fresh snapshot.

The rule I want is narrow. The count and the rows describe the same report, even if newer orders exist by the time anyone looks at it. The pair also reaches the screen together.

One Repeatable Read transaction around both reads gives them a shared snapshot. One combined query makes them a single read. Which states the rule more clearly?

The conversation continues 2

Follow the reasoning
  1. Permalink

    The combined query states the rule best. One read, nothing to keep in step. count(*) OVER () next to the page columns does it, since the window count is computed before LIMIT cuts the rows. One catch. Ask for a page past the end and no rows come back, so the total disappears with them.

    Repeatable Read works too, provided both SELECTs really run in that one transaction. Nothing there writes, so there is no update conflict to retry around.

    The read is only half of it. Replace the displayed pair together. Publish the count and the rows as two updates and the screen can show a mixed report again, with the database having done everything right.

  2. Permalink
    Following an earlier reply ↗

    Batch reports have done this for a long time. The control total is accumulated in the same pass that prints the detail lines, so the footer cannot disagree with the page above it. Nobody calls that a snapshot. It is simply one read. The combined query is the same idea, and I prefer it for the reason lichen gives. The pair is produced together and should be shown together.

Other sides of the same question

  1. Storage and queries

    NOT IN and one NULL

    SELECT FROM orders o WHERE o.customer_id NOT IN (SELECT id FROM blocked) returns nothing at all once blocked.id holds a singl…

    Replies 2sql