From 4423352524c14456bb451222b0d86c3dd5afbb02 Mon Sep 17 00:00:00 2001 From: steve-chavez 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 +#include +#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