Snapshot Too Old Error in YSQLSH

Environments

• YugabyteDB – Versions prior to v2024.2.2.0

Issue

Running a query via ysqlsh results in a "Snapshot too old" error. This typically occurs due to missing prefetching of the system catalog table pg_authid.

Symptoms

When the issue occurs, the following error message appears in the logs:

Snapshot too old. Read point: { physical: 1741422401264979 }, earliest read time allowed: { physical: 1741469732421160 }, delta (usec): 47331156181: kSnapshotTooOld (transaction error 4)

In yb-tserver logs, you may also see:

Snapshot too old (yb/tablet/tablet_retention_policy.cc:162): Failed Read(tablet: ...) to tablet server ... after 1 attempt(s): kSnapshotTooOld

Additionally, system log entries may reference prefetching failure:

Sys table prefetching is enabled but table { database_oid: 1 object_oid: 1260 } was not prefetched.

Cause

This error is triggered because the system table pg_authid (OID 1260) was not prefetched when system catalog prefetching was enabled. The missing prefetch causes the query to rely on a stale snapshot, which exceeds the timestamp history retention window, resulting in akSnapshotTooOld error.

This is a known issue tracked in GitHub Issue #25776.

Resolution

Permanent Fix

Upgrade to YugabyteDB v2024.2.2.0 or later, where the fix is included.

Workarounds (Short-term)

Until an upgrade is possible, apply one of the following mitigations at regular intervals (aligned with the timestamp history retention period, which defaults to 4 hours):

1. Restart the T-Server process regularly.

2. Run a redundant DDL to refresh metadata.

   What does this mean?

   A "redundant DDL" is a safe command that doesn't actually change the table but forces YugabyteDB to update its metadata.

   Example:

   If you have a table called users, run:

   ALTER TABLE users RENAME TO users_temp; ALTER TABLE users_temp RENAME TO users;

   This keeps your table unchanged but triggers a metadata refresh.

3. Disable T-Server metadata caching by setting the G-Flag:

   ysql_enable_read_request_caching=false
   

Note: This may degrade performance, especially for operations involving new connections or catalog lookups.

Additional Notes

• This issue may manifest intermittently, especially under long-running sessions or scripts.
• Disabling read request caching is only advisable in test environments or when other mitigations are infeasible.