Thread: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 18959
Logged by: Maximilian Chrzan
Email address: maximilian.chrzan@here.com
PostgreSQL version: 17.4
Operating system: x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.
Description:
Dear PostgreSQL team,
We encountered a reproducible issue when creating expression indexes on a
partitioned table using:
CREATE INDEX IF NOT EXISTS ... ON partitioned_table ((expression));
When such statements are executed in parallel (e.g., via separate
transactions or threads), the PostgreSQL engine attempts to propagate the
index to each child partition using internally generated names like:
partition_name_expr_idx
partition_name_expr_idx1
partition_name_expr_idx2
...
These internal names are not derived from the index expression or parent
index name, but instead appear to be based on a counter of existing
expression indexes.
The Issue:
When multiple expressions are indexed in parallel on the same partitioned
table, even with distinct expressions and parent index names, the system may
generate the same internal name for partition-level indexes, causing:
(Postgres <17): ERROR: duplicate key value violates unique constraint
"pg_class_relname_nsp_index" 23505
(Postgres 17): relation "{index_name}" already exists 42P07
This occurs even though the parent-level index names are unique and
expressions differ.
Reproducer (simplified):
-- In separate sessions concurrently:
CREATE INDEX IF NOT EXISTS idx_expr1 ON parent_table (((jsondata -> 'a' ->
'b')));
CREATE INDEX IF NOT EXISTS idx_expr2 ON parent_table (((jsondata -> 'x' ->
'y')));
Internally, PostgreSQL attempts to create something like:
CREATE INDEX parent_table_partition1_expr_idx ON ...
CREATE INDEX parent_table_partition1_expr_idx ON ... -- collision
Expected behavior:
If expressions or parent index names differ, partition-level index names
should be derived deterministically from:
* Parent index name (preferred) eg.: parent_idx_name_partition1
* Or a hash of the expression (as fallback)
This would avoid internal naming collisions and allow safe concurrent
execution of CREATE INDEX IF NOT EXISTS on partitioned tables.
This issue limits scalability when programmatically creating multiple
JSON-path expression indexes on partitioned tables, and complicates use of
parallelism. While advisory locking is a possible workaround, it is not
ideal.
Thanks in advance for looking into it.
Best regards,
Max Chrzan
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Dilip Kumar
Date:
On Sat, Jun 14, 2025 at 3:15 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18959
> Logged by: Maximilian Chrzan
> Email address: maximilian.chrzan@here.com
> PostgreSQL version: 17.4
> Operating system: x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.
> Description:
>
> Dear PostgreSQL team,
> We encountered a reproducible issue when creating expression indexes on a
> partitioned table using:
> CREATE INDEX IF NOT EXISTS ... ON partitioned_table ((expression));
> When such statements are executed in parallel (e.g., via separate
> transactions or threads), the PostgreSQL engine attempts to propagate the
> index to each child partition using internally generated names like:
> partition_name_expr_idx
> partition_name_expr_idx1
> partition_name_expr_idx2
> ...
> These internal names are not derived from the index expression or parent
> index name, but instead appear to be based on a counter of existing
> expression indexes.
> The Issue:
> When multiple expressions are indexed in parallel on the same partitioned
> table, even with distinct expressions and parent index names, the system may
> generate the same internal name for partition-level indexes, causing:
> (Postgres <17): ERROR: duplicate key value violates unique constraint
> "pg_class_relname_nsp_index" 23505
> (Postgres 17): relation "{index_name}" already exists 42P07
> This occurs even though the parent-level index names are unique and
> expressions differ.
> Reproducer (simplified):
> -- In separate sessions concurrently:
> CREATE INDEX IF NOT EXISTS idx_expr1 ON parent_table (((jsondata -> 'a' ->
> 'b')));
> CREATE INDEX IF NOT EXISTS idx_expr2 ON parent_table (((jsondata -> 'x' ->
> 'y')));
> Internally, PostgreSQL attempts to create something like:
> CREATE INDEX parent_table_partition1_expr_idx ON ...
> CREATE INDEX parent_table_partition1_expr_idx ON ... -- collision
> Expected behavior:
> If expressions or parent index names differ, partition-level index names
> should be derived deterministically from:
> * Parent index name (preferred) eg.: parent_idx_name_partition1
> * Or a hash of the expression (as fallback)
> This would avoid internal naming collisions and allow safe concurrent
> execution of CREATE INDEX IF NOT EXISTS on partitioned tables.
> This issue limits scalability when programmatically creating multiple
> JSON-path expression indexes on partitioned tables, and complicates use of
> parallelism. While advisory locking is a possible workaround, it is not
> ideal.
It seems beneficial to embed the parent index name within the names of
its partitioned child indexes, although it would become tricky when
building an index for a multi level partition hierarchy but we could
simplify this by only referencing the top-level user-provided index
name. This is my perspective, and I'm open to other ideas.
--
Regards,
Dilip Kumar
Google
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Phineas Jensen
Date:
On Jun 18, 2025, at 4:29 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
It seems beneficial to embed the parent index name within the names of
its partitioned child indexes, although it would become tricky when
building an index for a multi level partition hierarchy but we could
simplify this by only referencing the top-level user-provided index
name. This is my perspective, and I'm open to other ideas.
I agree that embedding the parent index name would be the simplest solution for this case, but a similar bug would still happen if no index name was specified for the parent at all (e.g. CREATE INDEX ON parent_table ((jsondata->’a’->’b’)) ), although in that case, the conflict is on the parent table, not the child tables.
Would it be worth making CREATE INDEX add a short hash or some other unique key when no name is specified? Or does it make more sense to just say (maybe in the documentation) that if you are running CREATE INDEX multiple times concurrently that you should specify a name to avoid conflicts?
I created SQL and Bash scripts to reproduce the problem, which I’ve attached.
Phin Jensen
Attachment
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Tom Lane
Date:
Dilip Kumar <dilipbalaut@gmail.com> writes:
> On Sat, Jun 14, 2025 at 3:15 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> If expressions or parent index names differ, partition-level index names
>> should be derived deterministically from:
>> * Parent index name (preferred) eg.: parent_idx_name_partition1
>> * Or a hash of the expression (as fallback)
>> This would avoid internal naming collisions and allow safe concurrent
>> execution of CREATE INDEX IF NOT EXISTS on partitioned tables.
> It seems beneficial to embed the parent index name within the names of
> its partitioned child indexes, although it would become tricky when
> building an index for a multi level partition hierarchy but we could
> simplify this by only referencing the top-level user-provided index
> name. This is my perspective, and I'm open to other ideas.
This seems very closely related to commit 3db61db48 [1], which fixed
a similar behavior for child foreign key constraints. Per that commit
message, it's a good idea for the child objects to have names related
to the parent objects, so we ought to change this behavior regardless
of any concurrent-failure considerations.
Having said that, I do not think that the OP's idea of fully
deterministic index name choice is workable. We don't constrain
partitions to be exactly like their parents; that means that an index
name that works fine at an upper level might conflict with some
pre-existing index on a child. So unless you prefer failure to
selecting a different name at the child level, it's necessary to
allow the child index names to sometimes be different.
But ... the code *does* have the ability to dodge conflicting
index names already; this is why you get
partition_name_expr_idx
partition_name_expr_idx1
partition_name_expr_idx2
and not immediate failure. If this isn't working reliably in
concurrent situations, that must mean that we are not obtaining
an exclusive lock before looking for pre-existing index names.
I'm not sure if that's a bug or intentional. My vague recollection
is that we intend to allow multiple CREATE INDEX in parallel, so it
may be that obtaining a lock would be a cure worse than the disease.
In any case, deriving the child index name(s) from the parent name
would reduce the scope of this problem, so I agree we ought to
make it do that.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3db61db48
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Tom Lane
Date:
I wrote:
> This seems very closely related to commit 3db61db48 [1], which fixed
> a similar behavior for child foreign key constraints. Per that commit
> message, it's a good idea for the child objects to have names related
> to the parent objects, so we ought to change this behavior regardless
> of any concurrent-failure considerations.
I experimented with the attached, which borrows a couple of ideas
from 3db61db48 to produce names like "parent_index_2" when cloning
indexes. While it should help with the immediate problem, I'm not
sure if this is acceptable, because there are a *lot* of ensuing
changes in the regression tests, many more than 3db61db48 caused.
(Note that I didn't bother to fix places where the tests rely on
a generated name that has changed; the delta in the test outputs
is merely meant to give an idea of how much churn there is.
I didn't check non-core test suites, either.)
Also, looking at the error message changes, I'm less sure that
this is a UX improvement than I was about 3db61db48. Do people
care which partition a uniqueness constraint failed in? In
the current behavior, the index name will reflect that, but
with this behavior, not so much.
Anyway, maybe this is a good idea or maybe it isn't. Thoughts?
regards, tom lane
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index c3ec2076a52..8eb6f429383 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1367,6 +1367,7 @@ DefineIndex(Oid tableId,
{
Oid childRelid = part_oids[i];
Relation childrel;
+ Oid childNamespace;
Oid child_save_userid;
int child_save_sec_context;
int child_save_nestlevel;
@@ -1376,6 +1377,7 @@ DefineIndex(Oid tableId,
bool found = false;
childrel = table_open(childRelid, lockmode);
+ childNamespace = RelationGetNamespace(childrel);
GetUserIdAndSecContext(&child_save_userid,
&child_save_sec_context);
@@ -1507,6 +1509,7 @@ DefineIndex(Oid tableId,
* original IndexStmt might not be.
*/
childStmt = generateClonedIndexStmt(NULL,
+ childNamespace,
parentIndex,
attmap,
NULL);
@@ -2584,6 +2587,8 @@ makeObjectName(const char *name1, const char *name2, const char *label)
* name1, name2, and label are used the same way as for makeObjectName(),
* except that the label can't be NULL; digits will be appended to the label
* if needed to create a name that is unique within the specified namespace.
+ * If the given label is empty, we only consider names that include at least
+ * one added digit.
*
* If isconstraint is true, we also avoid choosing a name matching any
* existing constraint in the same namespace. (This is stricter than what
@@ -2609,8 +2614,11 @@ ChooseRelationName(const char *name1, const char *name2,
char *relname = NULL;
char modlabel[NAMEDATALEN];
- /* try the unmodified label first */
- strlcpy(modlabel, label, sizeof(modlabel));
+ /* try the unmodified label first, unless it's empty */
+ if (label[0] != '\0')
+ strlcpy(modlabel, label, sizeof(modlabel));
+ else
+ snprintf(modlabel, sizeof(modlabel), "%s%d", label, ++pass);
for (;;)
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ea96947d813..7fe357ffa0a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1295,7 +1295,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
RelationGetDescr(parent),
false);
idxstmt =
- generateClonedIndexStmt(NULL, idxRel,
+ generateClonedIndexStmt(NULL,
+ RelationGetNamespace(rel),
+ idxRel,
attmap, &constraintOid);
DefineIndex(RelationGetRelid(rel),
idxstmt,
@@ -20654,6 +20656,7 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel)
Oid conOid;
stmt = generateClonedIndexStmt(NULL,
+ RelationGetNamespace(attachrel),
idxRel, attmap,
&conOid);
DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid,
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 62015431fdf..df217c87da4 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1541,6 +1541,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
/* Build CREATE INDEX statement to recreate the parent_index */
index_stmt = generateClonedIndexStmt(heapRel,
+ RelationGetNamespace(childrel),
parent_index,
attmap,
NULL);
@@ -1656,6 +1657,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
* heapRel is stored into the IndexStmt's relation field, but we don't use it
* otherwise; some callers pass NULL, if they don't need it to be valid.
* (The target relation might not exist yet, so we mustn't try to access it.)
+ * The namespace OID for the target relation must be provided, though.
*
* Attribute numbers in expression Vars are adjusted according to attmap.
*
@@ -1668,7 +1670,9 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
* complain if that fails to happen).
*/
IndexStmt *
-generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
+generateClonedIndexStmt(RangeVar *heapRel,
+ Oid heapNamespace,
+ Relation source_idx,
const AttrMap *attmap,
Oid *constraintOid)
{
@@ -1746,14 +1750,6 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
index->if_not_exists = false;
index->reset_default_tblspc = false;
- /*
- * We don't try to preserve the name of the source index; instead, just
- * let DefineIndex() choose a reasonable name. (If we tried to preserve
- * the name, we'd get duplicate-relation-name failures unless the source
- * table was in a different schema.)
- */
- index->idxname = NULL;
-
/*
* If the index is marked PRIMARY or has an exclusion condition, it's
* certainly from a constraint; else, if it's not marked UNIQUE, it
@@ -1832,6 +1828,17 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
else
index->isconstraint = false;
+ /*
+ * Choose a name for the new index. Ideally we'd preserve the name of the
+ * source index, but that would lead to duplicate-relation-name failures
+ * if the new table is in the same schema. Instead use ChooseRelationName,
+ * which will append digits as needed to make a unique name.
+ */
+ index->idxname = ChooseRelationName(RelationGetRelationName(source_idx),
+ NULL, "",
+ heapNamespace,
+ index->isconstraint);
+
/* Get the index expressions, if any */
datum = SysCacheGetAttr(INDEXRELID, ht_idx,
Anum_pg_index_indexprs, &isnull);
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 9f2b58de797..1553a4a646c 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -37,6 +37,7 @@ extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation
extern List *expandTableLikeClause(RangeVar *heapRel,
TableLikeClause *table_like_clause);
extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel,
+ Oid heapNamespace,
Relation source_idx,
const struct AttrMap *attmap,
Oid *constraintOid);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 476266e3f4b..91ab5dcfe6e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -158,13 +158,14 @@ CREATE TABLE part_attmp (a int primary key) partition by range (a);
CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100);
ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index;
ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index;
+ERROR: relation "part_attmp1_pkey" does not exist
ALTER TABLE part_attmp RENAME TO part_at2tmp;
ALTER TABLE part_attmp1 RENAME TO part_at2tmp1;
SET ROLE regress_alter_table_user1;
ALTER INDEX part_attmp_index RENAME TO fail;
ERROR: must be owner of index part_attmp_index
ALTER INDEX part_attmp1_index RENAME TO fail;
-ERROR: must be owner of index part_attmp1_index
+ERROR: relation "part_attmp1_index" does not exist
ALTER TABLE part_at2tmp RENAME TO fail;
ERROR: must be owner of table part_at2tmp
ALTER TABLE part_at2tmp1 RENAME TO fail;
@@ -457,7 +458,7 @@ CREATE TABLE like_constraint_rename_cache
--------+---------+-----------+----------+---------
a | integer | | not null |
Indexes:
- "like_constraint_rename_cache_pkey" PRIMARY KEY, btree (a)
+ "constraint_rename_pkey_new_1" PRIMARY KEY, btree (a)
Check constraints:
"chk_a_new" CHECK (a > 0)
@@ -2182,8 +2183,8 @@ create index on at_partitioned (a);
b | text | | |
Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
Indexes:
- "at_part_1_a_idx" btree (a)
- "at_part_1_b_idx" btree (b)
+ "at_partitioned_a_idx_1" btree (a)
+ "at_partitioned_b_idx_1" btree (b)
\d at_part_2
Table "public.at_part_2"
@@ -2201,8 +2202,8 @@ alter table at_partitioned attach partition at_part_2 for values from (1000) to
a | integer | | |
Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
Indexes:
- "at_part_2_a_idx" btree (a)
- "at_part_2_b_idx" btree (b)
+ "at_partitioned_a_idx_2" btree (a)
+ "at_partitioned_b_idx_2" btree (b)
alter table at_partitioned alter column b type numeric using b::numeric;
\d at_part_1
@@ -2213,8 +2214,8 @@ alter table at_partitioned alter column b type numeric using b::numeric;
b | numeric | | |
Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
Indexes:
- "at_part_1_a_idx" btree (a)
- "at_part_1_b_idx" btree (b)
+ "at_partitioned_a_idx_1" btree (a)
+ "at_partitioned_b_idx_1" btree (b)
\d at_part_2
Table "public.at_part_2"
@@ -2224,8 +2225,8 @@ Indexes:
a | integer | | |
Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
Indexes:
- "at_part_2_a_idx" btree (a)
- "at_part_2_b_idx" btree (b)
+ "at_partitioned_a_idx_2" btree (a)
+ "at_partitioned_b_idx_2" btree (b)
drop table at_partitioned;
-- Alter column type when no table rewrite is required
@@ -2237,11 +2238,15 @@ comment on index at_partitioned_id_name_key is 'parent index';
create table at_partitioned_0 partition of at_partitioned
for values with (modulus 2, remainder 0);
comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
+ERROR: constraint "at_partitioned_0_id_name_key" for table "at_partitioned_0" does not exist
comment on index at_partitioned_0_id_name_key is 'child 0 index';
+ERROR: relation "at_partitioned_0_id_name_key" does not exist
create table at_partitioned_1 partition of at_partitioned
for values with (modulus 2, remainder 1);
comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
+ERROR: constraint "at_partitioned_1_id_name_key" for table "at_partitioned_1" does not exist
comment on index at_partitioned_1_id_name_key is 'child 1 index';
+ERROR: relation "at_partitioned_1_id_name_key" does not exist
insert into at_partitioned values(1, 'foo');
insert into at_partitioned values(3, 'bar');
create temp table old_oids as
@@ -2259,24 +2264,24 @@ select relname,
from pg_class c left join old_oids using (relname)
where relname like 'at_partitioned%'
order by relname;
- relname | orig_oid | storage | desc
-------------------------------+----------+---------+---------------
+ relname | orig_oid | storage | desc
+------------------------------+----------+---------+--------------
at_partitioned | t | none |
at_partitioned_0 | t | own |
- at_partitioned_0_id_name_key | t | own | child 0 index
at_partitioned_1 | t | own |
- at_partitioned_1_id_name_key | t | own | child 1 index
at_partitioned_id_name_key | t | none | parent index
+ at_partitioned_id_name_key_1 | t | own |
+ at_partitioned_id_name_key_2 | t | own |
(6 rows)
select conname, obj_description(oid, 'pg_constraint') as desc
from pg_constraint where conname like 'at_partitioned%'
order by conname;
- conname | desc
-------------------------------+--------------------
- at_partitioned_0_id_name_key | child 0 constraint
- at_partitioned_1_id_name_key | child 1 constraint
+ conname | desc
+------------------------------+-------------------
at_partitioned_id_name_key | parent constraint
+ at_partitioned_id_name_key_1 |
+ at_partitioned_id_name_key_2 |
(3 rows)
alter table at_partitioned alter column name type varchar(127);
@@ -2296,10 +2301,10 @@ select relname,
------------------------------+----------+---------+--------------
at_partitioned | t | none |
at_partitioned_0 | t | own |
- at_partitioned_0_id_name_key | f | own |
at_partitioned_1 | t | own |
- at_partitioned_1_id_name_key | f | own |
at_partitioned_id_name_key | f | none | parent index
+ at_partitioned_id_name_key_1 | f | own |
+ at_partitioned_id_name_key_2 | f | own |
(6 rows)
select conname, obj_description(oid, 'pg_constraint') as desc
@@ -2307,9 +2312,9 @@ select conname, obj_description(oid, 'pg_constraint') as desc
order by conname;
conname | desc
------------------------------+-------------------
- at_partitioned_0_id_name_key |
- at_partitioned_1_id_name_key |
at_partitioned_id_name_key | parent constraint
+ at_partitioned_id_name_key_1 |
+ at_partitioned_id_name_key_2 |
(3 rows)
-- Don't remove this DROP, it exposes bug #15672
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ad6aaab7385..4be20ddad7c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -606,22 +606,22 @@ SELECT conname, conrelid::regclass FROM pg_constraint
WHERE conname LIKE 'parted_uniq%' ORDER BY conname;
conname | conrelid
-------------------------+-------------------
- parted_uniq_tbl_1_i_key | parted_uniq_tbl_1
- parted_uniq_tbl_2_i_key | parted_uniq_tbl_2
parted_uniq_tbl_i_key | parted_uniq_tbl
+ parted_uniq_tbl_i_key_1 | parted_uniq_tbl_1
+ parted_uniq_tbl_i_key_2 | parted_uniq_tbl_2
(3 rows)
BEGIN;
INSERT INTO parted_uniq_tbl VALUES (1);
SAVEPOINT f;
INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation
-ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_i_key_1"
DETAIL: Key (i)=(1) already exists.
ROLLBACK TO f;
SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;
INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
COMMIT;
-ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_i_key_1"
DETAIL: Key (i)=(1) already exists.
DROP TABLE parted_uniq_tbl;
-- test naming a constraint in a partition when a conflict exists
@@ -1281,7 +1281,7 @@ Not-null constraints:
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
Indexes:
- "notnull_tbl4_lk2_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
+ "notnull_tbl4_pkey_1" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
Not-null constraints:
"notnull_tbl4_a_not_null" NOT NULL "a"
@@ -1291,7 +1291,7 @@ Not-null constraints:
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
Indexes:
- "notnull_tbl4_lk3_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
+ "notnull_tbl4_pkey_2" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
Not-null constraints:
"a_nn" NOT NULL "a"
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 76604705a93..c391b7a58e6 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1125,9 +1125,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_idx" btree (b) WHERE b = 1
- "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))
+ "part_column_drop_b_expr_1" btree ((b = 1))
+ "part_column_drop_b_pred_1" btree (b) WHERE b = 1
+ "part_column_drop_d_expr_1" btree ((d = 2))
+ "part_column_drop_d_pred_1" btree (d) WHERE 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 bf34289e984..afb26e12cc7 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -284,7 +284,7 @@ DROP TABLE test_like_6, test_like_6c;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
-ERROR: duplicate key value violates unique constraint "inhg_pkey"
+ERROR: duplicate key value violates unique constraint "inhx_pkey_1"
DETAIL: Key (xx)=(10) already exists.
DROP TABLE inhg;
/* Multiple primary keys creation should fail */
@@ -310,7 +310,7 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
x | text | | |
xx | text | | not null |
Indexes:
- "inhz_pkey" PRIMARY KEY, btree (xx)
+ "inhx_pkey_1" PRIMARY KEY, btree (xx)
Foreign-key constraints:
"inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
Referenced by:
@@ -419,7 +419,7 @@ NOTICE: merging column "a" with inherited definition
b | text | | | | extended | |
c | text | | | | external | | C
Indexes:
- "ctlt13_like_expr_idx" btree ((a || c))
+ "ctlt3_fnidx_1" btree ((a || c))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -443,9 +443,9 @@ CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "ctlt_all_pkey" PRIMARY KEY, btree (a)
- "ctlt_all_b_idx" btree (b)
- "ctlt_all_expr_idx" btree ((a || b))
+ "ctlt1_pkey_1" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_1" btree (b)
+ "ctlt1_fnidx_1" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -456,10 +456,10 @@ Not-null constraints:
"ctlt1_a_not_null" NOT NULL "a"
SELECT c.relname, objsubid, description FROM pg_description, pg_index i, pg_class c WHERE classoid =
'pg_class'::regclassAND objoid = i.indexrelid AND c.oid = i.indexrelid AND i.indrelid = 'ctlt_all'::regclass ORDER BY
c.relname,objsubid;
- relname | objsubid | description
-----------------+----------+-------------
- ctlt_all_b_idx | 0 | index b_key
- ctlt_all_pkey | 0 | index pkey
+ relname | objsubid | description
+---------------+----------+-------------
+ ctlt1_b_key_1 | 0 | index b_key
+ ctlt1_pkey_1 | 0 | index pkey
(2 rows)
SELECT s.stxname, objsubid, description FROM pg_description, pg_statistic_ext s WHERE classoid =
'pg_statistic_ext'::regclassAND objoid = s.oid AND s.stxrelid = 'ctlt_all'::regclass ORDER BY s.stxname, objsubid;
@@ -486,9 +486,9 @@ CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "pg_attrdef_pkey" PRIMARY KEY, btree (a)
- "pg_attrdef_b_idx" btree (b)
- "pg_attrdef_expr_idx" btree ((a || b))
+ "ctlt1_pkey_2" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_2" btree (b)
+ "ctlt1_fnidx_2" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -511,9 +511,9 @@ CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "ctlt1_pkey" PRIMARY KEY, btree (a)
- "ctlt1_b_idx" btree (b)
- "ctlt1_expr_idx" btree ((a || b))
+ "ctlt1_pkey_1" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_1" btree (b)
+ "ctlt1_fnidx_1" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 7b2198eac6f..c003e5f8288 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -642,13 +642,13 @@ REINDEX TABLE CONCURRENTLY concur_reindex_part;
-- Now add some indexes.
CREATE INDEX concur_reindex_partidx ON concur_reindex_part (id);
REINDEX INDEX concur_reindex_partidx;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX INDEX CONCURRENTLY concur_reindex_partidx;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX TABLE concur_reindex_part;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX TABLE CONCURRENTLY concur_reindex_part;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
DROP TABLE concur_reindex_part;
-- Clean up
DROP EVENT TRIGGER regress_reindex_start;
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index bcf1db11d73..f49997e44dc 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -37,16 +37,16 @@ select relname, relkind, relhassubclass, 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 | relhassubclass | inhparent
------------------+---------+----------------+----------------
- idxpart | p | t |
- idxpart1 | r | f |
- idxpart1_a_idx | i | f | idxpart_a_idx
- idxpart2 | p | t |
- idxpart21 | r | f |
- idxpart21_a_idx | i | f | idxpart2_a_idx
- idxpart2_a_idx | I | t | idxpart_a_idx
- idxpart_a_idx | I | t |
+ relname | relkind | relhassubclass | inhparent
+-------------------+---------+----------------+-----------------
+ idxpart | p | t |
+ idxpart1 | r | f |
+ idxpart2 | p | t |
+ idxpart21 | r | f |
+ idxpart_a_idx | I | t |
+ idxpart_a_idx_1 | i | f | idxpart_a_idx
+ idxpart_a_idx_2 | I | t | idxpart_a_idx
+ idxpart_a_idx_2_1 | i | f | idxpart_a_idx_2
(8 rows)
drop table idxpart;
@@ -99,28 +99,11 @@ alter table idxpart attach partition idxpart1 for values from (0) to (10);
c | text | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
\d+ idxpart1_a_idx
- Index "public.idxpart1_a_idx"
- Column | Type | Key? | Definition | Storage | Stats target
---------+---------+------+------------+---------+--------------
- a | integer | yes | a | plain |
-Partition of: idxparti
-No partition constraint
-btree, for table "public.idxpart1"
-
\d+ idxpart1_b_c_idx
- Index "public.idxpart1_b_c_idx"
- Column | Type | Key? | Definition | Storage | Stats target
---------+---------+------+------------+----------+--------------
- b | integer | yes | b | plain |
- c | text | yes | c | extended |
-Partition of: idxparti2
-No partition constraint
-btree, for table "public.idxpart1"
-
-- Forbid ALTER TABLE when attaching or detaching an index to a partition.
create index idxpart_c on only idxpart (c);
create index idxpart1_c on idxpart1 (c);
@@ -173,8 +156,7 @@ create table idxpart (a int) partition by range (a);
create index on idxpart (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
drop index idxpart1_a_idx; -- no way
-ERROR: cannot drop index idxpart1_a_idx because index idxpart_a_idx requires it
-HINT: You can drop index idxpart_a_idx instead.
+ERROR: index "idxpart1_a_idx" does not exist
drop index concurrently idxpart_a_idx; -- unsupported
ERROR: cannot drop partitioned index "idxpart_a_idx" concurrently
drop index idxpart_a_idx; -- both indexes go away
@@ -203,8 +185,7 @@ create index on idxpart_temp(a);
create temp table idxpart1_temp partition of idxpart_temp
for values from (0) to (10);
drop index idxpart1_temp_a_idx; -- error
-ERROR: cannot drop index idxpart1_temp_a_idx because index idxpart_temp_a_idx requires it
-HINT: You can drop index idxpart_temp_a_idx instead.
+ERROR: index "idxpart1_temp_a_idx" does not exist
-- non-concurrent drop is enforced here, so it is a valid case.
drop index concurrently idxpart_temp_a_idx;
select relname, relkind from pg_class
@@ -277,8 +258,8 @@ Indexes:
"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))
+ "idxpart_a_idx_1" btree (a)
drop table idxpart;
-- If CREATE INDEX ONLY, don't create indexes on partitions; and existing
@@ -302,7 +283,7 @@ create index on idxpart (a);
a | integer | | |
Partition of: idxpart FOR VALUES FROM (0) TO (100)
Indexes:
- "idxpart1_a_idx" btree (a)
+ "idxpart_a_idx_1" btree (a)
\d idxpart2
Partitioned table "public.idxpart2"
@@ -328,10 +309,10 @@ where indexrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
indexrelid | indrelid | inhparent
-----------------+-----------+---------------
- idxpart1_a_idx | idxpart1 | idxpart_a_idx
idxpart22_a_idx | idxpart22 |
idxpart2_a_idx | idxpart2 | idxpart_a_idx
idxpart_a_idx | idxpart |
+ idxpart_a_idx_1 | idxpart1 | idxpart_a_idx
(4 rows)
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
@@ -341,10 +322,10 @@ where indexrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
indexrelid | indrelid | inhparent
-----------------+-----------+----------------
- idxpart1_a_idx | idxpart1 | idxpart_a_idx
idxpart22_a_idx | idxpart22 | idxpart2_a_idx
idxpart2_a_idx | idxpart2 | idxpart_a_idx
idxpart_a_idx | idxpart |
+ idxpart_a_idx_1 | idxpart1 | idxpart_a_idx
(4 rows)
-- attaching idxpart22 is not enough to set idxpart22_a_idx valid ...
@@ -391,21 +372,21 @@ create table idxpart1 (like idxpart including indexes);
c | text | | |
d | boolean | | |
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
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 |
- idxpart1_b_c_idx | i |
- idxparti | I |
- idxparti2 | I |
+ relname | relkind | inhparent
+-------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxparti | I |
+ idxparti2 | I |
+ idxparti2_1 | i |
+ idxparti_1 | i |
(6 rows)
alter table idxpart attach partition idxpart1 for values from (0) to (10);
@@ -419,21 +400,21 @@ alter table idxpart attach partition idxpart1 for values from (0) to (10);
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
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
- idxparti | I |
- idxparti2 | I |
+ relname | relkind | inhparent
+-------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxparti | I |
+ idxparti2 | I |
+ idxparti2_1 | i | idxparti2
+ idxparti_1 | i | idxparti
(6 rows)
-- While here, also check matching when creating an index after the fact.
@@ -448,9 +429,9 @@ create index on idxpart1 ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
@@ -460,11 +441,11 @@ select relname, relkind, inhparent::regclass
-------------------+---------+-----------
idxpart | p |
idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
idxpart1_expr_idx | i |
idxparti | I |
idxparti2 | I |
+ idxparti2_1 | i | idxparti2
+ idxparti_1 | i | idxparti
(7 rows)
create index idxparti3 on idxpart ((a+b)) where d = true;
@@ -478,9 +459,9 @@ create index idxparti3 on idxpart ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
@@ -490,12 +471,12 @@ select relname, relkind, inhparent::regclass
-------------------+---------+-----------
idxpart | p |
idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
idxpart1_expr_idx | i | idxparti3
idxparti | I |
idxparti2 | I |
+ idxparti2_1 | i | idxparti2
idxparti3 | I |
+ idxparti_1 | i | idxparti
(8 rows)
drop table idxpart;
@@ -551,16 +532,16 @@ alter table idxpart attach partition idxpart1 for values from (0000) to (1000);
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
- idxpart_a_idx | I
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(8 rows)
-- a) after detaching partitions, the indexes can be dropped independently
@@ -569,16 +550,20 @@ alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart1_a_idx;
drop index idxpart2_a_idx;
+ERROR: index "idxpart2_a_idx" does not exist
drop index idxpart3_a_idx;
+ERROR: index "idxpart3_a_idx" does not exist
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart2 | r
- idxpart3 | r
- idxpart_a_idx | I
-(5 rows)
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
+(7 rows)
drop table idxpart, idxpart1, idxpart2, idxpart3;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
@@ -596,16 +581,16 @@ alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
-- b) after detaching, dropping the index on parent does not remove the others
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
- idxpart_a_idx | I
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(8 rows)
alter table idxpart detach partition idxpart1;
@@ -613,15 +598,15 @@ alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart_a_idx;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(7 rows)
drop table idxpart, idxpart1, idxpart2, idxpart3;
@@ -643,7 +628,7 @@ alter table idxpart detach partition idxpart2;
b | integer | | |
c | integer | | |
Indexes:
- "idxpart2_c_idx" btree (c)
+ "idxpart_c_idx_2" btree (c)
alter table idxpart2 drop column c;
\d idxpart2
@@ -667,11 +652,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_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((a + b)))
+ idxpart_expr_idx_1 | idxpart_expr_idx | CREATE INDEX idxpart_expr_idx_1 ON public.idxpart2 USING btree (((a + b)))
+ idxpart_expr_idx_2 | idxpart_expr_idx | CREATE INDEX idxpart_expr_idx_2 ON public.idxpart3 USING btree (((a + b)))
(3 rows)
drop table idxpart;
@@ -693,13 +678,13 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
child | parent | childdef
-----------------+---------------+--------------------------------------------------------------------------------
- idxpart1_a_idx | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a COLLATE "C")
idxpart2_a_idx | | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a COLLATE "POSIX")
idxpart2_a_idx1 | | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a)
idxpart2_a_idx2 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx2 ON public.idxpart2 USING btree (a COLLATE "C")
- idxpart3_a_idx | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a COLLATE "C")
- idxpart4_a_idx | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a COLLATE "C")
idxpart_a_idx | | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a COLLATE "C")
+ idxpart_a_idx_1 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a COLLATE "C")
+ idxpart_a_idx_2 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_2 ON public.idxpart3 USING btree (a COLLATE "C")
+ idxpart_a_idx_3 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_3 ON public.idxpart4 USING btree (a COLLATE "C")
(7 rows)
drop table idxpart;
@@ -720,12 +705,12 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
child | parent | childdef
-----------------+---------------+------------------------------------------------------------------------------------
- idxpart1_a_idx | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a text_pattern_ops)
idxpart2_a_idx | | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
- idxpart2_a_idx1 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a text_pattern_ops)
- idxpart3_a_idx | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a text_pattern_ops)
- idxpart4_a_idx | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a text_pattern_ops)
idxpart_a_idx | | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a text_pattern_ops)
+ idxpart_a_idx_1 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a text_pattern_ops)
+ idxpart_a_idx_2 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_2 ON public.idxpart2 USING btree (a text_pattern_ops)
+ idxpart_a_idx_3 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_3 ON public.idxpart3 USING btree (a text_pattern_ops)
+ idxpart_a_idx_4 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_4 ON public.idxpart4 USING btree (a text_pattern_ops)
(6 rows)
drop index idxpart_a_idx;
@@ -792,12 +777,12 @@ select c.relname, pg_get_indexdef(indexrelid)
order by indexrelid::regclass::text collate "C";
relname | pg_get_indexdef
------------------+---------------------------------------------------------------------
- idxpart1_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a)
- idxpart1_c_b_idx | CREATE INDEX idxpart1_c_b_idx ON public.idxpart1 USING btree (c, b)
idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
idxpart2_c_b_idx | CREATE INDEX idxpart2_c_b_idx ON public.idxpart2 USING btree (c, b)
idxparti | CREATE INDEX idxparti ON ONLY public.idxpart USING btree (a)
idxparti2 | CREATE INDEX idxparti2 ON ONLY public.idxpart USING btree (c, b)
+ idxparti2_1 | CREATE INDEX idxparti2_1 ON public.idxpart1 USING btree (c, b)
+ idxparti_1 | CREATE INDEX idxparti_1 ON public.idxpart1 USING btree (a)
(6 rows)
drop table idxpart;
@@ -817,14 +802,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
+--------------------+------------------------------------------------------------------------------
+ idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USING btree (abs(b))
+ idxpart_abs_idx | CREATE INDEX idxpart_abs_idx ON ONLY public.idxpart USING btree (abs(b))
+ idxpart_abs_idx_1 | CREATE INDEX idxpart_abs_idx_1 ON public.idxpart1 USING btree (abs(b))
+ idxpart_expr_idx | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpart USING btree (((b + 1)))
+ idxpart_expr_idx_1 | CREATE INDEX idxpart_expr_idx_1 ON public.idxpart2 USING btree (((b + 1)))
+ idxpart_expr_idx_2 | CREATE INDEX idxpart_expr_idx_2 ON public.idxpart1 USING btree (((b + 1)))
(6 rows)
drop table idxpart;
@@ -843,11 +828,11 @@ 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_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a) WHERE (b > 1000)
- idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a) WHERE (b > 1000)
- idxpart_a_idx | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a) WHERE (b > 1000)
+ relname | pg_get_indexdef
+-----------------+------------------------------------------------------------------------------------
+ idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a) WHERE (b > 1000)
+ idxpart_a_idx | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a) WHERE (b > 1000)
+ idxpart_a_idx_1 | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a) WHERE (b > 1000)
(3 rows)
drop table idxpart;
@@ -1047,7 +1032,7 @@ create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000
c | text | | |
Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)
Indexes:
- "idxpart1_pkey" PRIMARY KEY, btree (a, b)
+ "idxpart_pkey_1" PRIMARY KEY, btree (a, b)
drop table idxpart;
-- use ALTER TABLE to add a unique constraint
@@ -1117,26 +1102,26 @@ alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 3
select conname, contype, conrelid::regclass, conindid::regclass, conkey
from pg_constraint where conrelid::regclass::text like 'idxpart%'
order by conrelid::regclass::text, conname;
- conname | contype | conrelid | conindid | conkey
----------------------+---------+-----------+----------------+--------
- idxpart_a_not_null | n | idxpart | - | {1}
- idxpart_b_not_null | n | idxpart | - | {2}
- idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
- idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}
- idxpart_a_not_null | n | idxpart1 | - | {1}
- idxpart_b_not_null | n | idxpart1 | - | {2}
- idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
- idxpart_a_not_null | n | idxpart2 | - | {1}
- idxpart_b_not_null | n | idxpart2 | - | {2}
- idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
- idxpart_a_not_null | n | idxpart21 | - | {1}
- idxpart_b_not_null | n | idxpart21 | - | {2}
- idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}
- idxpart_a_not_null | n | idxpart22 | - | {1}
- idxpart_b_not_null | n | idxpart22 | - | {2}
- idxpart3_a_not_null | n | idxpart3 | - | {2}
- idxpart3_b_not_null | n | idxpart3 | - | {1}
- idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}
+ conname | contype | conrelid | conindid | conkey
+---------------------+---------+-----------+------------------+--------
+ idxpart_a_not_null | n | idxpart | - | {1}
+ idxpart_b_not_null | n | idxpart | - | {2}
+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
+ idxpart_a_not_null | n | idxpart1 | - | {1}
+ idxpart_b_not_null | n | idxpart1 | - | {2}
+ idxpart_pkey_1 | p | idxpart1 | idxpart_pkey_1 | {1,2}
+ idxpart_a_not_null | n | idxpart2 | - | {1}
+ idxpart_b_not_null | n | idxpart2 | - | {2}
+ idxpart_pkey_2 | p | idxpart2 | idxpart_pkey_2 | {1,2}
+ idxpart_a_not_null | n | idxpart21 | - | {1}
+ idxpart_b_not_null | n | idxpart21 | - | {2}
+ idxpart_pkey_2_1 | p | idxpart21 | idxpart_pkey_2_1 | {1,2}
+ idxpart_a_not_null | n | idxpart22 | - | {1}
+ idxpart_b_not_null | n | idxpart22 | - | {2}
+ idxpart_pkey_2_2 | p | idxpart22 | idxpart_pkey_2_2 | {1,2}
+ idxpart3_a_not_null | n | idxpart3 | - | {2}
+ idxpart3_b_not_null | n | idxpart3 | - | {1}
+ idxpart_pkey_3 | p | idxpart3 | idxpart_pkey_3 | {2,1}
(18 rows)
drop table idxpart;
@@ -1163,17 +1148,17 @@ create table idxpart21 partition of idxpart2 for values from (0) to (1000);
select conname, contype, conrelid::regclass, conindid::regclass, conkey
from pg_constraint where conrelid::regclass::text like 'idxpart%'
order by conrelid::regclass::text, conname;
- conname | contype | conrelid | conindid | conkey
---------------------+---------+-----------+----------------+--------
- idxpart_a_not_null | n | idxpart | - | {1}
- idxpart_b_not_null | n | idxpart | - | {2}
- idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
- idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
- idxpart_a_not_null | n | idxpart2 | - | {1}
- idxpart_b_not_null | n | idxpart2 | - | {2}
- idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
- idxpart_a_not_null | n | idxpart21 | - | {1}
- idxpart_b_not_null | n | idxpart21 | - | {2}
+ conname | contype | conrelid | conindid | conkey
+--------------------+---------+-----------+------------------+--------
+ idxpart_a_not_null | n | idxpart | - | {1}
+ idxpart_b_not_null | n | idxpart | - | {2}
+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
+ idxpart_a_not_null | n | idxpart2 | - | {1}
+ idxpart_b_not_null | n | idxpart2 | - | {2}
+ idxpart_pkey_1 | p | idxpart2 | idxpart_pkey_1 | {1,2}
+ idxpart_a_not_null | n | idxpart21 | - | {1}
+ idxpart_b_not_null | n | idxpart21 | - | {2}
+ idxpart_pkey_1_1 | p | idxpart21 | idxpart_pkey_1_1 | {1,2}
(9 rows)
drop table idxpart;
@@ -1192,23 +1177,22 @@ select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid
left join pg_constraint con on (idx.indexrelid = con.conindid)
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
-----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------
- idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t
- idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | f | t
- idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
+----------+----------------+--------------+------------+----------------+------------+-------------+--------------+--------------
+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t
+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ idxpart1 | idxpart_pkey_1 | idxpart_pkey | t | idxpart_pkey_1 | f | 1 | f | t
(3 rows)
drop index idxpart0_pkey; -- fail
ERROR: cannot drop index idxpart0_pkey because index idxpart_pkey requires it
HINT: You can drop index idxpart_pkey instead.
drop index idxpart1_pkey; -- fail
-ERROR: cannot drop index idxpart1_pkey because index idxpart_pkey requires it
-HINT: You can drop index idxpart_pkey instead.
+ERROR: index "idxpart1_pkey" does not exist
alter table idxpart0 drop constraint idxpart0_pkey; -- fail
ERROR: cannot drop inherited constraint "idxpart0_pkey" of relation "idxpart0"
alter table idxpart1 drop constraint idxpart1_pkey; -- fail
-ERROR: cannot drop inherited constraint "idxpart1_pkey" of relation "idxpart1"
+ERROR: constraint "idxpart1_pkey" of relation "idxpart1" does not exist
alter table idxpart drop constraint idxpart_pkey; -- ok
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
conname, conislocal, coninhcount, connoinherit, convalidated
@@ -1295,11 +1279,11 @@ select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid
left join pg_constraint con on (idx.indexrelid = con.conindid)
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
-----------+----------------+--------------+------------+---------------+------------+-------------+--------------+--------------
- idxpart1 | idxpart1_a_idx | | t | | | | |
- idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | f | t
- idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
+----------+----------------+--------------+------------+----------------+------------+-------------+--------------+--------------
+ idxpart1 | idxpart1_a_idx | | t | | | | |
+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ idxpart1 | idxpart_pkey_1 | idxpart_pkey | t | idxpart_pkey_1 | f | 1 | f | t
(3 rows)
drop table idxpart;
@@ -1323,15 +1307,15 @@ create unique index on idxpart (a);
alter table idxpart attach partition idxpart2 for values from (100000) to (1000000);
insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen');
insert into idxpart select 2^g, format('two to power of %s', g) from generate_series(15, 17) g;
-ERROR: duplicate key value violates unique constraint "idxpart1_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_1"
DETAIL: Key (a)=(65536) already exists.
insert into idxpart values (16, 'sixteen');
insert into idxpart (b, a) values ('one', 142857), ('two', 285714);
insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and 2^19;
-ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_2"
DETAIL: Key (a)=(285714) already exists.
insert into idxpart values (572814, 'five');
-ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_2"
DETAIL: Key (a)=(572814) already exists.
insert into idxpart values (857142, 'six');
select tableoid::regclass, * from idxpart order by a;
@@ -1358,18 +1342,18 @@ explain (costs off) select * from idxpart where b = 'abcd';
-------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (b = 'abcd'::text)
- -> Bitmap Index Scan on idxpart1_b_idx
+ -> Bitmap Index Scan on idxpart_brin_1
Index Cond: (b = 'abcd'::text)
(4 rows)
drop index idxpart_brin;
create index idxpart_spgist on idxpart using spgist(b);
explain (costs off) select * from idxpart where b = 'abcd';
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (b = 'abcd'::text)
- -> Bitmap Index Scan on idxpart1_b_idx
+ -> Bitmap Index Scan on idxpart_spgist_1
Index Cond: (b = 'abcd'::text)
(4 rows)
@@ -1380,7 +1364,7 @@ explain (costs off) select * from idxpart where c @> array[42];
----------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (c @> '{42}'::integer[])
- -> Bitmap Index Scan on idxpart1_c_idx
+ -> Bitmap Index Scan on idxpart_gin_1
Index Cond: (c @> '{42}'::integer[])
(4 rows)
@@ -1430,14 +1414,14 @@ create table covidxpart1 partition of covidxpart for values in (1);
create table covidxpart2 partition of covidxpart for values in (2);
insert into covidxpart values (1, 1);
insert into covidxpart values (1, 1);
-ERROR: duplicate key value violates unique constraint "covidxpart1_a_b_idx"
+ERROR: duplicate key value violates unique constraint "covidxpart_a_b_idx_1"
DETAIL: Key (a)=(1) already exists.
create table covidxpart3 (b int, c int, a int);
alter table covidxpart3 drop c;
alter table covidxpart attach partition covidxpart3 for values in (3);
insert into covidxpart values (3, 1);
insert into covidxpart values (3, 1);
-ERROR: duplicate key value violates unique constraint "covidxpart3_a_b_idx"
+ERROR: duplicate key value violates unique constraint "covidxpart_a_b_idx_3"
DETAIL: Key (a)=(3) already exists.
create table covidxpart4 (b int, a int);
create unique index on covidxpart4 (a) include (b);
@@ -1454,16 +1438,18 @@ DETAIL: UNIQUE constraint on table "covidxpart" lacks column "a" which is part
create table parted_pk_detach_test (a int primary key) partition by list (a);
create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1);
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey; -- should fail
-ERROR: cannot drop inherited constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1"
+ERROR: constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1" does not exist
alter table parted_pk_detach_test detach partition parted_pk_detach_test1;
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;
+ERROR: constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1" does not exist
drop table parted_pk_detach_test, parted_pk_detach_test1;
create table parted_uniq_detach_test (a int unique) partition by list (a);
create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1);
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key; -- should fail
-ERROR: cannot drop inherited constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1"
+ERROR: constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1" does not exist
alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
+ERROR: constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1" does not exist
drop table parted_uniq_detach_test, parted_uniq_detach_test1;
-- check that dropping a column takes with it any partitioned indexes
-- depending on it.
@@ -1500,7 +1486,7 @@ Number of partitions: 2 (Use \d+ to list them.)
Partition of: parted_index_col_drop FOR VALUES IN (1)
Partition key: LIST (a)
Indexes:
- "parted_index_col_drop1_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_1" btree (b)
Number of partitions: 1 (Use \d+ to list them.)
\d parted_index_col_drop2
@@ -1512,7 +1498,7 @@ Number of partitions: 1 (Use \d+ to list them.)
Partition of: parted_index_col_drop FOR VALUES IN (2)
Partition key: LIST (a)
Indexes:
- "parted_index_col_drop2_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_2" btree (b)
Number of partitions: 0
\d parted_index_col_drop11
@@ -1523,7 +1509,7 @@ Number of partitions: 0
b | integer | | |
Partition of: parted_index_col_drop1 FOR VALUES IN (1)
Indexes:
- "parted_index_col_drop11_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_1_1" btree (b)
drop table parted_index_col_drop;
-- Check that invalid indexes are not selected when attaching a partition.
@@ -1546,13 +1532,13 @@ select indexrelid::regclass, indisvalid,
pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'parted_inval%'
order by indexrelid::regclass::text collate "C";
- indexrelid | indisvalid | indrelid | inhparent
-----------------------------+------------+----------------------+--------------------------
- parted_inval_idx | t | parted_inval_tab |
- parted_inval_ixd_1 | f | parted_inval_tab_1 |
- parted_inval_tab_1_1_a_idx | t | parted_inval_tab_1_1 | parted_inval_tab_1_a_idx
- parted_inval_tab_1_2_a_idx | t | parted_inval_tab_1_2 | parted_inval_tab_1_a_idx
- parted_inval_tab_1_a_idx | t | parted_inval_tab_1 | parted_inval_idx
+ indexrelid | indisvalid | indrelid | inhparent
+----------------------+------------+----------------------+--------------------
+ parted_inval_idx | t | parted_inval_tab |
+ parted_inval_idx_1 | t | parted_inval_tab_1 | parted_inval_idx
+ parted_inval_idx_1_1 | t | parted_inval_tab_1_1 | parted_inval_idx_1
+ parted_inval_idx_1_2 | t | parted_inval_tab_1_2 | parted_inval_idx_1
+ parted_inval_ixd_1 | f | parted_inval_tab_1 |
(5 rows)
drop table parted_inval_tab;
@@ -1581,13 +1567,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_1 | f | parted_isvalid_tab_1 | parted_isvalid_idx
+ parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_idx_1
+ parted_isvalid_idx_1_1 | t | parted_isvalid_tab_12 | parted_isvalid_idx_1
+ parted_isvalid_idx_2 | 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 f9b0c415cfd..b6484683837 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -3272,45 +3272,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 mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6 on mcrparted5 mcrparted_6
+ -> Index Scan using mcrparted_a_abs_c_idx_7 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 mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6 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 mcrparted_a_abs_c_idx_6 on mcrparted5 mcrparted_6
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
(7 rows)
-- check that Append plan is used containing a MergeAppend for sub-partitions
@@ -3320,18 +3320,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 mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 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 mcrparted_a_abs_c_idx_6_1 on mcrparted5a mcrparted_7
+ -> Index Scan using mcrparted_a_abs_c_idx_6_2 on mcrparted5_def mcrparted_8
(10 rows)
drop table mcrparted5_def;
@@ -3339,30 +3339,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 mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6_1 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 mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
Index Cond: (a < 20)
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
Index Cond: (a < 20)
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
Index Cond: (a < 20)
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
Index Cond: (a < 20)
(9 rows)
@@ -3374,11 +3374,11 @@ create table mclparted2 partition of mclparted for values in(2);
create index on mclparted (a);
-- Ensure an Append is used for a list partition with an order by.
explain (costs off) select * from mclparted order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
(3 rows)
-- Ensure a MergeAppend is used when a partition exists with interleaved
@@ -3386,24 +3386,24 @@ explain (costs off) select * from mclparted order by a;
create table mclparted3_5 partition of mclparted for values in(3,5);
create table mclparted4 partition of mclparted for values in(4);
explain (costs off) select * from mclparted order by a;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
- -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_3
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_3 on mclparted3_5 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
(6 rows)
explain (costs off) select * from mclparted where a in(3,4,5) order by a;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_3 on mclparted3_5 mclparted_1
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_2
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
(6 rows)
@@ -3412,28 +3412,28 @@ create table mclparted_null partition of mclparted for values in(null);
create table mclparted_def partition of mclparted default;
-- Append can be used providing we don't scan the interleaved partition
explain (costs off) select * from mclparted where a in(1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
(7 rows)
explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclparted_4
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_null mclparted_4
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
(9 rows)
@@ -3442,61 +3442,61 @@ drop table mclparted_null;
create table mclparted_0_null partition of mclparted for values in(0,null);
-- Ensure MergeAppend is used since 0 and NULLs are in the same partition.
explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclparted_1
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_2
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_3
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
(10 rows)
explain (costs off) select * from mclparted where a in(0,1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclparted_1
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_2
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_3
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
(10 rows)
-- Ensure Append is used when the null partition is pruned
explain (costs off) select * from mclparted where a in(1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
(7 rows)
-- Ensure MergeAppend is used when the default partition is not pruned
explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4
+ -> Index Only Scan using mclparted_a_idx_6 on mclparted_def mclparted_4
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
(10 rows)
@@ -3548,11 +3548,11 @@ create table bool_lp_true partition of bool_lp for values in(true);
create table bool_lp_false partition of bool_lp for values in(false);
create index on bool_lp (b);
explain (costs off) select * from bool_lp order by b;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_lp_false_b_idx on bool_lp_false bool_lp_1
- -> Index Only Scan using bool_lp_true_b_idx on bool_lp_true bool_lp_2
+ -> Index Only Scan using bool_lp_b_idx_1 on bool_lp_false bool_lp_1
+ -> Index Only Scan using bool_lp_b_idx_2 on bool_lp_true bool_lp_2
(3 rows)
drop table bool_lp;
@@ -3564,42 +3564,42 @@ create table bool_rp_false_2k partition of bool_rp for values from (false,1000)
create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000);
create index on bool_rp (b,a);
explain (costs off) select * from bool_rp where b = true order by b,a;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
- -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
(5 rows)
explain (costs off) select * from bool_rp where b = false order by b,a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_1 on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
- -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_2 on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
(5 rows)
explain (costs off) select * from bool_rp where b = true order by a;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
- -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
(5 rows)
explain (costs off) select * from bool_rp where b = false order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_1 on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
- -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_2 on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
(5 rows)
@@ -3611,19 +3611,19 @@ create table range_parted1 partition of range_parted for values from (0,0) to (1
create table range_parted2 partition of range_parted for values from (10,10) to (20,20);
create index on range_parted (a,b,c);
explain (costs off) select * from range_parted order by a,b,c;
- QUERY PLAN
--------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Append
- -> Index Only Scan using range_parted1_a_b_c_idx on range_parted1 range_parted_1
- -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 range_parted_2
+ -> Index Only Scan using range_parted_a_b_c_idx_1 on range_parted1 range_parted_1
+ -> Index Only Scan using range_parted_a_b_c_idx_2 on range_parted2 range_parted_2
(3 rows)
explain (costs off) select * from range_parted order by a desc,b desc,c desc;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Append
- -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_parted2 range_parted_2
- -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1 range_parted_1
+ -> Index Only Scan Backward using range_parted_a_b_c_idx_2 on range_parted2 range_parted_2
+ -> Index Only Scan Backward using range_parted_a_b_c_idx_1 on range_parted1 range_parted_1
(3 rows)
drop table range_parted;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index d5368186caa..9fd5285b628 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2530,9 +2530,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
Filter: (a = prtx1_1.a)
-> BitmapAnd
- -> Bitmap Index Scan on prtx2_1_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_1
Index Cond: (b = prtx1_1.b)
- -> Bitmap Index Scan on prtx2_1_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_1
Index Cond: (c = 123)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
@@ -2541,9 +2541,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
Filter: (a = prtx1_2.a)
-> BitmapAnd
- -> Bitmap Index Scan on prtx2_2_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_2
Index Cond: (b = prtx1_2.b)
- -> Bitmap Index Scan on prtx2_2_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_2
Index Cond: (c = 123)
(23 rows)
@@ -2571,9 +2571,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
Filter: (a = prtx1_1.a)
-> BitmapOr
- -> Bitmap Index Scan on prtx2_1_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_1
Index Cond: (b = (prtx1_1.b + 1))
- -> Bitmap Index Scan on prtx2_1_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_1
Index Cond: (c = 99)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
@@ -2582,9 +2582,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
Filter: (a = prtx1_2.a)
-> BitmapOr
- -> Bitmap Index Scan on prtx2_2_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_2
Index Cond: (b = (prtx1_2.b + 1))
- -> Bitmap Index Scan on prtx2_2_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_2
Index Cond: (c = 99)
(23 rows)
@@ -3075,8 +3075,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_a
-- 3-way join where not every pair of relations can do partitioned join
EXPLAIN (COSTS OFF)
SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) INNER JOIN prt1_adv
t3ON (t1.b = t3.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a, t3.a;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Sort
Sort Key: t1.b, t2.a
-> Append
@@ -3084,9 +3084,9 @@ SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2
-> Nested Loop
-> Seq Scan on prt2_adv_p1 t1_1
Filter: (a = 0)
- -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t3_1
+ -> Index Scan using prt1_adv_a_idx_1 on prt1_adv_p1 t3_1
Index Cond: (a = t1_1.b)
- -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t2_1
+ -> Index Scan using prt1_adv_a_idx_1 on prt1_adv_p1 t2_1
Index Cond: (a = t1_1.b)
-> Hash Right Join
Hash Cond: (t2_2.a = t1_2.b)
@@ -5228,53 +5228,53 @@ SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = on;
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id
-> Merge Left Join
Merge Cond: (x_1.id = y_1.id)
- -> Index Only Scan using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
-> Merge Left Join
Merge Cond: (x_2.id = y_2.id)
- -> Index Only Scan using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
(11 rows)
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id DESC
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan Backward using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
Index Cond: (id = x_1.id)
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan Backward using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
Index Cond: (id = x_2.id)
(11 rows)
EXPLAIN (COSTS OFF) -- Should use NestLoop with parameterised inner scan
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id)
ORDER BY x.id DESC LIMIT 2;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id DESC
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan Backward using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
Index Cond: (id = x_1.id)
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan Backward using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
Index Cond: (id = x_2.id)
(11 rows)
@@ -5307,8 +5307,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
-- Increase number of tuples requested and an IndexScan will be chosen
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Limit
-> Append
-> Nested Loop
@@ -5316,21 +5316,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
- -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ -> Index Scan using pht1_c_idx_1 on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
-> Seq Scan on pht1_p2 p1_2
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
- -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ -> Index Scan using pht1_c_idx_2 on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
-> Seq Scan on pht1_p3 p1_3
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
- -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ -> Index Scan using pht1_c_idx_3 on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
(23 rows)
@@ -5361,8 +5361,8 @@ SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Gather
Workers Planned: 1
Single Copy: true
@@ -5373,21 +5373,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
- -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ -> Index Scan using pht1_c_idx_1 on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
-> Seq Scan on pht1_p2 p1_2
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
- -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ -> Index Scan using pht1_c_idx_2 on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
-> Seq Scan on pht1_p3 p1_3
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
- -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ -> Index Scan using pht1_c_idx_3 on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
(26 rows)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index d1966cd7d82..9e885c3375b 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1903,16 +1903,16 @@ select * from
from int4_tbl touter) ss,
asptab
where asptab.id > ss.b::int;
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Nested Loop
-> Seq Scan on int4_tbl touter
-> Append
- -> Index Only Scan using asptab0_pkey on asptab0 asptab_1
+ -> Index Only Scan using asptab_pkey_1 on asptab0 asptab_1
Index Cond: (id > (EXISTS(SubPlan 3))::integer)
SubPlan 4
-> Seq Scan on int4_tbl tinner_2
- -> Index Only Scan using asptab1_pkey on asptab1 asptab_2
+ -> Index Only Scan using asptab_pkey_2 on asptab1 asptab_2
Index Cond: (id > (EXISTS(SubPlan 3))::integer)
SubPlan 3
-> Seq Scan on int4_tbl tinner_1
@@ -3566,16 +3566,16 @@ create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Merge Append (actual rows=2.00 loops=1)
Sort Key: ma_test.b
Subplans Removed: 1
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_1 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
@@ -3589,12 +3589,12 @@ execute mt_q1(15);
(2 rows)
explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Merge Append (actual rows=1.00 loops=1)
Sort Key: ma_test.b
Subplans Removed: 2
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_1 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
@@ -3636,24 +3636,24 @@ explain (analyze, verbose, costs off, summary off, timing off, buffers off) exec
deallocate mt_q2;
-- ensure initplan params properly prune partitions
explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b)
fromma_test_p2) order by b;
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
Merge Append (actual rows=20.00 loops=1)
Sort Key: ma_test.b
InitPlan 2
-> Result (actual rows=1.00 loops=1)
InitPlan 1
-> Limit (actual rows=1.00 loops=1)
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 (actual rows=1.00 loops=1)
Index Cond: (b IS NOT NULL)
Index Searches: 1
- -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
+ -> Index Scan using ma_test_b_idx_1 on ma_test_p1 ma_test_1 (never executed)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 0
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 1
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 1
(18 rows)
@@ -4216,8 +4216,8 @@ create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
explain (analyze on, costs off, timing off, summary off, buffers off)
select * from rangep where b IN((select 1),(select 2)) order by a;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Append (actual rows=0.00 loops=1)
InitPlan 1
-> Result (actual rows=1.00 loops=1)
@@ -4225,16 +4225,16 @@ select * from rangep where b IN((select 1),(select 2)) order by a;
-> Result (actual rows=1.00 loops=1)
-> Merge Append (actual rows=0.00 loops=1)
Sort Key: rangep_2.a
- -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_1_1 on rangep_0_to_100_1 rangep_2 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
- -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_1_2 on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
- -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed)
+ -> Index Scan using rangep_a_idx_1_3 on rangep_0_to_100_3 rangep_4 (never executed)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 0
- -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_2 on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
(19 rows)
@@ -4768,16 +4768,16 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1
+ -> Index Scan using part_abc_a_idx_2 on part_abc_2 part_abc_1
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
-> Merge Append
Sort Key: part_abc_3.a
Subplans Removed: 1
- -> Index Scan using part_abc_3_1_a_idx on part_abc_3_1 part_abc_3
+ -> Index Scan using part_abc_a_idx_3_1 on part_abc_3_1 part_abc_3
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
- -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_4
+ -> Index Scan using part_abc_a_idx_3_2 on part_abc_3_2 part_abc_4
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
-> Subquery Scan on "*SELECT* 2"
@@ -4785,16 +4785,16 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6
+ -> Index Scan using part_abc_a_idx_2 on part_abc_2 part_abc_6
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
-> Merge Append
Sort Key: a
Subplans Removed: 1
- -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_8
+ -> Index Scan using part_abc_a_idx_3_2 on part_abc_3_2 part_abc_8
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
- -> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9
+ -> Index Scan using part_abc_a_idx_3_3 on part_abc_3_3 part_abc_9
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
(35 rows)
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..961e4bb09c4 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5054,13 +5054,13 @@ insert into parent_tab values (generate_series(30,39));
(1 row)
\dP testpart.*
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
\dP
@@ -5080,31 +5080,31 @@ insert into parent_tab values (generate_series(30,39));
(2 rows)
\dPin
- List of partitioned indexes
- Schema | Name | Owner | Parent name | Table
-----------+--------------------+---------------------------+--------------+-------------
- testpart | parent_index | regress_partitioning_role | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | parent_index | child_30_40
+ List of partitioned indexes
+ Schema | Name | Owner | Parent name | Table
+----------+----------------+---------------------------+--------------+-------------
+ testpart | parent_index | regress_partitioning_role | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | parent_index | child_30_40
(2 rows)
\dPn
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
\dPn testpart.*
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
drop table parent_tab cascade;
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..6cb35ab97f5 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -330,12 +330,10 @@ CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace;
CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx' ORDER BY relname;
- relname | spcname
--------------+------------------
- part1_a_idx | regress_tblspace
- part2_a_idx | regress_tblspace
- part_a_idx | regress_tblspace
-(3 rows)
+ relname | spcname
+------------+------------------
+ part_a_idx | regress_tblspace
+(1 row)
\d testschema.part
Partitioned table "testschema.part"
@@ -365,7 +363,7 @@ Partitions: testschema.part1 FOR VALUES IN (1),
a | integer | | |
Partition of: testschema.part FOR VALUES IN (1)
Indexes:
- "part1_a_idx" btree (a), tablespace "regress_tblspace"
+ "part_a_idx_1" btree (a), tablespace "regress_tblspace"
\d+ testschema.part1
Table "testschema.part1"
@@ -375,7 +373,7 @@ Indexes:
Partition of: testschema.part FOR VALUES IN (1)
Partition constraint: ((a IS NOT NULL) AND (a = 1))
Indexes:
- "part1_a_idx" btree (a), tablespace "regress_tblspace"
+ "part_a_idx_1" btree (a), tablespace "regress_tblspace"
\d testschema.part_a_idx
Partitioned index "testschema.part_a_idx"
@@ -392,8 +390,8 @@ Tablespace: "regress_tblspace"
--------+---------+------+------------+---------+--------------
a | integer | yes | a | plain |
btree, for table "testschema.part"
-Partitions: testschema.part1_a_idx,
- testschema.part2_a_idx
+Partitions: testschema.part_a_idx_1,
+ testschema.part_a_idx_2
Tablespace: "regress_tblspace"
-- partitioned rels cannot specify the default tablespace. These fail:
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index ea607bed0a4..4563eb46275 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -69,7 +69,7 @@ CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL);
id | int4range | | not null |
valid_at | daterange | | not null |
Indexes:
- "temporal_rng2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ "temporal_rng_pk_1" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
DROP TABLE temporal_rng2;
-- no PK from INHERITS:
@@ -2304,7 +2304,7 @@ UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)';
UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)';
-- should fail:
UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)';
-ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey"
+ERROR: conflicting key value violates exclusion constraint "temporal_partitioned_fk_rng2rng_pk_1"
DETAIL: Key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)) conflicts with existing key (id, valid_at)=([1,2),
[2000-01-01,2000-04-01)).
--
-- partitioned FK referenced updates NO ACTION
@@ -2426,7 +2426,7 @@ UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[4,5)' WHERE id = '[1,2)'
UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[1,2)' WHERE id = '[4,5)';
-- should fail:
UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2000-01-01', '2000-04-01'))
WHEREid = '[1,2)';
-ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey"
+ERROR: conflicting key value violates exclusion constraint "temporal_partitioned_fk_mltrng2mltrng_pk_1"
DETAIL: Key (id, valid_at)=([1,2), {[2000-01-01,2000-04-01)}) conflicts with existing key (id, valid_at)=([1,2),
{[2000-01-01,2000-04-01)}).
--
-- partitioned FK referenced updates NO ACTION
Re: [EXTERNAL] Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Dilip Kumar
Date:
On Thu, Jun 19, 2025 at 7:38 PM Chrzan, Maximilian <maximilian.chrzan@here.com> wrote: > > We are working with very large partitioned tables (500M+ rows, >1 TB of data) and need to create multiple expression indexeson them. > > To avoid the issues with parallel index creation, we switched to sequential execution: as soon as one index finishes (usuallyafter 1–2 hours), we immediately start the next (typically within a second). In this setup, there is no actual parallelism— yet we occasionally still hit this error: > > ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index" > Detail: Key (relname, relnamespace) = (…) already exists. > > This suggests that the issue is not limited to concurrent execution. It can also occur when index creation happens in quicksuccession. > > Additionally, we noticed that two parallel index creations on a partitioned table will block each other — even if theytarget different expressions. Here's a simplified example: > > CREATE TABLE test ( > jsondata JSONB, > version BIGINT NOT NULL DEFAULT 9223372036854775807 > ) PARTITION BY RANGE (version); > > CREATE TABLE test_p0 PARTITION OF test FOR VALUES FROM (0) TO (100000); > > Transaction 1: > > DO $$ > BEGIN > CREATE INDEX IF NOT EXISTS idx_1 ON test > (((jsondata -> 'properties') -> 'foo1') ASC NULLS LAST); > PERFORM pg_sleep(10); > END; > $$; > > Transaction 2 (started in parallel): > > DO $$ > BEGIN > CREATE INDEX IF NOT EXISTS idx_2 ON test > (((jsondata -> 'properties') -> 'foo2') ASC NULLS LAST); > END; > $$; > > Transaction 2 will block until Transaction 1 completes — and then fail with: I believe this is fundamentally the same issue we're addressing here. We're observing duplicate index name creation on child tables. If the first transaction remains open, the second transaction waits for it to commit or roll back because it's attempting to insert the same index name key into the catalog. Once the first transaction commits, the second will roll back due to a unique key violation. Conversely, if the first transaction rolls back, the second will succeed. -- Regards, Dilip Kumar Google
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Junwang Zhao
Date:
Hi Tom, On Thu, Jun 19, 2025 at 12:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I wrote: > > This seems very closely related to commit 3db61db48 [1], which fixed > > a similar behavior for child foreign key constraints. Per that commit > > message, it's a good idea for the child objects to have names related > > to the parent objects, so we ought to change this behavior regardless > > of any concurrent-failure considerations. > > I experimented with the attached, which borrows a couple of ideas > from 3db61db48 to produce names like "parent_index_2" when cloning > indexes. While it should help with the immediate problem, I'm not > sure if this is acceptable, because there are a *lot* of ensuing > changes in the regression tests, many more than 3db61db48 caused. > (Note that I didn't bother to fix places where the tests rely on > a generated name that has changed; the delta in the test outputs > is merely meant to give an idea of how much churn there is. > I didn't check non-core test suites, either.) I think this approach is better because each child index inherits its parent's index name with an extra number, creating a more intuitive hierarchy. This naming convention makes it easier to understand the partition levels directly from the index name. So I'm +1 for this idea. > > Also, looking at the error message changes, I'm less sure that > this is a UX improvement than I was about 3db61db48. Do people > care which partition a uniqueness constraint failed in? In > the current behavior, the index name will reflect that, but > with this behavior, not so much. I can see the benefit of being able to identify the associated partition directly by checking the index name. Can we prepend the partition rel name to the index name, this will make the index longer, not sure if it's acceptable. > > Anyway, maybe this is a good idea or maybe it isn't. Thoughts? > > regards, tom lane > -- Regards Junwang Zhao
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Dilip Kumar
Date:
On Wed, Jun 18, 2025 at 10:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I wrote: > > This seems very closely related to commit 3db61db48 [1], which fixed > > a similar behavior for child foreign key constraints. Per that commit > > message, it's a good idea for the child objects to have names related > > to the parent objects, so we ought to change this behavior regardless > > of any concurrent-failure considerations. > > I experimented with the attached, which borrows a couple of ideas > from 3db61db48 to produce names like "parent_index_2" when cloning > indexes. While it should help with the immediate problem, I'm not > sure if this is acceptable, because there are a *lot* of ensuing > changes in the regression tests, many more than 3db61db48 caused. > (Note that I didn't bother to fix places where the tests rely on > a generated name that has changed; the delta in the test outputs > is merely meant to give an idea of how much churn there is. > I didn't check non-core test suites, either.) > > Also, looking at the error message changes, I'm less sure that > this is a UX improvement than I was about 3db61db48. Do people > care which partition a uniqueness constraint failed in? In > the current behavior, the index name will reflect that, but > with this behavior, not so much. > > Anyway, maybe this is a good idea or maybe it isn't. Thoughts? I haven't reviewed the patch itself, but I like the idea. We're now consistently using the parent index name for partitioned indexes, whether they're named or unnamed indexes. That looks like a great improvement. And I think including the partition number of each level in the index name significantly enhances its clarity, especially within a multi-level partition hierarchy. -- Regards, Dilip Kumar Google
Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table.
From
Tom Lane
Date:
Dilip Kumar <dilipbalaut@gmail.com> writes:
> Yes, that makes sense to apply in v19 because of user visible behavior
> changes in index names. I agree the SnapshotDirty patch can give
> relief for this case for back branches.
OK, I pushed the SnapshotDirty patch. The other patch still seems
to apply over it, so I won't repost that unless the cfbot thinks
differently.
regards, tom lane