Scoring cache returns stale relevance after committed updates

[erickreutz]

Repeating a search after a committed update returns the updated text with a stale relevance score. I reproduced this in plain SQL on the same connection, without Rails or an adapter.

Environment: Lead 1.0.3 at bd95c7e51b6afce81396790852ee2f2c169570ad, PostgreSQL 18.6, Linux arm64.

Reproduction

Run this through psql with its default autocommit behavior, executing each statement separately. Do not wrap the reproduction in BEGIN or send the entire script as one implicit transaction.

CREATE EXTENSION IF NOT EXISTS tin;

CREATE TEMP TABLE lead_cache_probe (
  id integer PRIMARY KEY,
  body text
);
CREATE INDEX ON lead_cache_probe USING tin (body);

INSERT INTO lead_cache_probe VALUES (1, 'Blue sofa');

SELECT body, tin.full_score(ctid) AS score
FROM lead_cache_probe
WHERE body ==> 'sofa';

UPDATE lead_cache_probe SET body = 'Red sofa';

SELECT body, tin.full_score(ctid) AS score
FROM lead_cache_probe
WHERE body ==> 'sofa';

Observed:

Search	Returned text	Score
Before update	Blue sofa	0.2876821
After update	Red sofa	0

Both scores should be 0.2876821: the document length and frequency of the searched term are unchanged. Matching and row visibility are correct in this reproduction, but relevance scoring is stale. Sorting by score can therefore return the wrong ranking.

Suspected cause

The score cache key includes GetTopTransactionIdIfAny() and GetCurrentCommandId(false). Separate read-only autocommit transactions can have the same values for both, so the next search reuses a corpus from an earlier snapshot.

The lookup by document text then looks for Red sofa in a cached map containing Blue sofa and defaults to 0.0.

I also reproduced a related case with two connections: a reader stays in a READ COMMITTED transaction with an explicitly assigned transaction ID; another connection commits a text update; the reader repeats the query and sees the new text but receives a zero score. A savepoint around each search does not prevent it. Assigning a transaction ID alone therefore does not address freshness across statement snapshots.

Local workaround

For a local integration proof, I invalidated the corpus at the start of each scoring call:

SCORE_CACHE.with_borrow_mut(|slot| {
    *slot = None;
    // Existing cache construction and scoring follow.

The same SQL then returns 0.2876821 before and after the update. Regression tests for both successive autocommit searches and an external commit during a READ COMMITTED transaction pass with this change.

This intentionally trades performance for correctness and is not a proposal to discard caching permanently. The cache needs a lifetime or invalidation rule that respects the statement's visible snapshot. I have not tested production TIN for this behavior.

[erickreutz]

I’ve put a fix and regression tests in my fork.

The fix replaces the backend-wide cache with pg_func_extra, owned by each scoring expression’s executor memory context. It preserves reuse across rows, refreshes for each prepared-query execution, and retains a cursor’s original corpus across fetches. The corpus is freed when that execution ends. Query, index, and scoring parameters remain in the cache key.

Both new cross-connection regressions fail before the fix with 0.0 instead of 0.2876821: repeated generic prepared executions after committed updates, and READ COMMITTED queries after another connection commits an update. The tests also cover updates within one transaction and an assigned-XID cursor interleaved with a fresh query.

With the fix, all 39 tests pass on PostgreSQL 17.11 and 18.6 on Linux arm64. Clippy passes with warnings denied for both versions. These run through the existing suite, with no added dependencies:

cargo pgrx test pg17 --package tin --no-default-features --features pg17
cargo pgrx test pg18 --package tin --no-default-features --features pg18

Sharing the commit here since PRs are restricted to collaborators.

[erickreutz]

I’ve now tested this against actual PlanetScale TIN 1.0.3 on PostgreSQL 18.6, Linux arm64, in an isolated test database. The stale-zero behavior did not reproduce.

Running the original autocommit reproduction on the same connection gives:

Search	Returned text	Lead 1.0.3, unmodified	Production TIN 1.0.3
Before update	Blue sofa	0.2876821	0.2876821
After update	Red sofa	0	0.18232156

A correction to my original expected-result wording: an unchanged score after an equal-length update is not a cross-engine requirement. TIN’s documented corpus statistics include obsolete index entries until maintenance removes them, so scores can legitimately change after updates. The defect here is Lead returning zero for this matching updated document because it reuses the old corpus.

The two-connection READ COMMITTED case also passes against TIN: the reader sees the updated text with a positive, finite score, and the old text no longer matches. I updated the shared adapter tests to check those properties instead of exact before/after score equality. The suite passes on actual TIN with 34 tests and 214 assertions, without adapter changes.