v2-0001-add-a-hint-for-a-missing-relation.patch

From e9c6abc3793d676d65dacdaecd754d2245b83366 Mon Sep 17 00:00:00 2001
From: steve-chavez <stevechavezast@gmail.com>
Date: Mon, 1 Dec 2025 20:58:46 -0500
Subject: [PATCH v2] add a hint for a missing relation

* remove unneeded MAX_FUZZY_DISTANCE guard
* only scan base relations and not partitions
* constraint scanning to only relevant relnamespaces
---
 src/backend/parser/parse_relation.c          | 196 ++++++++++++++++++-
 src/test/regress/expected/prepared_xacts.out |   2 +
 src/test/regress/expected/psql.out           |   1 +
 src/test/regress/expected/temp.out           |   4 +
 src/test/regress/expected/transactions.out   |   2 +
 src/test/regress/expected/xml_1.out          |   1 +
 6 files changed, 203 insertions(+), 3 deletions(-)

diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index c1563f1b51b..3cb319ae444 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -15,11 +15,15 @@
 #include "postgres.h"
 
 #include <ctype.h>
+#include <limits.h>
 
+#include "access/heapam.h"
 #include "access/htup_details.h"
 #include "access/relation.h"
 #include "access/table.h"
+#include "access/tableam.h"
 #include "catalog/heap.h"
+#include "catalog/pg_class.h"
 #include "catalog/namespace.h"
 #include "funcapi.h"
 #include "nodes/makefuncs.h"
@@ -30,6 +34,8 @@
 #include "parser/parsetree.h"
 #include "storage/lmgr.h"
 #include "utils/builtins.h"
+#include "utils/hsearch.h"
+#include "utils/fmgroids.h"
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 #include "utils/varlena.h"
@@ -71,6 +77,24 @@ typedef struct
 
 #define MAX_FUZZY_DISTANCE				3
 
+#define MAX_REL_HINT_CANDIDATES			4096
+
+/*
+ * Support for fuzzily matching relations for error messages.
+ *
+ * If the relation is schema qualified, relations in that schema are fuzzyly searched.
+ * If the relation is not schema qualified, all the schemas in search_path are fuzzily searched.
+ *
+ * The searches are capped at MAX_REL_HINT_CANDIDATES, to prevent doing too much work
+ * in big catalogs.
+ */
+typedef struct
+{
+	int			distance;		/* Current distance */
+	int			search_path_pos;		/* Position of the current schema in search path */
+	char		*nspname;		/* Schema of current match */
+	char		*relname;		/* Relation name of current match */
+} FuzzyRelationMatchState;
 
 static ParseNamespaceItem *scanNameSpaceForRefname(ParseState *pstate,
 												   const char *refname,
@@ -102,7 +126,11 @@ static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
 static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
 
 static inline bool levenshtein_is_absurd(int distance, int matchlen);
-static void errorMissingRelation(ParseState *pstate, const RangeVar *relation);
+static void errorMissingRelation(ParseState *pstate,
+											 const RangeVar *relation);
+static List *relhintCandidateSchemas(const RangeVar *relation);
+static bool bestFuzzyRelationMatch(const RangeVar *relation,
+										  FuzzyRelationMatchState *match);
 
 /*
  * refnameNamespaceItem
@@ -1414,6 +1442,10 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex,
 	return nsitem;
 }
 
+/*
+ * Generate a suitable error about a missing relation, possibly with hints
+ * about similarly named relations.
+ */
 void
 errorMissingRelation(ParseState *pstate, const RangeVar *relation)
 {
@@ -1432,11 +1464,31 @@ errorMissingRelation(ParseState *pstate, const RangeVar *relation)
 						   relation->relname),
 				 errhint("Use WITH RECURSIVE, or re-order the WITH items to remove forward references.")));
 	else
