Per sqlsmith.
postgres=# SELECT pg_get_statisticsobjdef_expressions(123);
ERROR: cache lookup failed for statistics object 123
postgres=# \errverbose
ERROR: XX000: cache lookup failed for statistics object 123
LOCATION: pg_get_statisticsobjdef_expressions, ruleutils.c:1762
The expectation is that sql callable functions should return null rather than
hitting elog().
In the 003 patch, I wonder if this part should be updated, too:
| ... which can greatly improve query plans that use the expression index.
It can improve queries even that don't use the index, right ?
Say, if a query has f(x) = 11, and the MCV list for the expression shows that
50% of the table has f(x)=11, then the query might decide to *not* use an index
scan.
--
Justin
>From 6dec09300e4ad6cc7977acbfee9db7087420a9b5 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Wed, 5 May 2021 04:29:00 -0500
Subject: [PATCH 1/3] Return NULL rather than elog(ERROR) for sql-callable
function
---
src/backend/utils/adt/ruleutils.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0a4fa93d01..881e8ec03d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1759,9 +1759,9 @@ pg_get_statisticsobjdef_expressions(PG_FUNCTION_ARGS)
statexttup = SearchSysCache1(STATEXTOID, ObjectIdGetDatum(statextid));
if (!HeapTupleIsValid(statexttup))
- elog(ERROR, "cache lookup failed for statistics object %u", statextid);
+ PG_RETURN_NULL();
- /* has the statistics expressions? */
+ /* Does the stats object have expressions? */
has_exprs = !heap_attisnull(statexttup, Anum_pg_statistic_ext_stxexprs, NULL);
/* no expressions? we're done */
--
2.17.0
>From ede54e64cf5e2249fe6910d6f2c0de177a0edb9b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 27 Apr 2021 07:57:50 -0500
Subject: [PATCH 2/3] Comment typos: extended stats a4d75c86b and 518442c7f
---
src/backend/parser/parse_utilcmd.c | 2 +-
src/backend/statistics/extended_stats.c | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 9dd30370da..eb9e63f4a8 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1943,7 +1943,7 @@ generateClonedExtStatsStmt(RangeVar *heapRel, Oid heapRelid,
* simply append them after simple column references.
*
* XXX Some places during build/estimation treat expressions as if they
- * are before atttibutes, but for the CREATE command that's entirely
+ * are before attributes, but for the CREATE command that's entirely
* irrelevant.
*/
datum = SysCacheGetAttr(STATEXTOID, ht_stats,
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index 7e11cb9d5f..5e53783ea6 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -1796,7 +1796,7 @@ statext_mcv_clauselist_selectivity(PlannerInfo *root, List *clauses, int varReli
continue;
/*
- * Now we know the clause is compatible (we have either atttnums
+ * Now we know the clause is compatible (we have either attnums
* or expressions extracted from it), and was not estimated yet.
*/
--
2.17.0
>From e64e4b3d206d76ed8bbd5345798e0d35958759bd Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 23 Apr 2021 09:15:40 -0500
Subject: [PATCH 3/3] Mention statistics objects
Previously mentioned at 20210423025012.gi7...@telsasoft.com
---
doc/src/sgml/maintenance.sgml | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index ee6113926a..de7fd75e1c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -330,7 +330,8 @@
<para>
Also, by default there is limited information available about
- the selectivity of functions. However, if you create an expression
+ the selectivity of functions. However, if you create a statistics
+ object or an expression
index that uses a function call, useful statistics will be
gathered about the function, which can greatly improve query
plans that use the expression index.
--
2.17.0
