🌐 AI搜索 & 代理 主页
Skip to content

Commit f225473

Browse files
alvherrealvherre
committed
CREATE STATISTICS: improve misleading error message
I think the error message for a different condition was inadvertently copied. This problem seems to have been introduced by commit a4d75c8. Author: Álvaro Herrera <alvherre@kurilemu.de> Reported-by: jian he <jian.universality@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Backpatch-through: 14 Discussion: https://postgr.es/m/CACJufxEZ48toGH0Em_6vdsT57Y3L8pLF=DZCQ_gCii6=C3MeXw@mail.gmail.com
1 parent 5d7f588 commit f225473

File tree

3 files changed

+40
-1
lines changed

3 files changed

+40
-1
lines changed

‎src/backend/tcop/utility.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1874,7 +1874,8 @@ ProcessUtilitySlow(ParseState *pstate,
18741874
if (!IsA(rel, RangeVar))
18751875
ereport(ERROR,
18761876
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1877-
errmsg("only a single relation is allowed in CREATE STATISTICS")));
1877+
errmsg("cannot create statistics on the specified relation"),
1878+
errdetail("CREATE STATISTICS only supports tables, foreign tables and materialized views.")));
18781879

18791880
/*
18801881
* CREATE STATISTICS will influence future execution plans

‎src/test/regress/expected/stats_ext.out‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,32 @@ CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test;
5454
ERROR: duplicate expression in statistics definition
5555
CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
5656
ERROR: unrecognized statistics kind "unrecognized"
57+
-- unsupported targets
58+
CREATE STATISTICS tst ON a FROM (VALUES (x)) AS foo;
59+
ERROR: cannot create statistics on the specified relation
60+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
61+
CREATE STATISTICS tst ON a FROM foo NATURAL JOIN bar;
62+
ERROR: cannot create statistics on the specified relation
63+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
64+
CREATE STATISTICS tst ON a FROM (SELECT * FROM ext_stats_test) AS foo;
65+
ERROR: cannot create statistics on the specified relation
66+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
67+
CREATE STATISTICS tst ON a FROM ext_stats_test s TABLESAMPLE system (x);
68+
ERROR: cannot create statistics on the specified relation
69+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
70+
CREATE STATISTICS tst ON a FROM XMLTABLE('foo' PASSING 'bar' COLUMNS a text);
71+
ERROR: cannot create statistics on the specified relation
72+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
73+
CREATE STATISTICS tst ON a FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int));
74+
ERROR: cannot create statistics on the specified relation
75+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
76+
CREATE FUNCTION tftest(int) returns table(a int, b int) as $$
77+
SELECT $1, $1+i FROM generate_series(1,5) g(i);
78+
$$ LANGUAGE sql IMMUTABLE STRICT;
79+
CREATE STATISTICS alt_stat2 ON a FROM tftest(1);
80+
ERROR: cannot create statistics on the specified relation
81+
DETAIL: CREATE STATISTICS only supports tables, foreign tables and materialized views.
82+
DROP FUNCTION tftest;
5783
-- incorrect expressions
5884
CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
5985
ERROR: extended statistics require at least 2 columns

‎src/test/regress/sql/stats_ext.sql‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,18 @@ CREATE STATISTICS tst ON x, x, y, x, x, (x || 'x'), (y + 1), (x || 'x'), (x || '
4040
CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1) FROM ext_stats_test;
4141
CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test;
4242
CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
43+
-- unsupported targets
44+
CREATE STATISTICS tst ON a FROM (VALUES (x)) AS foo;
45+
CREATE STATISTICS tst ON a FROM foo NATURAL JOIN bar;
46+
CREATE STATISTICS tst ON a FROM (SELECT * FROM ext_stats_test) AS foo;
47+
CREATE STATISTICS tst ON a FROM ext_stats_test s TABLESAMPLE system (x);
48+
CREATE STATISTICS tst ON a FROM XMLTABLE('foo' PASSING 'bar' COLUMNS a text);
49+
CREATE STATISTICS tst ON a FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int));
50+
CREATE FUNCTION tftest(int) returns table(a int, b int) as $$
51+
SELECT $1, $1+i FROM generate_series(1,5) g(i);
52+
$$ LANGUAGE sql IMMUTABLE STRICT;
53+
CREATE STATISTICS alt_stat2 ON a FROM tftest(1);
54+
DROP FUNCTION tftest;
4355
-- incorrect expressions
4456
CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
4557
CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses

0 commit comments

Comments
 (0)