Skip to content

02000 — no_data

PostgreSQL SQLSTATE 02000 is a no-data condition; PostgreSQL 18.6 has DEBUG diagnostics in amcheck, not a rule that every zero-row SELECT raises it.

02000 — no_data

At a glance

02000 has confirmed 18.6 DEBUG1/DEBUG2 paths in contrib/amcheck/verify_nbtree.c for internal index-check conditions. The three groups detailed below are each DEBUG1; they are not the same as a normal query returning zero rows and are not a generic client error.

Field Value
SQLSTATE 02000
Condition no_data
Status active
Known present by 7.4
Locked snapshots 9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3
Macros ERRCODE_NO_DATA
Aliases —

Meaning

The confirmed 18.6 implementation is in contrib/amcheck/verify_nbtree.c, where index consistency diagnostics use ERRCODE_NO_DATA at DEBUG1 or DEBUG2. This is an internal diagnostic use, not ordinary SELECT no-row semantics. The authored evidence expands three representative groups; other source paths are outside that selected message set.

Messages and diagnostics

The selected fixed groups are:

  • DEBUG1, internal errmsg_internal: harmless fast root mismatch in index "%s", with internal detail Fast root block %u (level %u) differs from true root block %u (level %u). The source labels this condition harmless.
  • DEBUG1, internal errmsg_internal: block %u of index "%s" concurrently deleted. This branch has no detail or hint in the resolved group.
  • DEBUG1, ordinary errmsg/errdetail/errhint: index uniqueness can not be checked for index tid=(%u,%u) in index "%s", with detail It doesn't have visible heap tids and key is equal to the tid=(%u,%u)%s (points to heap tid=(%u,%u)). and hint VACUUM the table and repeat the check. The VACUUM hint belongs to this group only.

Diagnosis

If this code comes from amcheck, identify the check, index, block/TID context and server log severity. Treat a fast-root mismatch as the source labels it: a harmless diagnostic requiring context, not automatic corruption repair. For a concurrently deleted block, correlate the concurrent operation or snapshot and rerun only after the relevant activity has settled. For the uniqueness-uncheckable group, inspect the named index and follow its specific VACUUM hint before repeating the check. For application no-row handling, inspect the command result or procedural condition actually returned; PL/pgSQL NO_DATA_FOUND is P0002, not a blanket synonym for 02000.

Response

Apply the branch-specific guidance: do not turn the harmless fast-root diagnostic into an automatic repair; investigate concurrent state for a concurrent deletion; and use VACUUM before repeating the check only when the uniqueness-uncheckable group supplies that hint. Do not require maintenance for every DEBUG finding, and do not add a no-row exception or retry to ordinary SELECT code solely because the SQLSTATE class is 02.

Versions

02000 is present from the locked 9.0.23 snapshot through 18.6 and 19 Beta 3, with known_present_by 7.4. The fixed 18.6 evidence covers the amcheck DEBUG paths; other producers need separate source evidence.

P0002 is PL/pgSQL no_data_found; 02001 concerns additional dynamic result sets; 00000 is successful completion.

Sources

  • errcodes.txt — definition, SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba.
  • verify_nbtree.c — fixed path, SHA-256 a59ae7540a990e5a70e8eb9cedbcad7c62e3e86b1ad426142c8931ee188b752d.
  • Structured evidence — fixed sources, message groups, and runtime boundary.