On-demand + limit: an optimistic update that moves a row out of a limited live query is not shown until the transaction settles #2169
Send to my agentVersions: @tanstack/db 0.15.0 + @tanstack/query-db-collection 1.4.5 (also 0.14.0 + 1.4.4)
What happens
With syncMode: "on-demand", a live query with where + orderBy + limit keeps showing a row after an optimistic update moves it out of the where. collection.get() already returns the optimistic value. The view only updates when the transaction settles, so the optimistic update is never visible in the list (e.g. archiving a message keeps it in the inbox for the whole request).
Without limit, or in eager mode, the row leaves at once. Ascending/descending order, with or without indexes, and limits at or above the row count all behave the same.
Repro
import { QueryClient } from "@tanstack/query-core";
import { parseLoadSubsetOptions, queryCollectionOptions } from "@tanstack/query-db-collection";
import { BasicIndex, createCollection, createLiveQueryCollection, createTransaction, eq } from "@tanstack/db";
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
const server = [1, 2, 3].map((n) => ({ id: n, folder: "inbox", at: n }));
const rows = createCollection(queryCollectionOptions({
id: "rows", syncMode: "on-demand", queryClient: new QueryClient(),
queryKey: (o) => (o?.where ? ["rows", JSON.stringify(parseLoadSubsetOptions(o))] : ["rows"]),
queryFn: async ({ meta }) => {
const { filters, limit } = parseLoadSubsetOptions(meta.loadSubsetOptions);
return server.filter((r) => filters.every((f) => r[f.field[0]] === f.value))
.sort((a, b) => b.at - a.at).slice(0, limit).map((r) => ({ ...r }));
},
getKey: (r) => r.id, autoIndex: "eager", defaultIndexType: BasicIndex,
}));
const view = createLiveQueryCollection((q) => q.from({ r: rows })
.where(({ r }) => eq(r.folder, "inbox")).orderBy(({ r }) => r.at, "desc").limit(2));
await view.preload();
const show = (t) => console.log(t, view.toArray.map((r) => r.id), "row 3:", rows.get(3)?.folder, "row 1:", rows.get(1)?.folder);
show("before"); // [3, 2]
const tx = createTransaction({ mutationFn: async () => {
await sleep(500); server[2].folder = "archive"; await rows.utils.refetch();
} });
tx.mutate(() => rows.update(3, (d) => { d.folder = "archive"; }));
await sleep(100); show("optimistic"); // expected [2, 1], got [3, 2]
await tx.isPersisted.promise; await sleep(10); show("persisted"); // [2, 1]
process.exit(0);
Output:
before [ 3, 2 ] row 3: inbox
optimistic [ 3, 2 ] row 3: archive <- expected [ 2, 1 ]
persisted [ 2, 1 ]
Cause, as far as we traced it
The delete reaches the pipeline (CollectionSubscriber.sendChangesToPipeline). OrderedSourceLoader.onSourceChanges → invalidateSourceOrdering starts an ordered refill that holds publication (trackOrderedLoadPromise; flushPendingChanges returns early while pendingOrderedLoads.size > 0). The refill's sync write waits behind the persisting transaction in CollectionStateManager.commitNextPendingTransactionBatch, so the repair cannot settle until the transaction does.
Related: #2131 (keep the last complete snapshot during repair), #1017 and RFC #1625 (sync parked behind pending mutations). We found no issue for this exact effect.
Expected: the view drops the row at once, as it does in eager mode or without a limit, and fills the gap when the refill lands.
A related observation from #2154 is filed as #2184. The same mechanism also delays readiness. While an optimistic transaction persists, core holds accepted sync rows back, so a new live query's
preload()on an on-demand Query Collection waits until the transaction settles. The law decision this issue needs probably applies to both: which observations may wait for a persisting transaction.This comment was written by Isaac.
atduarte(GitHub profile, opens in a new tab) closed this
Sign in to join this conversation.
Notes for whoever picks this up#
Thanks for the precise trace. The mechanism matches what we see from the loader side. Here is the context that bears on a fix.
Two laws collide here#
packages/db/src/query/live/ARCHITECTURE.mdstates this rule.CollectionConfigBuilder(packages/db/src/query/live/collection-config-builder.ts) enforces it throughpendingOrderedLoads, the check you found influshPendingChanges.limit.The repair's sync write is queued behind the persisting transaction (
CollectionStateManager.commitNextPendingTransactionBatch). So the barrier holds the optimistic removal for the whole request. Neither law is wrong alone, but the barrier needs an exception, or the repair needs a different completion point, while a local transaction blocks sync application. Settle that law first, and update ARCHITECTURE.md in the same PR. One candidate: a repair that cannot settle until a pending transaction settles must not hold publication of that transaction's optimistic changes. The window may publish the last complete window minus the removed row, then fill the gap when the refill lands. That means one intermediate publication, which #2131's law currently forbids.Closely related open issue: #2148#
#2148 ("On-demand joined window stays short after an optimistic delete of its visible row") is the joined-filter sibling. There the next row is never loaded until the delete persists. Both involve an optimistic change that leaves a window, a repair that waits for the transaction, and a window that stays wrong in between. One fix may close both; check #2148 against any change.
Existing oracle coverage to extend (don't add an isolated test)#
packages/db/tests/query/ordered-work-oracle.property.test.tsowns ordered windows on on-demand providers, including held loads and the barrier observations. Its coverage-map entry (docs/contributing/oracle-coverage.md, "Ordered acquisition") says an optimistic-delete dimension for #2148 is "kept as a patch". This issue needs that dimension, plus an optimistic update that moves a row out of awhere, with a provider whose writes are blocked by a pending transaction.packages/db/tests/query/ordered-source-loader-state-oracle.test.tscovers the loader's repair and settlement states.packages/db/tests/optimistic-history-oracle.tsowns optimistic snapshots and settlement; its law for when optimistic state is visible is the other side of the collision.A witness should fail on main at the "optimistic" checkpoint of the repro,
[3, 2]where[2, 1]is expected. It should pass once the row leaves at once, and the window must still never publish a wrong final window.Interaction with PR #2154#
PR #2154 (#2136) adds a second way to start the same repair. After a refetch, query-db calls the new
invalidateLoadedSubsets()sync param, and each paging ordered loader runs its bounded prefix repair. The repro's pattern (mutationFncallingrows.utils.refetch()), and the default auto-refetch after mutation handlers, can therefore start a repair while the transaction is still pending. That repair would also hold publication behind the transaction. #2154 limits it to windows that need rows past their own first page. Your repro removes the row through the delete path, not through a refetch report, so #2154 neither causes nor fixes it. A fix at the barrier should cover both triggers.This comment was written by Isaac.