[PATCH] Add hint for misspelled relations

From 9a500e02535b09051816a55ebb6f75e4421da270 Mon Sep 17 00:00:00 2001
From: steve-chavez <stevechavezast@gmail.com>
Date: Mon, 1 Dec 2025 20:13:36 -0500
Subject: [PATCH 1/2] refactor: isolate relation errors to a function

Also add levenshtein_is_absurd explaining more about the formula used.
---
 src/backend/parser/parse_relation.c | 74 ++++++++++++++++-------------
 1 file changed, 40 insertions(+), 34 deletions(-)

diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index d544a69fc80..c1563f1b51b 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -101,6 +101,8 @@ static int	specialAttNum(const char *attname);
 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);
 
 /*
  * refnameNamespaceItem
@@ -615,11 +617,7 @@ updateFuzzyAttrMatchState(int fuzzy_rte_penalty,
 									  - fuzzy_rte_penalty,
 									  true);
 
-	/*
-	 * If more than half the characters are different, don't treat it as a
-	 * match, to avoid making ridiculous suggestions.
-	 */
-	if (columndistance > matchlen / 2)
+	if (levenshtein_is_absurd(columndistance, matchlen))
 		return;
 
 	/*
@@ -1416,6 +1414,31 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex,
 	return nsitem;
 }
 
+void
+errorMissingRelation(ParseState *pstate, const RangeVar *relation)
+{
+	/*
+	 * An unqualified name might have been meant as a reference to
+	 * some not-yet-in-scope CTE.  The bare "does not exist" message
+	 * has proven remarkably unhelpful for figuring out such problems,
+	 * so we take pains to offer a specific hint.
+	 */
+	if (isFutureCTE(pstate, relation->relname))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_TABLE),
+				 errmsg("relation \"%s\" does not exist",
+						relation->relname),
+				 errdetail("There is a WITH item named \"%s\", but it cannot be referenced from this part of the query.",
+						   relation->relname),
+				 errhint("Use WITH RECURSIVE, or re-order the WITH items to remove forward references.")));
+	else
+		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)));
+}
+
 /*
  * Open a table during parse analysis
  *
@@ -1436,35 +1459,7 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
 	setup_parser_errposition_callback(&pcbstate, pstate, relation->location);
 	rel = table_openrv_extended(relation, lockmode, true);
 	if (rel == NULL)
-	{
-		if (relation->schemaname)
-			ereport(ERROR,
-					(errcode(ERRCODE_UNDEFINED_TABLE),
-					 errmsg("relation \"%s.%s\" does not exist",
-							relation->schemaname, relation->relname)));
-		else
-		{
-			/*
-			 * An unqualified name might have been meant as a reference to
-			 * some not-yet-in-scope CTE.  The bare "does not exist" message
-			 * has proven remarkably unhelpful for figuring out such problems,
-			 * so we take pains to offer a specific hint.
-			 */
-			if (isFutureCTE(pstate, relation->relname))
-				ereport(ERROR,
-						(errcode(ERRCODE_UNDEFINED_TABLE),
-						 errmsg("relation \"%s\" does not exist",
-								relation->relname),
-						 errdetail("There is a WITH item named \"%s\", but it cannot be referenced from this part of the query.",
-								   relation->relname),
-						 errhint("Use WITH RECURSIVE, or re-order the WITH items to remove forward references.")));
-			else
-				ereport(ERROR,
-						(errcode(ERRCODE_UNDEFINED_TABLE),
-						 errmsg("relation \"%s\" does not exist",
-								relation->relname)));
-		}
-	}
+		errorMissingRelation(pstate, relation);
 	cancel_parser_errposition_callback(&pcbstate);
 	return rel;
 }
@@ -3971,3 +3966,14 @@ getRTEPermissionInfo(List *rteperminfos, RangeTblEntry *rte)
 
 	return perminfo;
 }
+
+
+/*
+ * Ignore absurd suggestions (e.g., mostly different characters).
+ * Having MAX_FUZZY_DISTANCE = 3, `abc` and `xyz` have a levenshtein distance of 3 which falls under MAX_FUZZY_DISTANCE, but yet they're mostly different.
+ */
+static inline bool
+levenshtein_is_absurd(int distance, int matchlen)
+{
+	return distance > matchlen / 2;
+}
-- 
2.42.0

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

---
 src/backend/parser/parse_relation.c          | 219 ++++++++++++++++++-
 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, 226 insertions(+), 3 deletions(-)

diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index c1563f1b51b..734c132aaff 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -15,10 +15,13 @@
 #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/namespace.h"
 #include "funcapi.h"
@@ -30,6 +33,7 @@
 #include "parser/parsetree.h"
 #include "storage/lmgr.h"
 #include "utils/builtins.h"
+#include "utils/hsearch.h"
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 #include "utils/varlena.h"
@@ -71,6 +75,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 +124,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 +1440,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 +1462,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 + 1,
+			.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 +1514,169 @@ 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	   *schemas = relhintCandidateSchemas(target);
+	ListCell   *lc;
+	Relation classRel;
+	TableScanDesc scan;
+	HeapTuple tuple;
+	int i = 0;
+	int checked = 0;
+
+	/* Schema order in the search_path */
+	typedef struct {
+	  Oid			oid;
+	  int			order;
+	} SchemaOrderEntry;
+	HTAB	   *schema_order;
+
+	if (schemas == NIL)
+		return false;
+
+	schema_order = hash_create("relation hint search_path order",
+									list_length(schemas),
+									&(HASHCTL){
+										.keysize = sizeof(Oid),
+ 										.entrysize = sizeof(SchemaOrderEntry)
+ 									},
+									HASH_ELEM | HASH_BLOBS);
+
+	/*
+	 * Populate schema_order.  This map won't really be needed in cases where
+	 * the candidate list has only one element, but keep it anyway to reuse
+	 * code.
+	 */
+	foreach(lc, schemas)
+	{
+		Oid			nsp = lfirst_oid(lc);
+		SchemaOrderEntry *entry;
+		bool		found;
+
+		entry = hash_search(schema_order, &nsp, HASH_ENTER, &found);
+		if (!found)
+			entry->order = i;
+		i++;
+	}
+
+	classRel = table_open(RelationRelationId, AccessShareLock);
+	scan = table_beginscan_catalog(classRel, 0, NULL);
+
+	/* Iterate over pg_class entries */
+	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			order = -1;
+		int			reldistance;
+		SchemaOrderEntry *entry;
+		bool		found;
+
+		/* 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;
+
+		entry = hash_search(schema_order, &classForm->relnamespace,
+							HASH_FIND, &found);
+		if (!found)
+			continue;
+
+		order = entry->order;
+
+		/* Keep the scan bounded in very large catalogs. */
+		if (++checked > MAX_REL_HINT_CANDIDATES)
+			break;
+
+		/*
+		 * 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,
+												 Min(fuzzystate->distance + 1, MAX_FUZZY_DISTANCE + 1),
+												 true);
+
+		/* The above can return MAX_FUZZY_DISTANCE + 1 results, skip these */
+		if (reldistance > MAX_FUZZY_DISTANCE)
+			continue;
+
+		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 &&
+			 order < fuzzystate->search_path_pos) ||
+			(reldistance == fuzzystate->distance &&
+			 order == 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 = order;
+			fuzzystate->relname         = pstrdup(candidate);
+			fuzzystate->nspname         = get_namespace_name(classForm->relnamespace);
+		}
+	}
+
+	table_endscan(scan);
+	table_close(classRel, AccessShareLock);
+	list_free(schemas);
+	hash_destroy(schema_order);
+
+	/* 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