+	{
+		bool		found_match = false;
+		FuzzyRelationMatchState match = {
+			.distance = MAX_FUZZY_DISTANCE,
+			.search_path_pos = INT_MAX,
+			.nspname = NULL,
+			.relname = NULL,
+		};
+
+		found_match = bestFuzzyRelationMatch(relation, &match);
+
 		ereport(ERROR,
 				(errcode(ERRCODE_UNDEFINED_TABLE),
 				 relation->schemaname ?
-					errmsg("relation \"%s.%s\" does not exist", relation->schemaname, relation->relname) :
-					errmsg("relation \"%s\" does not exist", relation->relname)));
+					errmsg("relation \"%s.%s\" does not exist",
+						   relation->schemaname, relation->relname) :
+					errmsg("relation \"%s\" does not exist",
+						   relation->relname),
+				 found_match ?
+					errhint("Perhaps you meant to reference the table \"%s\".",
+							quote_qualified_identifier(match.nspname,
+													   match.relname)) :
+					0,
+				 parser_errposition(pstate, relation->location)));
+	}
 }
 
 /*
@@ -1464,6 +1516,144 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
 	return rel;
 }
 
+/*
+ * Search pg_class for a relation name that's similar to the target.
+ */
+static bool
+bestFuzzyRelationMatch(const RangeVar *target,
+				  FuzzyRelationMatchState *fuzzystate)
+{
+	List   *candidate_schemas = relhintCandidateSchemas(target);
+	Relation	classRel;
+	int		checked = 0;
+	int		schema_pos = 0;
+
+	if (candidate_schemas == NIL)
+		return false;
+
+	classRel = table_open(RelationRelationId, AccessShareLock);
+
+	foreach_oid(schema, candidate_schemas)
+	{
+		ScanKeyData skey[2];
+		TableScanDesc scan;
+		HeapTuple	tuple;
+
+		/* Scan objects on each schema */
+		ScanKeyInit(&skey[0],
+					Anum_pg_class_relnamespace,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(schema));
+
+    /* Don't scan partitions */
+		ScanKeyInit(&skey[1],
+					Anum_pg_class_relispartition,
+					BTEqualStrategyNumber, F_BOOLEQ,
+					BoolGetDatum(false));
+
+		scan = table_beginscan_catalog(classRel, 2, skey);
+
+		while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
+		{
+			Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+			const char *candidate = NameStr(classForm->relname);
+			int		candidatelen = strlen(candidate);
+			int		reldistance;
+
+			/* Only search for relation-like objects */
+			if (!(classForm->relkind == RELKIND_RELATION ||
+				  classForm->relkind == RELKIND_PARTITIONED_TABLE ||
+				  classForm->relkind == RELKIND_FOREIGN_TABLE ||
+				  classForm->relkind == RELKIND_VIEW ||
+				  classForm->relkind == RELKIND_MATVIEW))
+				continue;
+
+			/* Keep the scan bounded in very large catalogs. */
+			if (++checked > MAX_REL_HINT_CANDIDATES)
+			{
+				table_endscan(scan);
+				goto done;
+			}
+
+			/*
+			 * Only obtain new distances lower or equal than the capped current distance.
+			 * Equal distances are relevant because the tie is solved below.
+			 */
+			reldistance = varstr_levenshtein_less_equal(target->relname, strlen(target->relname),
+							candidate, candidatelen,
+							1, 1, 1,
+							fuzzystate->distance,
+							true);
+
+			if (levenshtein_is_absurd(reldistance, candidatelen))
+				continue;
+
+			/*
+			 * If the new distance is less than the match found so far, update
+			 * fuzzystate.  If the distance is equal, prefer the match with the
+			 * lowest search path position.  If the search position is equal, use
+			 * the lexicographic order to solve the tie, this also ensures the
+			 * hint is stable across runs since the heap scan order is
+			 * nondeterministic.
+			 */
+			if (reldistance < fuzzystate->distance ||
+				(reldistance == fuzzystate->distance &&
+				 schema_pos < fuzzystate->search_path_pos) ||
+				(reldistance == fuzzystate->distance &&
+				 schema_pos == fuzzystate->search_path_pos &&
+				 strcmp(candidate, fuzzystate->relname) < 0))
+			{
+				if (fuzzystate->relname)
+					pfree(fuzzystate->relname);
+				if (fuzzystate->nspname)
+					pfree(fuzzystate->nspname);
+
+				fuzzystate->distance = reldistance;
+				fuzzystate->search_path_pos = schema_pos;
+				fuzzystate->relname = pstrdup(candidate);
+				fuzzystate->nspname = get_namespace_name(classForm->relnamespace);
+			}
+		}
+
+		table_endscan(scan);
+		schema_pos++;
+	}
+
+done:
+	table_close(classRel, AccessShareLock);
+	list_free(candidate_schemas);
+
+	/* Return true if there was a match */
+	return fuzzystate->relname != NULL;
+}
+
+/*
+ * Candidate schemas for the relation hint.
+ *
+ * If the relation was schema-qualified we'll only search that schema
+ * (considering pg_temp), otherwise consider search_path.
+ */
+static List *
+relhintCandidateSchemas(const RangeVar *relation)
+{
+	if (relation->schemaname)
+	{
+		Oid			namespaceId = LookupNamespaceNoError(relation->schemaname);
+
+		return OidIsValid(namespaceId) ? list_make1_oid(namespaceId) : NIL;
+	}
+	else if (relation->relpersistence == RELPERSISTENCE_TEMP)
+	{
+		Oid			tempNamespace;
+
+		GetTempNamespaceState(&tempNamespace, NULL);
+		return OidIsValid(tempNamespace) ? list_make1_oid(tempNamespace) : NIL;
+	}
+	else
+		return fetch_search_path(true);
+}
+
+
 /*
  * Add an entry for a relation to the pstate's range table (p_rtable).
  * Then, construct and return a ParseNamespaceItem for the new RTE.
diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index 515a2ada9d1..47b1f6920fb 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -201,6 +201,7 @@ SELECT * FROM pxtest2;
 ERROR:  relation "pxtest2" does not exist
 LINE 1: SELECT * FROM pxtest2;
                       ^
+HINT:  Perhaps you meant to reference the table "public.pxtest3".
 -- There should be two prepared transactions
 SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
      gid      
@@ -257,6 +258,7 @@ SELECT * FROM pxtest3;
 ERROR:  relation "pxtest3" does not exist
 LINE 1: SELECT * FROM pxtest3;
                       ^
+HINT:  Perhaps you meant to reference the table "public.pxtest2".
 -- There should be no prepared transactions
 SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
  gid 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index c8f3932edf0..6b8f133aa7d 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5633,6 +5633,7 @@ SELECT * FROM ac_test;  -- should be gone now
 ERROR:  relation "ac_test" does not exist
 LINE 1: SELECT * FROM ac_test;
                       ^
+HINT:  Perhaps you meant to reference the table "public.aggtest".
 -- ON_ERROR_ROLLBACK
 \set ON_ERROR_ROLLBACK on
 CREATE TABLE oer_test (a int);
diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index a50c7ae88a9..63e62028d56 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -47,6 +47,7 @@ SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
 LINE 1: SELECT * FROM temptest;
                       ^
+HINT:  Perhaps you meant to reference the table "public.xmltest".
 -- Test ON COMMIT DELETE ROWS
 CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
 -- while we're here, verify successful truncation of index with SQL function
@@ -100,6 +101,7 @@ SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
 LINE 1: SELECT * FROM temptest;
                       ^
+HINT:  Perhaps you meant to reference the table "public.xmltest".
 BEGIN;
 CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
 SELECT * FROM temptest;
@@ -113,6 +115,7 @@ SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
 LINE 1: SELECT * FROM temptest;
                       ^
+HINT:  Perhaps you meant to reference the table "public.xmltest".
 -- Test it with a CHECK condition that produces a toasted pg_constraint entry
 BEGIN;
 do $$
@@ -133,6 +136,7 @@ SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
 LINE 1: SELECT * FROM temptest;
                       ^
+HINT:  Perhaps you meant to reference the table "public.xmltest".
 -- ON COMMIT is only allowed for TEMP
 CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
 ERROR:  ON COMMIT can only be used on temporary tables
diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out
index 7f5757e89c4..2d89fb791c5 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -208,6 +208,7 @@ SELECT * FROM trans_bar;		-- shouldn't exist
 ERROR:  relation "trans_bar" does not exist
 LINE 1: SELECT * FROM trans_bar;
                       ^
+HINT:  Perhaps you meant to reference the table "public.trans_baz".
 SELECT * FROM trans_barbaz;	-- should be empty
  a 
 ---
@@ -226,6 +227,7 @@ BEGIN;
 ERROR:  relation "trans_bar" does not exist
 LINE 1: INSERT into trans_bar VALUES (1);
                     ^
+HINT:  Perhaps you meant to reference the table "public.trans_baz".
 	ROLLBACK TO one;
 	RELEASE SAVEPOINT one;
 	SAVEPOINT two;
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 73c411118a3..0514b8dd92b 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1072,6 +1072,7 @@ SELECT * FROM xmltableview2;
 ERROR:  relation "xmltableview2" does not exist
 LINE 1: SELECT * FROM xmltableview2;
                       ^
+HINT:  Perhaps you meant to reference the table "public.xmltableview1".
 \sv xmltableview2
 ERROR:  relation "xmltableview2" does not exist
 SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
-- 
2.42.0