Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit537ca68

Browse files
committed
Identify simple column references in extended statistics
Until now, when defining extended statistics, everything except a plaincolumn reference was treated as complex expression. So for example "a"was a column reference, but "(a)" would be an expression. In most casesthis does not matter much, but there were a couple strange consequences.For example CREATE STATISTICS s ON a FROM t;would fail, because extended stats require at least two columns. But CREATE STATISTICS s ON (a) FROM t;would succeed, because that requirement does not apply to expressions.Moreover, that statistics object is useless - the optimizer will alwaysuse the regular statistics collected for attribute "a".So do a bit more work to identify those expressions referencing a singlecolumn, and translate them to a simple column reference. Backpatch to14, where support for extended statistics on expressions was introduced.Reported-by: Justin PryzbyBackpatch-through: 14Discussion:https://postgr.es/m/20210816013255.GS10479%40telsasoft.com
1 parentb0c0662 commit537ca68

File tree

3 files changed

+34
-6
lines changed

3 files changed

+34
-6
lines changed

‎src/backend/commands/statscmds.c

Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@
3333
#include"optimizer/optimizer.h"
3434
#include"statistics/statistics.h"
3535
#include"utils/builtins.h"
36+
#include"utils/lsyscache.h"
3637
#include"utils/fmgroids.h"
3738
#include"utils/inval.h"
3839
#include"utils/memutils.h"
@@ -211,13 +212,15 @@ CreateStatistics(CreateStatsStmt *stmt)
211212
/*
212213
* Convert the expression list to a simple array of attnums, but also keep
213214
* a list of more complex expressions. While at it, enforce some
214-
* constraints.
215+
* constraints - we don't allow extended statistics on system attributes,
216+
* and we require the data type to have less-than operator.
215217
*
216-
* XXX We do only the bare minimum to separate simple attribute and
217-
* complex expressions - for example "(a)" will be treated as a complex
218-
* expression. No matter how elaborate the check is, there'll always be a
219-
* way around it, if the user is determined (consider e.g. "(a+0)"), so
220-
* it's not worth protecting against it.
218+
* There are many ways how to "mask" a simple attribute refenrece as an
219+
* expression, for example "(a+0)" etc. We can't possibly detect all of
220+
* them, but we handle at least the simple case with attribute in parens.
221+
* There'll always be a way around this, if the user is determined (like
222+
* the "(a+0)" example), but this makes it somewhat consistent with how
223+
* indexes treat attributes/expressions.
221224
*/
222225
foreach(cell,stmt->exprs)
223226
{
@@ -258,6 +261,28 @@ CreateStatistics(CreateStatsStmt *stmt)
258261
nattnums++;
259262
ReleaseSysCache(atttuple);
260263
}
264+
elseif (IsA(selem->expr,Var))/* column reference in parens */
265+
{
266+
Var*var= (Var*)selem->expr;
267+
TypeCacheEntry*type;
268+
269+
/* Disallow use of system attributes in extended stats */
270+
if (var->varattno <=0)
271+
ereport(ERROR,
272+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
273+
errmsg("statistics creation on system columns is not supported")));
274+
275+
/* Disallow data types without a less-than operator */
276+
type=lookup_type_cache(var->vartype,TYPECACHE_LT_OPR);
277+
if (type->lt_opr==InvalidOid)
278+
ereport(ERROR,
279+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
280+
errmsg("column \"%s\" cannot be used in statistics because its type %s has no default btree operator class",
281+
get_attname(relid,var->varattno, false),format_type_be(var->vartype))));
282+
283+
attnums[nattnums]=var->varattno;
284+
nattnums++;
285+
}
261286
else/* expression */
262287
{
263288
Node*expr=selem->expr;

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,8 @@ ERROR: duplicate expression in statistics definition
5555
CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
5656
ERROR: unrecognized statistics kind "unrecognized"
5757
-- incorrect expressions
58+
CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
59+
ERROR: extended statistics require at least 2 columns
5860
CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses
5961
ERROR: syntax error at or near "+"
6062
LINE 1: CREATE STATISTICS tst ON y + z FROM ext_stats_test;

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x')
4141
CREATE STATISTICS tstON (x||'x'), (x||'x'), yFROM ext_stats_test;
4242
CREATE STATISTICS tst (unrecognized)ON x, yFROM ext_stats_test;
4343
-- incorrect expressions
44+
CREATE STATISTICS tstON (y)FROM ext_stats_test;-- single column reference
4445
CREATE STATISTICS tstON y+ zFROM ext_stats_test;-- missing parentheses
4546
CREATE STATISTICS tstON (x, y)FROM ext_stats_test;-- tuple expression
4647
DROPTABLE ext_stats_test;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp