↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQLSTATE / CLASS 02 · NO DATA (THIS IS ALSO A WARNING CLASS PER THE SQL STANDARD)

02000

Read PG 18 manual ↗

no_data

SQLSTATE
02000
Condition name
no_data
Class
No Data (this is also a warning class per the SQL standard)
Source macro
ERRCODE_NO_DATA
Evidence
Source path confirmed
Analysis & operational context

English SQLSTATE atlas: authored explanations and source/runtime evidence are separate from the manual definitions. View source ↗

<h1>02000 — no_data</h1>

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.

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.

Sources

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

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

02000 is no_data in SQLSTATE Class 02.

Method: Read the fixed errcodes definition row and macro.

src.errcodes.18.6

A fixed PostgreSQL 18.6 source path uses this SQLSTATE at the stated severity and operation.

Method: Read the fixed source context around the SQLSTATE macro.

Limits: This confirms a source path and current wording; it is not a runtime observation of every installation.

src.verify_nbtree.18.6

The authored evidence expands three representative amcheck groups, each resolved at DEBUG1; the fixed source also contains other 02000 DEBUG1/DEBUG2 paths that are outside this selected message set.

Method: Compare the selected message groups with the fixed verify_nbtree.c call scan.

Limits: No natural amcheck runtime or exhaustive producer inventory is claimed.

src.verify_nbtree.18.6

Message templates

DEBUG1 · L508-L514

Primary

harmless fast root mismatch in index "%s"

Detail

Fast root block %u (level %u) differs from true root block %u (level %u).

Resolved group is DEBUG1 and internal; the fixed source labels this mismatch harmless, so it does not imply corruption or a VACUUM requirement.

DEBUG1 · L691-L694

Primary

block %u of index "%s" concurrently deleted

Resolved group is DEBUG1 and internal; this branch has no VACUUM hint and requires concurrency context.

DEBUG1 · L992-L1001

Primary

index uniqueness can not be checked for index tid=(%u,%u) in index "%s"

Detail

It doesn't have visible heap tids and key is equal to the tid=(%u,%u)%s (points to heap tid=(%u,%u)).

Hint

VACUUM the table and repeat the check.

Resolved group is DEBUG1; the VACUUM hint belongs to this uniqueness-check branch only.

Reproduction & repair cases

No reproduction case is attached to this condition.

Recorded runtime evidence

No runtime observation is attached to this entry.

Definition snapshot: english-manuals:ec2fefe321cb571afbe1e324b05… · English manual source