Re: Improving the names generated for indexes on expressions
| От | Tom Lane |
|---|---|
| Тема | Re: Improving the names generated for indexes on expressions |
| Дата | |
| Msg-id | 238409.1762036106@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: Improving the names generated for indexes on expressions (Robert Haas <robertmhaas@gmail.com>) |
| Список | pgsql-hackers |
[ Nobody has had any better ideas in 2 months, so back to this ... ]
Robert Haas <robertmhaas@gmail.com> writes:
> Do you think there's any way of doing this usefully while not
> autogenerating names that require quoting, or is that a hopeless
> endeavor? If it's hopeless, is it better to accept autogenerated names
> that require quoting, or is it better to solve the problem on the
> other thread with something more like what you proposed there?
If people are dead set against double-quoting, I think the only way
forward is to not include operator names in the generated index names.
I do not think that's a better way personally, but I seem to be
outvoted. Here's a v2 that does it like that.
regards, tom lane
From 8bc8722d7bb87cc1daa889b9724e4b6211baa9ad Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Nov 2025 18:23:26 -0400
Subject: [PATCH v2] Improve the names generated for indexes on expressions.
If the user doesn't specify a name for an index, it's generated
based on the names chosen for the index columns (which the user
has no control over). For index columns that are just columns
of the base relation, the index column name is the same as the
base column name; but for index columns that are expressions,
it's less clear what to do. Up to now, what we have done is
equivalent to the heuristics used to choose SELECT output
column names, except that we fall back to "expr" not "?column?"
in the numerous cases that FigureColname doesn't know what
to do with. This frequently leads to collisions of generated
index names, which we can handle but only at the cost of
user confusion; also there's some risk of concurrent index
creations trying to use the same name. Let's try to do better.
Messing with the FigureColname heuristics would have a very
large blast radius, since that affects the column headings
that applications see. That doesn't seem wise, but fortunately
SQL queries are seldom directly concerned with index names.
So we should be able to change the index-name generation rules
as long as we decouple them from FigureColname.
The rule proposed in this patch is to dig through the expression,
extract the names of Vars, the string representations of Consts,
and the names of functions, and run those together with underscores
between. Other expression node types are ignored but descended
through. We could work harder by handling more node types, but
it seems like this is likely to be sufficient to arrive at unique
index names in many cases.
Unlike the previous iteration of this patch, I didn't include
operator names in generated index names. I'm not convinced that's
better, but some other people are awfully resistant to the idea
of having to double-quote index names (despite the existence of
many cases where you'd have to anyway).
I've also chosen to strip Const representations down to only
alphanumeric characters (plus non-ASCII characters, which our
parser treats as alphabetic anyway). So for example "x + 1.0"
would be represented as "x_10". This likewise avoids possible
quoting problems.
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/18959-f63b53b864bb1417@postgresql.org
---
contrib/seg/expected/partition.out | 2 +-
src/backend/commands/indexcmds.c | 137 +++++++++++++++++-
src/backend/parser/parse_target.c | 16 --
src/backend/parser/parse_utilcmd.c | 6 +-
src/include/nodes/parsenodes.h | 3 +-
src/include/parser/parse_target.h | 1 -
src/test/regress/expected/alter_table.out | 6 +-
src/test/regress/expected/create_index.out | 18 +--
src/test/regress/expected/create_table.out | 4 +-
.../regress/expected/create_table_like.out | 8 +-
src/test/regress/expected/indexing.out | 84 +++++------
src/test/regress/expected/inherit.out | 114 +++++++--------
src/test/regress/expected/rangetypes.out | 6 +-
src/test/regress/expected/stats_import.out | 16 +-
src/test/regress/sql/create_index.sql | 2 +-
src/tools/pgindent/typedefs.list | 1 +
16 files changed, 265 insertions(+), 159 deletions(-)
diff --git a/contrib/seg/expected/partition.out b/contrib/seg/expected/partition.out
index 90d8397d5d4..10a86ec78fe 100644
--- a/contrib/seg/expected/partition.out
+++ b/contrib/seg/expected/partition.out
@@ -48,7 +48,7 @@ Partitions: pt12 FOR VALUES IN (1, 2),
Partition of: pt FOR VALUES IN (1, 2)
Partition constraint: ((category IS NOT NULL) AND (category = ANY (ARRAY[1, 2])))
Indexes:
- "pt12_expr_idx" btree ((mydouble(category) + 1))
+ "pt12_mydouble_category_1_idx" btree ((mydouble(category) + 1))
"pt12_sdata_idx" btree (sdata)
"pt12_tdata_idx" btree (tdata COLLATE mycollation)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5712fac3697..229d750d15e 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -72,6 +72,13 @@
#include "utils/syscache.h"
+/* context for ChooseIndexExpressionName_walker */
+typedef struct CIEN_context
+{
+ Relation rel; /* index's parent relation */
+ StringInfo buf; /* output string */
+} CIEN_context;
+
/* non-export function prototypes */
static bool CompareOpclassOptions(const Datum *opts1, const Datum *opts2, int natts);
static void CheckPredicate(Expr *predicate);
@@ -96,7 +103,10 @@ static char *ChooseIndexName(const char *tabname, Oid namespaceId,
const List *colnames, const List *exclusionOpNames,
bool primary, bool isconstraint);
static char *ChooseIndexNameAddition(const List *colnames);
-static List *ChooseIndexColumnNames(const List *indexElems);
+static List *ChooseIndexColumnNames(Relation rel, const List *indexElems);
+static char *ChooseIndexExpressionName(Relation rel, Node *indexExpr);
+static bool ChooseIndexExpressionName_walker(Node *node,
+ CIEN_context *context);
static void ReindexIndex(const ReindexStmt *stmt, const ReindexParams *params,
bool isTopLevel);
static void RangeVarCallbackForReindexIndex(const RangeVar *relation,
@@ -819,7 +829,7 @@ DefineIndex(Oid tableId,
/*
* Choose the index column names.
*/
- indexColNames = ChooseIndexColumnNames(allIndexParams);
+ indexColNames = ChooseIndexColumnNames(rel, allIndexParams);
/*
* Select name for index if caller didn't specify
@@ -2753,13 +2763,14 @@ ChooseIndexNameAddition(const List *colnames)
/*
* Select the actual names to be used for the columns of an index, given the
- * list of IndexElems for the columns. This is mostly about ensuring the
- * names are unique so we don't get a conflicting-attribute-names error.
+ * parent Relation and the list of IndexElems for the columns. The logic in
+ * this function is mostly about ensuring the names are unique so we don't
+ * get a conflicting-attribute-names error.
*
* Returns a List of plain strings (char *, not String nodes).
*/
static List *
-ChooseIndexColumnNames(const List *indexElems)
+ChooseIndexColumnNames(Relation rel, const List *indexElems)
{
List *result = NIL;
ListCell *lc;
@@ -2778,7 +2789,7 @@ ChooseIndexColumnNames(const List *indexElems)
else if (ielem->name)
origname = ielem->name; /* simple column reference */
else
- origname = "expr"; /* default name for expression */
+ origname = ChooseIndexExpressionName(rel, ielem->expr);
/* If it conflicts with any previous column, tweak it */
curname = origname;
@@ -2812,6 +2823,120 @@ ChooseIndexColumnNames(const List *indexElems)
return result;
}
+/*
+ * Generate a suitable index-column name for an index expression.
+ *
+ * Our strategy is to collect the Var names, Const values, and function names
+ * appearing in the expression, and print them separated by underscores.
+ * We could expend a lot more effort to handle additional expression node
+ * types, but this seems sufficient to usually produce a column name distinct
+ * from other index expressions.
+ */
+static char *
+ChooseIndexExpressionName(Relation rel, Node *indexExpr)
+{
+ StringInfoData buf;
+ CIEN_context context;
+ int nlen;
+
+ /* Prepare ... */
+ initStringInfo(&buf);
+ context.rel = rel;
+ context.buf = &buf;
+ /* Walk the tree, stopping when we have enough text */
+ (void) ChooseIndexExpressionName_walker(indexExpr, &context);
+ /* Ensure generated names are shorter than NAMEDATALEN */
+ nlen = pg_mbcliplen(buf.data, buf.len, NAMEDATALEN - 1);
+ buf.data[nlen] = '\0';
+ return buf.data;
+}
+
+/* Recursive guts of ChooseIndexExpressionName */
+static bool
+ChooseIndexExpressionName_walker(Node *node,
+ CIEN_context *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ TupleDesc tupdesc = RelationGetDescr(context->rel);
+ Form_pg_attribute att;
+
+ /* Paranoia: ignore the Var if it looks fishy */
+ if (var->varno != 1 || var->varlevelsup != 0 ||
+ var->varattno <= 0 || var->varattno > tupdesc->natts)
+ return false;
+ att = TupleDescAttr(tupdesc, var->varattno - 1);
+ if (att->attisdropped)
+ return false; /* even more paranoia; shouldn't happen */
+
+ if (context->buf->len > 0)
+ appendStringInfoChar(context->buf, '_');
+ appendStringInfoString(context->buf, NameStr(att->attname));
+
+ /* Done if we've already reached NAMEDATALEN */
+ return (context->buf->len >= NAMEDATALEN);
+ }
+ else if (IsA(node, Const))
+ {
+ Const *constval = (Const *) node;
+
+ if (context->buf->len > 0)
+ appendStringInfoChar(context->buf, '_');
+ if (constval->constisnull)
+ appendStringInfoString(context->buf, "NULL");
+ else
+ {
+ Oid typoutput;
+ bool typIsVarlena;
+ char *extval;
+
+ getTypeOutputInfo(constval->consttype,
+ &typoutput, &typIsVarlena);
+ extval = OidOutputFunctionCall(typoutput, constval->constvalue);
+
+ /*
+ * We sanitize constant values by dropping non-alphanumeric ASCII
+ * characters. This is probably not really necessary, but it
+ * reduces the odds of needing to double-quote the generated name.
+ */
+ for (const char *ptr = extval; *ptr; ptr++)
+ {
+ if (IS_HIGHBIT_SET(*ptr) ||
+ strchr("ABCDEFGHIJKLMNOPQRSTUVWXYZ"
+ "abcdefghijklmnopqrstuvwxyz"
+ "0123456789", *ptr) != NULL)
+ appendStringInfoChar(context->buf, *ptr);
+ }
+ }
+
+ /* Done if we've already reached NAMEDATALEN */
+ return (context->buf->len >= NAMEDATALEN);
+ }
+ else if (IsA(node, FuncExpr))
+ {
+ FuncExpr *funcexpr = (FuncExpr *) node;
+ char *fname = get_func_name(funcexpr->funcid);
+
+ if (fname)
+ {
+ if (context->buf->len > 0)
+ appendStringInfoChar(context->buf, '_');
+ appendStringInfoString(context->buf, fname);
+ }
+ /* fall through to examine arguments */
+ }
+
+ /* Abandon recursion once we reach NAMEDATALEN */
+ if (context->buf->len >= NAMEDATALEN)
+ return true;
+
+ return expression_tree_walker(node, ChooseIndexExpressionName_walker,
+ context);
+}
+
/*
* ExecReindex
*
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 905c975d83b..f7a73f8e6c2 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1720,22 +1720,6 @@ FigureColname(Node *node)
return "?column?";
}
-/*
- * FigureIndexColname -
- * choose the name for an expression column in an index
- *
- * This is actually just like FigureColname, except we return NULL if
- * we can't pick a good name.
- */
-char *
-FigureIndexColname(Node *node)
-{
- char *name = NULL;
-
- (void) FigureColnameInternal(node, &name);
- return name;
-}
-
/*
* FigureColnameInternal -
* internal workhorse for FigureColname
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..fd9ab79113f 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -3092,11 +3092,7 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString)
if (ielem->expr)
{
- /* Extract preliminary index col name before transforming expr */
- if (ielem->indexcolname == NULL)
- ielem->indexcolname = FigureIndexColname(ielem->expr);
-
- /* Now do parse transformation of the expression */
+ /* Do parse transformation of the expression */
ielem->expr = transformExpr(pstate, ielem->expr,
EXPR_KIND_INDEX_EXPRESSION);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ecbddd12e1b..c95d4f3e647 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -803,7 +803,8 @@ typedef enum TableLikeOption
*
* For a plain index attribute, 'name' is the name of the table column to
* index, and 'expr' is NULL. For an index expression, 'name' is NULL and
- * 'expr' is the expression tree.
+ * 'expr' is the expression tree. indexcolname is currently used only to
+ * force column name choices when cloning an index.
*/
typedef struct IndexElem
{
diff --git a/src/include/parser/parse_target.h b/src/include/parser/parse_target.h
index 89357f31ad6..87bf0410ed2 100644
--- a/src/include/parser/parse_target.h
+++ b/src/include/parser/parse_target.h
@@ -53,6 +53,5 @@ extern List *checkInsertTargets(ParseState *pstate, List *cols,
extern TupleDesc expandRecordVariable(ParseState *pstate, Var *var,
int levelsup);
extern char *FigureColname(Node *node);
-extern char *FigureIndexColname(Node *node);
#endif /* PARSE_TARGET_H */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 5e98bbf2425..7f6fba3ea92 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -109,7 +109,7 @@ ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS 1000;
Column | Type | Key? | Definition | Storage | Stats target
--------+------------------+------+------------+---------+--------------
a | integer | yes | a | plain |
- expr | double precision | yes | (d + e) | plain | 1000
+ d_e | double precision | yes | (d + e) | plain | 1000
b | cstring | yes | b | plain |
btree, for table "public.attmp"
@@ -3190,7 +3190,7 @@ DROP TABLE test_tbl1;
CREATE TABLE test_tbl1 (x int, y text);
CREATE INDEX test_tbl1_idx ON test_tbl1((row(x,y)::test_type1));
ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
-ERROR: cannot alter type "test_type1" because column "test_tbl1_idx.row" uses it
+ERROR: cannot alter type "test_type1" because column "test_tbl1_idx.x_y" uses it
DROP TABLE test_tbl1;
DROP TYPE test_type1;
CREATE TYPE test_type2 AS (a int, b text);
@@ -3904,7 +3904,7 @@ ALTER TABLE ataddindex
--------+------+-----------+----------+---------
f1 | text | | |
Indexes:
- "ataddindex_expr_excl" EXCLUDE USING btree ((f1 ~~ 'a'::text) WITH =)
+ "ataddindex_f1_a_excl" EXCLUDE USING btree ((f1 ~~ 'a'::text) WITH =)
DROP TABLE ataddindex;
CREATE TABLE ataddindex(id int, ref_id int);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index c743fc769cb..5aea2d98b1d 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1339,10 +1339,10 @@ Indexes:
"func_index_index" UNIQUE, btree (textcat(f1, f2))
\d func_index_index
- Index "public.func_index_index"
- Column | Type | Key? | Definition
----------+------+------+-----------------
- textcat | text | yes | textcat(f1, f2)
+ Index "public.func_index_index"
+ Column | Type | Key? | Definition
+---------------+------+------+-----------------
+ textcat_f1_f2 | text | yes | textcat(f1, f2)
unique, btree, for table "public.func_index_heap"
--
@@ -1374,12 +1374,12 @@ Indexes:
Index "public.func_index_index"
Column | Type | Key? | Definition
--------+------+------+------------
- expr | text | yes | (f1 || f2)
+ f1_f2 | text | yes | (f1 || f2)
unique, btree, for table "public.func_index_heap"
-- this should fail because of unsafe column type (anonymous record)
create index on func_index_heap ((f1 || f2), (row(f1, f2)));
-ERROR: column "row" has pseudo-type record
+ERROR: column "f1_f21" has pseudo-type record
--
-- Test unique index with included columns
--
@@ -1463,7 +1463,7 @@ VACUUM FULL concur_heap;
f1 | text | | |
f2 | text | | |
Indexes:
- "concur_heap_expr_idx" btree ((f2 || f1))
+ "concur_heap_f2_f1_idx" btree ((f2 || f1))
"concur_index1" btree (f2, f1)
"concur_index2" UNIQUE, btree (f1)
"concur_index3" UNIQUE, btree (f2) INVALID
@@ -1479,7 +1479,7 @@ REINDEX TABLE concur_heap;
f1 | text | | |
f2 | text | | |
Indexes:
- "concur_heap_expr_idx" btree ((f2 || f1))
+ "concur_heap_f2_f1_idx" btree ((f2 || f1))
"concur_index1" btree (f2, f1)
"concur_index2" UNIQUE, btree (f1)
"concur_index3" UNIQUE, btree (f2)
@@ -1530,7 +1530,7 @@ DROP INDEX CONCURRENTLY IF EXISTS "concur_index3";
DROP INDEX CONCURRENTLY "concur_index4";
DROP INDEX CONCURRENTLY "concur_index5";
DROP INDEX CONCURRENTLY "concur_index1";
-DROP INDEX CONCURRENTLY "concur_heap_expr_idx";
+DROP INDEX CONCURRENTLY "concur_heap_f2_f1_idx";
\d concur_heap
Table "public.concur_heap"
Column | Type | Collation | Nullable | Default
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 029beb20aae..95f029d4126 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1137,9 +1137,9 @@ Number of partitions: 1 (Use \d+ to list them.)
b | integer | | |
Partition of: part_column_drop FOR VALUES FROM (1) TO (10)
Indexes:
+ "part_column_drop_1_10_b_1_idx" btree ((b = 1))
"part_column_drop_1_10_b_idx" btree (b) WHERE b = 1
+ "part_column_drop_1_10_d_2_idx" btree ((d = 2))
"part_column_drop_1_10_d_idx" btree (d) WHERE d = 2
- "part_column_drop_1_10_expr_idx" btree ((b = 1))
- "part_column_drop_1_10_expr_idx1" btree ((d = 2))
drop table part_column_drop;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..62f027f13c5 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -432,7 +432,7 @@ NOTICE: merging column "a" with inherited definition
b | text | | | | extended | |
c | text | | | | external | | C
Indexes:
- "ctlt13_like_expr_idx" btree ((a || c))
+ "ctlt13_like_a_c_idx" btree ((a || c))
Check constraints:
"cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
@@ -458,8 +458,8 @@ CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
b | text | | | | extended | | B
Indexes:
"ctlt_all_pkey" PRIMARY KEY, btree (a)
+ "ctlt_all_a_b_idx" btree ((a || b))
"ctlt_all_b_idx" btree (b)
- "ctlt_all_expr_idx" btree ((a || b))
Check constraints:
"cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
@@ -502,8 +502,8 @@ CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL);
b | text | | | | extended | | B
Indexes:
"pg_attrdef_pkey" PRIMARY KEY, btree (a)
+ "pg_attrdef_a_b_idx" btree ((a || b))
"pg_attrdef_b_idx" btree (b)
- "pg_attrdef_expr_idx" btree ((a || b))
Check constraints:
"cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
@@ -528,8 +528,8 @@ CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL);
b | text | | | | extended | | B
Indexes:
"ctlt1_pkey" PRIMARY KEY, btree (a)
+ "ctlt1_a_b_idx" btree ((a || b))
"ctlt1_b_idx" btree (b)
- "ctlt1_expr_idx" btree ((a || b))
Check constraints:
"cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 4d29fb85293..6cc73bb682e 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -274,11 +274,11 @@ alter table idxpart attach partition idxpart1 for values from (0) to (1000);
b | integer | | |
Partition of: idxpart FOR VALUES FROM (0) TO (1000)
Indexes:
+ "idxpart1_a_0_idx" btree ((a + 0))
"idxpart1_a_a1_idx" btree (a, a)
"idxpart1_a_idx" hash (a)
"idxpart1_a_idx1" btree (a) WHERE b > 1
"idxpart1_a_idx2" btree (a)
- "idxpart1_expr_idx" btree ((a + 0))
drop table idxpart;
-- If CREATE INDEX ONLY, don't create indexes on partitions; and existing
@@ -448,23 +448,23 @@ create index on idxpart1 ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
+ "idxpart1_a_b_idx" btree ((a + b)) WHERE d = true
"idxpart1_a_idx" btree (a)
"idxpart1_b_c_idx" btree (b, c)
- "idxpart1_expr_idx" btree ((a + b)) WHERE d = true
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
left join pg_inherits on (ix.indexrelid = inhrelid)
where relname like 'idxpart%' order by relname;
- relname | relkind | inhparent
--------------------+---------+-----------
- idxpart | p |
- idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
- idxpart1_expr_idx | i |
- idxparti | I |
- idxparti2 | I |
+ relname | relkind | inhparent
+------------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxpart1_a_b_idx | i |
+ idxpart1_a_idx | i | idxparti
+ idxpart1_b_c_idx | i | idxparti2
+ idxparti | I |
+ idxparti2 | I |
(7 rows)
create index idxparti3 on idxpart ((a+b)) where d = true;
@@ -478,24 +478,24 @@ create index idxparti3 on idxpart ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
+ "idxpart1_a_b_idx" btree ((a + b)) WHERE d = true
"idxpart1_a_idx" btree (a)
"idxpart1_b_c_idx" btree (b, c)
- "idxpart1_expr_idx" btree ((a + b)) WHERE d = true
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
left join pg_inherits on (ix.indexrelid = inhrelid)
where relname like 'idxpart%' order by relname;
- relname | relkind | inhparent
--------------------+---------+-----------
- idxpart | p |
- idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
- idxpart1_expr_idx | i | idxparti3
- idxparti | I |
- idxparti2 | I |
- idxparti3 | I |
+ relname | relkind | inhparent
+------------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxpart1_a_b_idx | i | idxparti3
+ idxpart1_a_idx | i | idxparti
+ idxpart1_b_c_idx | i | idxparti2
+ idxparti | I |
+ idxparti2 | I |
+ idxparti3 | I |
(8 rows)
drop table idxpart;
@@ -667,11 +667,11 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
from pg_class join pg_inherits on inhrelid = oid,
lateral pg_get_indexdef(pg_class.oid)
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
- child | parent | childdef
--------------------+------------------+---------------------------------------------------------------------------
- idxpart1_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((a + b)))
- idxpart2_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((a + b)))
- idxpart3_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart3_expr_idx ON public.idxpart3 USING btree (((a + b)))
+ child | parent | childdef
+------------------+-----------------+--------------------------------------------------------------------------
+ idxpart1_a_b_idx | idxpart_a_b_idx | CREATE INDEX idxpart1_a_b_idx ON public.idxpart1 USING btree (((a + b)))
+ idxpart2_a_b_idx | idxpart_a_b_idx | CREATE INDEX idxpart2_a_b_idx ON public.idxpart2 USING btree (((a + b)))
+ idxpart3_a_b_idx | idxpart_a_b_idx | CREATE INDEX idxpart3_a_b_idx ON public.idxpart3 USING btree (((a + b)))
(3 rows)
drop table idxpart;
@@ -817,14 +817,14 @@ select c.relname, pg_get_indexdef(indexrelid)
from pg_class c join pg_index i on c.oid = i.indexrelid
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- relname | pg_get_indexdef
--------------------+------------------------------------------------------------------------------
- idxpart1_abs_idx | CREATE INDEX idxpart1_abs_idx ON public.idxpart1 USING btree (abs(b))
- idxpart1_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((b + 1)))
- idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USING btree (abs(b))
- idxpart2_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((b + 1)))
- idxpart_abs_idx | CREATE INDEX idxpart_abs_idx ON ONLY public.idxpart USING btree (abs(b))
- idxpart_expr_idx | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpart USING btree (((b + 1)))
+ relname | pg_get_indexdef
+--------------------+-----------------------------------------------------------------------------
+ idxpart1_abs_b_idx | CREATE INDEX idxpart1_abs_b_idx ON public.idxpart1 USING btree (abs(b))
+ idxpart1_b_1_idx | CREATE INDEX idxpart1_b_1_idx ON public.idxpart1 USING btree (((b + 1)))
+ idxpart2_abs_b_idx | CREATE INDEX idxpart2_abs_b_idx ON public.idxpart2 USING btree (abs(b))
+ idxpart2_b_1_idx | CREATE INDEX idxpart2_b_1_idx ON public.idxpart2 USING btree (((b + 1)))
+ idxpart_abs_b_idx | CREATE INDEX idxpart_abs_b_idx ON ONLY public.idxpart USING btree (abs(b))
+ idxpart_b_1_idx | CREATE INDEX idxpart_b_1_idx ON ONLY public.idxpart USING btree (((b + 1)))
(6 rows)
drop table idxpart;
@@ -1581,13 +1581,13 @@ select indexrelid::regclass, indisvalid,
pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'parted_isvalid%'
order by indexrelid::regclass::text collate "C";
- indexrelid | indisvalid | indrelid | inhparent
---------------------------------+------------+-----------------------+-------------------------------
- parted_isvalid_idx | f | parted_isvalid_tab |
- parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_expr_idx
- parted_isvalid_tab_12_expr_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_expr_idx
- parted_isvalid_tab_1_expr_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx
- parted_isvalid_tab_2_expr_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx
+ indexrelid | indisvalid | indrelid | inhparent
+-------------------------------+------------+-----------------------+------------------------------
+ parted_isvalid_idx | f | parted_isvalid_tab |
+ parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_a_b_idx
+ parted_isvalid_tab_12_a_b_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_a_b_idx
+ parted_isvalid_tab_1_a_b_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx
+ parted_isvalid_tab_2_a_b_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx
(5 rows)
drop table parted_isvalid_tab;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0490a746555..d8699010fe4 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -3282,45 +3282,45 @@ drop table parted_minmax;
create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c);
-- MergeAppend must be used when a default partition exists
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Merge Append
Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
- -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7
+ -> Index Scan using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted4_a_abs_b_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted5_a_abs_b_c_idx on mcrparted5 mcrparted_6
+ -> Index Scan using mcrparted_def_a_abs_b_c_idx on mcrparted_def mcrparted_7
(9 rows)
drop table mcrparted_def;
-- Append is used for a RANGE partitioned table with no default
-- and no subpartitions
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
+ -> Index Scan using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted4_a_abs_b_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted5_a_abs_b_c_idx on mcrparted5 mcrparted_6
(7 rows)
-- Append is used with subpaths in reverse order with backwards index scans
explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Append
- -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
- -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan Backward using mcrparted5_a_abs_b_c_idx on mcrparted5 mcrparted_6
+ -> Index Scan Backward using mcrparted4_a_abs_b_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan Backward using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan Backward using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan Backward using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan Backward using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
(7 rows)
-- check that Append plan is used containing a MergeAppend for sub-partitions
@@ -3330,18 +3330,18 @@ create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (
create table mcrparted5a partition of mcrparted5 for values in(20);
create table mcrparted5_def partition of mcrparted5 default;
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted4_a_abs_b_c_idx on mcrparted4 mcrparted_5
-> Merge Append
Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c
- -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7
- -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8
+ -> Index Scan using mcrparted5a_a_abs_b_c_idx on mcrparted5a mcrparted_7
+ -> Index Scan using mcrparted5_def_a_abs_b_c_idx on mcrparted5_def mcrparted_8
(10 rows)
drop table mcrparted5_def;
@@ -3349,30 +3349,30 @@ drop table mcrparted5_def;
-- into the main Append when the sub-partition is unordered but contains
-- just a single sub-partition.
explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6
+ -> Index Scan using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted4_a_abs_b_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted5a_a_abs_b_c_idx on mcrparted5a mcrparted_6
(7 rows)
-- check that Append is used when the sub-partitioned tables are pruned
-- during planning.
explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted0_a_abs_b_c_idx on mcrparted0 mcrparted_1
Index Cond: (a < 20)
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
Index Cond: (a < 20)
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
Index Cond: (a < 20)
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
(9 rows)
@@ -3521,19 +3521,19 @@ create index on mcrparted2 (a, abs(b), c);
create index on mcrparted3 (a, abs(b), c);
create index on mcrparted4 (a, abs(b), c);
explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Limit
-> Append
-> Sort
Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c
-> Seq Scan on mcrparted0 mcrparted_1
Filter: (a < 20)
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_2
Index Cond: (a < 20)
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_3
Index Cond: (a < 20)
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted3_a_abs_b_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
(12 rows)
@@ -3541,12 +3541,12 @@ set enable_bitmapscan = 0;
-- Ensure Append node can be used when the partition is ordered by some
-- pathkeys which were deemed redundant.
explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1
+ -> Index Scan using mcrparted1_a_abs_b_c_idx on mcrparted1 mcrparted_1
Index Cond: (a = 10)
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2
+ -> Index Scan using mcrparted2_a_abs_b_c_idx on mcrparted2 mcrparted_2
Index Cond: (a = 10)
(5 rows)
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index cdd95799cd5..3ba6f304610 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1485,10 +1485,10 @@ select count(*) from test_range_elem where i <@ int4range(10,50);
create index on test_range_elem using spgist(int4range(i,i+10));
explain (costs off)
select count(*) from test_range_elem where int4range(i,i+10) <@ int4range(10,30);
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Aggregate
- -> Index Scan using test_range_elem_int4range_idx on test_range_elem
+ -> Index Scan using test_range_elem_int4range_i_i_10_idx on test_range_elem
Index Cond: (int4range(i, (i + 10)) <@ '[10,30)'::int4range)
(3 rows)
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 98ce7dc2841..f0d6d71a37b 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1117,14 +1117,14 @@ CROSS JOIN LATERAL
WHERE s.schemaname = 'stats_import'
AND s.tablename IN ('test', 'is_odd')
ORDER BY s.tablename, s.attname, s.inherited;
- schemaname | tablename | attname | inherited | r
---------------+-----------+---------+-----------+---
- stats_import | is_odd | expr | f | t
- stats_import | test | arange | f | t
- stats_import | test | comp | f | t
- stats_import | test | id | f | t
- stats_import | test | name | f | t
- stats_import | test | tags | f | t
+ schemaname | tablename | attname | inherited | r
+--------------+-----------+----------+-----------+---
+ stats_import | is_odd | comp_2_1 | f | t
+ stats_import | test | arange | f | t
+ stats_import | test | comp | f | t
+ stats_import | test | id | f | t
+ stats_import | test | name | f | t
+ stats_import | test | tags | f | t
(6 rows)
SELECT c.relname, COUNT(*) AS num_stats
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index eabc9623b20..8bf9929e329 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -577,7 +577,7 @@ DROP INDEX CONCURRENTLY IF EXISTS "concur_index3";
DROP INDEX CONCURRENTLY "concur_index4";
DROP INDEX CONCURRENTLY "concur_index5";
DROP INDEX CONCURRENTLY "concur_index1";
-DROP INDEX CONCURRENTLY "concur_heap_expr_idx";
+DROP INDEX CONCURRENTLY "concur_heap_f2_f1_idx";
\d concur_heap
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 018b5919cf6..c45dad989d4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -367,6 +367,7 @@ CFuncHashTabEntry
CHAR
CHECKPOINT
CHKVAL
+CIEN_context
CIRCLE
CMPDAffix
CONTEXT
--
2.43.7
В списке pgsql-hackers по дате отправления: