Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uRvvy-000xTR-9i for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Jun 2025 16:46:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uRvvv-002o8n-26 for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Jun 2025 16:46:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uRvvu-002o8f-JL for pgsql-bugs@lists.postgresql.org; Wed, 18 Jun 2025 16:46:43 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uRvvs-002nDI-2L for pgsql-bugs@lists.postgresql.org; Wed, 18 Jun 2025 16:46:42 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 55IGkZ1A1138733; Wed, 18 Jun 2025 12:46:35 -0400 From: Tom Lane To: Dilip Kumar cc: maximilian.chrzan@here.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table. In-reply-to: <1081051.1750260111@sss.pgh.pa.us> References: <18959-f63b53b864bb1417@postgresql.org> <1081051.1750260111@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 18 Jun 2025 11:21:51 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1138684.1750265169.0@sss.pgh.pa.us> Date: Wed, 18 Jun 2025 12:46:35 -0400 Message-ID: <1138732.1750265195@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1138684.1750265169.1@sss.pgh.pa.us> 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="wip-change-choice-of-cloned-index-names.patch"; charset="us-ascii" Content-ID: <1138684.1750265169.2@sss.pgh.pa.us> Content-Description: wip-change-choice-of-cloned-index-names.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/index= cmds.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 =3D 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 =3D false; = childrel =3D table_open(childRelid, lockmode); + childNamespace =3D RelationGetNamespace(childrel); = GetUserIdAndSecContext(&child_save_userid, &child_save_sec_context); @@ -1507,6 +1509,7 @@ DefineIndex(Oid tableId, * original IndexStmt might not be. */ childStmt =3D 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 la= bel * if needed to create a name that is unique within the specified namespa= ce. + * If the given label is empty, we only consider names that include at le= ast + * 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 wha= t @@ -2609,8 +2614,11 @@ ChooseRelationName(const char *name1, const char *n= ame2, char *relname =3D 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] !=3D '\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/table= cmds.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 o= wnerId, RelationGetDescr(parent), false); idxstmt =3D - generateClonedIndexStmt(NULL, idxRel, + generateClonedIndexStmt(NULL, + RelationGetNamespace(rel), + idxRel, attmap, &constraintOid); DefineIndex(RelationGetRelid(rel), idxstmt, @@ -20654,6 +20656,7 @@ AttachPartitionEnsureIndexes(List **wqueue, Relati= on rel, Relation attachrel) Oid conOid; = stmt =3D 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, TableLikeCl= ause *table_like_clause) = /* Build CREATE INDEX statement to recreate the parent_index */ index_stmt =3D generateClonedIndexStmt(heapRel, + RelationGetNamespace(childrel), parent_index, attmap, NULL); @@ -1656,6 +1657,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *of= Typename) * heapRel is stored into the IndexStmt's relation field, but we don't us= e 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 *of= Typename) * 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 =3D false; index->reset_default_tblspc =3D 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 =3D 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 =3D false; = + /* + * Choose a name for the new index. Ideally we'd preserve the name of t= he + * source index, but that would lead to duplicate-relation-name failures + * if the new table is in the same schema. Instead use ChooseRelationNam= e, + * which will append digits as needed to make a unique name. + */ + index->idxname =3D ChooseRelationName(RelationGetRelationName(source_idx= ), + NULL, "", + heapNamespace, + index->isconstraint); + /* Get the index expressions, if any */ datum =3D 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(Parse= State *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) partitio= n 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 numer= ic 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 'pa= rent 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_partition= ed_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_partition= ed_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_constrain= t 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)=3D(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)=3D(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 D= EFERRED + "notnull_tbl4_pkey_1" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEF= ERRED 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 D= EFERRED + "notnull_tbl4_pkey_2" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEF= ERRED 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 =3D 1 - "part_column_drop_1_10_d_idx" btree (d) WHERE d =3D 2 - "part_column_drop_1_10_expr_idx" btree ((b =3D 1)) - "part_column_drop_1_10_expr_idx1" btree ((d =3D 2)) + "part_column_drop_b_expr_1" btree ((b =3D 1)) + "part_column_drop_b_pred_1" btree (b) WHERE b =3D 1 + "part_column_drop_d_expr_1" btree ((d =3D 2)) + "part_column_drop_d_pred_1" btree (d) WHERE d =3D 2 = drop table part_column_drop; diff --git a/src/test/regress/expected/create_table_like.out b/src/test/re= gress/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); /* copie= s 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)=3D(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 I= NCLUDING 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 =3D 'pg_class'::regclass AND objoid =3D i.indexr= elid AND c.oid =3D i.indexrelid AND i.indrelid =3D 'ctlt_all'::regclass OR= DER 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 =3D 'pg_statistic_ext'::regclass AND objoid =3D s.oi= d AND s.stxrelid =3D '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/regres= s/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=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_child_id_idx +NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_partidx_1 REINDEX INDEX CONCURRENTLY concur_reindex_partidx; -NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_child_id_idx +NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_partidx_1 REINDEX TABLE concur_reindex_part; -NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_child_id_idx +NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_partidx_1 REINDEX TABLE CONCURRENTLY concur_reindex_part; -NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.concur_reindex_child_id_idx +NOTICE: REINDEX END: command_tag=3DREINDEX type=3Dindex identity=3Dpubli= c.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/exp= ected/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::re= gclass from pg_class left join pg_index ix on (indexrelid =3D oid) left join pg_inherits on (ix.indexrelid =3D 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 valu= es 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 requ= ires 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 indexe= s); 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 =3D oid) left join pg_inherits on (ix.indexrelid =3D 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 va= lues 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 =3D oid) left join pg_inherits on (ix.indexrelid =3D 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 =3D 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 =3D 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 =3D 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 =3D true; @@ -478,9 +459,9 @@ create index idxparti3 on idxpart ((a+b)) where d =3D = 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 =3D 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 =3D 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 va= lues 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 (300= 0); 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 independentl= y @@ -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 va= lues from (1000) to (2000); create table idxpart3 partition of idxpart for values from (2000) to (300= 0); -- b) after detaching, dropping the index on parent does not remove the o= thers 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 pare= nt, pg_get_indexdef as child from pg_class join pg_inherits on inhrelid =3D oid, lateral pg_get_indexdef(pg_class.oid) where relkind in ('i', 'I') and relname like 'idxpart%' order by relnam= e; - child | parent | c= hilddef = --------------------+------------------+----------------------------------= ----------------------------------------- - 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 O= N 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 pare= nt, pg_get_indexdef as child where relkind in ('i', 'I') and relname like 'idxpart%' order by relnam= e; child | parent | chi= lddef = -----------------+---------------+---------------------------------------= ----------------------------------------- - 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 pub= lic.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 pare= nt, pg_get_indexdef as child where relkind in ('i', 'I') and relname like 'idxpart%' order by relnam= e; child | parent | c= hilddef = -----------------+---------------+---------------------------------------= --------------------------------------------- - 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 pub= lic.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 USIN= G 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 USIN= G btree (c, b) idxparti | CREATE INDEX idxparti ON ONLY public.idxpart USING bt= ree (a) idxparti2 | CREATE INDEX idxparti2 ON ONLY public.idxpart USING b= tree (c, b) + idxparti2_1 | CREATE INDEX idxparti2_1 ON public.idxpart1 USING btr= ee (c, b) + idxparti_1 | CREATE INDEX idxparti_1 ON public.idxpart1 USING btre= e (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 =3D 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 USI= NG btree (abs(b)) - idxpart1_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 US= ING btree (((b + 1))) - idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USI= NG btree (abs(b)) - idxpart2_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 US= ING 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 US= ING 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 U= SING btree (abs(b)) + idxpart_expr_idx | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpar= t 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 =3D 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 bt= ree (a) WHERE (b > 1000) - idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING bt= ree (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 b= tree (a) WHERE (b > 1000) + idxpart_a_idx | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USIN= G 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 value= s 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 v= alues 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, i= nhparent::regclass, indisvalid left join pg_constraint con on (idx.indexrelid =3D con.conindid) where indrelid::regclass::text like 'idxpart%' order by indexrelid::regclass::text collate "C"; - indrelid | indexrelid | inhparent | indisvalid | conname | c= onislocal | 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 requir= es it HINT: You can drop index idxpart_pkey instead. drop index idxpart1_pkey; -- fail -ERROR: cannot drop index idxpart1_pkey because index idxpart_pkey requir= es 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 "idx= part0" alter table idxpart1 drop constraint idxpart1_pkey; -- fail -ERROR: cannot drop inherited constraint "idxpart1_pkey" of relation "idx= part1" +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, ind= isvalid, conname, conislocal, coninhcount, connoinherit, convalidated @@ -1295,11 +1279,11 @@ select indrelid::regclass, indexrelid::regclass, i= nhparent::regclass, indisvalid left join pg_constraint con on (idx.indexrelid =3D 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 gene= rate_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)=3D(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^1= 6 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)=3D(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)=3D(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 = =3D 'abcd'; ------------------------------------------- Bitmap Heap Scan on idxpart1 idxpart Recheck Cond: (b =3D 'abcd'::text) - -> Bitmap Index Scan on idxpart1_b_idx + -> Bitmap Index Scan on idxpart_brin_1 Index Cond: (b =3D '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 =3D 'abcd'; - QUERY PLAN = -------------------------------------------- + QUERY PLAN = +--------------------------------------------- Bitmap Heap Scan on idxpart1 idxpart Recheck Cond: (b =3D 'abcd'::text) - -> Bitmap Index Scan on idxpart1_b_idx + -> Bitmap Index Scan on idxpart_spgist_1 Index Cond: (b =3D '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 f= or 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_i= dx" +ERROR: duplicate key value violates unique constraint "covidxpart_a_b_id= x_1" DETAIL: Key (a)=3D(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_i= dx" +ERROR: duplicate key value violates unique constraint "covidxpart_a_b_id= x_3" DETAIL: Key (a)=3D(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" l= acks 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 fo= r 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_d= etach_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_d= etach_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_tes= t for values in (1); alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_t= est1_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_u= niq_detach_test1" does not exist alter table parted_uniq_detach_test detach partition parted_uniq_detach_t= est1; alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_t= est1_a_key; +ERROR: constraint "parted_uniq_detach_test1_a_key" of relation "parted_u= niq_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 =3D 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 =3D 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 | pa= rted_isvalid_tab_1_expr_idx - parted_isvalid_tab_12_expr_idx | t | parted_isvalid_tab_12 | pa= rted_isvalid_tab_1_expr_idx - parted_isvalid_tab_1_expr_idx | f | parted_isvalid_tab_1 | pa= rted_isvalid_idx - parted_isvalid_tab_2_expr_idx | t | parted_isvalid_tab_2 | pa= rted_isvalid_idx + indexrelid | indisvalid | indrelid | inhpa= rent = +------------------------+------------+-----------------------+-----------= ----------- + parted_isvalid_idx | f | parted_isvalid_tab | = + parted_isvalid_idx_1 | f | parted_isvalid_tab_1 | parted_isv= alid_idx + parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isv= alid_idx_1 + parted_isvalid_idx_1_1 | t | parted_isvalid_tab_12 | parted_isv= alid_idx_1 + parted_isvalid_idx_2 | t | parted_isvalid_tab_2 | parted_isv= alid_idx (5 rows) = drop table parted_isvalid_tab; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expe= cted/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 mcrpar= ted_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 mcrparte= d_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 sca= ns 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 mcr= parted_6 - -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcr= parted_5 - -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcr= parted_4 - -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcr= parted_3 - -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcr= parted_2 - -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcr= parted_1 + -> Index Scan Backward using mcrparted_a_abs_c_idx_6 on mcrparted5 mc= rparted_6 + -> Index Scan Backward using mcrparted_a_abs_c_idx_5 on mcrparted4 mc= rparted_5 + -> Index Scan Backward using mcrparted_a_abs_c_idx_4 on mcrparted3 mc= rparted_4 + -> Index Scan Backward using mcrparted_a_abs_c_idx_3 on mcrparted2 mc= rparted_3 + -> Index Scan Backward using mcrparted_a_abs_c_idx_2 on mcrparted1 mc= rparted_2 + -> Index Scan Backward using mcrparted_a_abs_c_idx_1 on mcrparted0 mc= rparted_1 (7 rows) = -- check that Append plan is used containing a MergeAppend for sub-partit= ions @@ -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 mcrp= arted_7 - -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_de= f mcrparted_8 + -> Index Scan using mcrparted_a_abs_c_idx_6_1 on mcrparted5a mc= rparted_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 mcrparte= d_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 =3D 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 =3D 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 =3D 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 =3D 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 =3D ANY ('{1,2,4}'::integer[])) (7 rows) = explain (costs off) select * from mclparted where a in(1,2,4) or a is nul= l 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 =3D 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 =3D 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 =3D ANY ('{1,2,4}'::integer[])) OR (a IS NULL)) - -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclpa= rted_4 + -> Index Only Scan using mclparted_a_idx_5 on mclparted_null mclparte= d_4 Filter: ((a =3D 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 nul= l 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 m= clparted_1 + -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclpar= ted_1 Filter: ((a =3D 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 =3D 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 =3D 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 =3D 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 m= clparted_1 + -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclpar= ted_1 Index Cond: (a =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 b= y 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 =3D 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 =3D 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 =3D ANY ('{1,2,4,100}'::integer[])) - -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclpart= ed_4 + -> Index Only Scan using mclparted_a_idx_6 on mclparted_def mclparted= _4 Index Cond: (a =3D 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,1= 000) to (true,2000); create index on bool_rp (b,a); explain (costs off) select * from bool_rp where b =3D 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 b= ool_rp_1 + -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp= _1 Index Cond: (b =3D true) - -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k b= ool_rp_2 + -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp= _2 Index Cond: (b =3D true) (5 rows) = explain (costs off) select * from bool_rp where b =3D 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_r= p_1 Index Cond: (b =3D 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_r= p_2 Index Cond: (b =3D false) (5 rows) = explain (costs off) select * from bool_rp where b =3D 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 b= ool_rp_1 + -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp= _1 Index Cond: (b =3D true) - -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k b= ool_rp_2 + -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp= _2 Index Cond: (b =3D true) (5 rows) = explain (costs off) select * from bool_rp where b =3D 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_r= p_1 Index Cond: (b =3D 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_r= p_2 Index Cond: (b =3D false) (5 rows) = @@ -3611,19 +3611,19 @@ create table range_parted1 partition of range_part= ed 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 ran= ge_parted_1 - -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 ran= ge_parted_2 + -> Index Only Scan using range_parted_a_b_c_idx_1 on range_parted1 ra= nge_parted_1 + -> Index Only Scan using range_parted_a_b_c_idx_2 on range_parted2 ra= nge_parted_2 (3 rows) = explain (costs off) select * from range_parted order by a desc,b desc,c d= esc; - QUERY PLAN = = --------------------------------------------------------------------------= --------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ---------------------- Append - -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_pa= rted2 range_parted_2 - -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_pa= rted1 range_parted_1 + -> Index Only Scan Backward using range_parted_a_b_c_idx_2 on range_p= arted2 range_parted_2 + -> Index Only Scan Backward using range_parted_a_b_c_idx_1 on range_p= arted1 range_parted_1 (3 rows) = drop table range_parted; diff --git a/src/test/regress/expected/partition_join.out b/src/test/regre= ss/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 =3D prtx1_1.b) AND (c =3D 123)) Filter: (a =3D prtx1_1.a) -> BitmapAnd - -> Bitmap Index Scan on prtx2_1_b_idx + -> Bitmap Index Scan on prtx2_b_idx_1 Index Cond: (b =3D prtx1_1.b) - -> Bitmap Index Scan on prtx2_1_c_idx + -> Bitmap Index Scan on prtx2_c_idx_1 Index Cond: (c =3D 123) -> Nested Loop Anti Join -> Seq Scan on prtx1_2 @@ -2541,9 +2541,9 @@ where not exists (select 1 from prtx2 Recheck Cond: ((b =3D prtx1_2.b) AND (c =3D 123)) Filter: (a =3D prtx1_2.a) -> BitmapAnd - -> Bitmap Index Scan on prtx2_2_b_idx + -> Bitmap Index Scan on prtx2_b_idx_2 Index Cond: (b =3D prtx1_2.b) - -> Bitmap Index Scan on prtx2_2_c_idx + -> Bitmap Index Scan on prtx2_c_idx_2 Index Cond: (c =3D 123) (23 rows) = @@ -2571,9 +2571,9 @@ where not exists (select 1 from prtx2 Recheck Cond: ((b =3D (prtx1_1.b + 1)) OR (c =3D 99)) Filter: (a =3D prtx1_1.a) -> BitmapOr - -> Bitmap Index Scan on prtx2_1_b_idx + -> Bitmap Index Scan on prtx2_b_idx_1 Index Cond: (b =3D (prtx1_1.b + 1)) - -> Bitmap Index Scan on prtx2_1_c_idx + -> Bitmap Index Scan on prtx2_c_idx_1 Index Cond: (c =3D 99) -> Nested Loop Anti Join -> Seq Scan on prtx1_2 @@ -2582,9 +2582,9 @@ where not exists (select 1 from prtx2 Recheck Cond: ((b =3D (prtx1_2.b + 1)) OR (c =3D 99)) Filter: (a =3D prtx1_2.a) -> BitmapOr - -> Bitmap Index Scan on prtx2_2_b_idx + -> Bitmap Index Scan on prtx2_b_idx_2 Index Cond: (b =3D (prtx1_2.b + 1)) - -> Bitmap Index Scan on prtx2_2_c_idx + -> Bitmap Index Scan on prtx2_c_idx_2 Index Cond: (c =3D 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 =3D t2.a) INNER JOIN prt1_adv t3 ON (t1.b =3D t3.a) WHERE= t1.a =3D 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 =3D 0) - -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p= 1 t3_1 + -> Index Scan using prt1_adv_a_idx_1 on prt1_adv_p1= t3_1 Index Cond: (a =3D 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 =3D t1_1.b) -> Hash Right Join Hash Cond: (t2_2.a =3D t1_2.b) @@ -5228,53 +5228,53 @@ SET max_parallel_workers_per_gather =3D 0; SET enable_partitionwise_join =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 =3D 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 10= 0; - QUERY PLAN = ------------------------------------------------------------------------- + QUERY PLAN = +----------------------------------------------------------------------- Limit -> Append -> Nested Loop @@ -5316,21 +5316,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht= 1 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 =3D 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 =3D 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 =3D p1_3.c) (23 rows) = @@ -5361,8 +5361,8 @@ SET max_parallel_workers_per_gather =3D 1; SET debug_parallel_query =3D on; -- Partial paths should also be smart enough to employ limits EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 10= 0; - QUERY PLAN = = --------------------------------------------------------------------------= ----- + QUERY PLAN = = +-------------------------------------------------------------------------= ---- Gather Workers Planned: 1 Single Copy: true @@ -5373,21 +5373,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht= 1 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 p= 2_1 Index Cond: (c =3D 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 p= 2_2 Index Cond: (c =3D 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 p= 2_3 Index Cond: (c =3D p1_3.c) (26 rows) = diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regr= ess/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 >=3D $1 and a % 10 =3D= 5 order by b; explain (analyze, costs off, summary off, timing off, buffers off) execut= e mt_q1(15); - QUERY PLAN = = --------------------------------------------------------------------------= ------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------ Merge Append (actual rows=3D2.00 loops=3D1) 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=3D1.00 loops=3D1) + -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_1 (actual r= ows=3D1.00 loops=3D1) Filter: ((a >=3D $1) AND ((a % 10) =3D 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=3D1.00 loops=3D1) + -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_2 (actual r= ows=3D1.00 loops=3D1) Filter: ((a >=3D $1) AND ((a % 10) =3D 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) execut= e mt_q1(25); - QUERY PLAN = = --------------------------------------------------------------------------= ------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------ Merge Append (actual rows=3D1.00 loops=3D1) 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=3D1.00 loops=3D1) + -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_1 (actual r= ows=3D1.00 loops=3D1) Filter: ((a >=3D $1) AND ((a % 10) =3D 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 >=3D (select min(b) from ma_test_p2) order by b; - QUERY PLAN = = --------------------------------------------------------------------------= ------------------------- + QUERY PLAN = = +-------------------------------------------------------------------------= ------------------------ Merge Append (actual rows=3D20.00 loops=3D1) Sort Key: ma_test.b InitPlan 2 -> Result (actual rows=3D1.00 loops=3D1) InitPlan 1 -> Limit (actual rows=3D1.00 loops=3D1) - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (a= ctual rows=3D1.00 loops=3D1) + -> Index Scan using ma_test_b_idx_2 on ma_test_p2 (ac= tual rows=3D1.00 loops=3D1) 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 e= xecuted) + -> Index Scan using ma_test_b_idx_1 on ma_test_p1 ma_test_1 (never ex= ecuted) Filter: (a >=3D (InitPlan 2).col1) Index Searches: 0 - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual = rows=3D10.00 loops=3D1) + -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_2 (actual r= ows=3D10.00 loops=3D1) Filter: (a >=3D (InitPlan 2).col1) Index Searches: 1 - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual = rows=3D10.00 loops=3D1) + -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_3 (actual r= ows=3D10.00 loops=3D1) Filter: (a >=3D (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=3D0.00 loops=3D1) InitPlan 1 -> Result (actual rows=3D1.00 loops=3D1) @@ -4225,16 +4225,16 @@ select * from rangep where b IN((select 1),(select= 2)) order by a; -> Result (actual rows=3D1.00 loops=3D1) -> Merge Append (actual rows=3D0.00 loops=3D1) 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=3D0.00 loops=3D1) + -> Index Scan using rangep_a_idx_1_1 on rangep_0_to_100_1 range= p_2 (actual rows=3D0.00 loops=3D1) Filter: (b =3D 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=3D0.00 loops=3D1) + -> Index Scan using rangep_a_idx_1_2 on rangep_0_to_100_2 range= p_3 (actual rows=3D0.00 loops=3D1) Filter: (b =3D 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 range= p_4 (never executed) Filter: (b =3D 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 rang= ep_5 (actual rows=3D0.00 loops=3D1) + -> Index Scan using rangep_a_idx_2 on rangep_100_to_200 rangep_5 (act= ual rows=3D0.00 loops=3D1) Filter: (b =3D 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) fro= m part_abc where a >=3D 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 >=3D (stable_one() + 1)) Filter: (d <=3D stable_one()) -> Merge Append Sort Key: part_abc_3.a Subplans Removed: 1 - -> Index Scan using part_abc_3_1_a_idx on par= t_abc_3_1 part_abc_3 + -> Index Scan using part_abc_a_idx_3_1 on par= t_abc_3_1 part_abc_3 Index Cond: (a >=3D (stable_one() + 1)) Filter: (d <=3D stable_one()) - -> Index Scan using part_abc_3_2_a_idx on par= t_abc_3_2 part_abc_4 + -> Index Scan using part_abc_a_idx_3_2 on par= t_abc_3_2 part_abc_4 Index Cond: (a >=3D (stable_one() + 1)) Filter: (d <=3D stable_one()) -> Subquery Scan on "*SELECT* 2" @@ -4785,16 +4785,16 @@ select min(a) over (partition by a order by a) fro= m part_abc where a >=3D 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 >=3D (stable_one() + 1)) Filter: (d >=3D stable_one()) -> Merge Append Sort Key: a Subplans Removed: 1 - -> Index Scan using part_abc_3_2_a_idx on par= t_abc_3_2 part_abc_8 + -> Index Scan using part_abc_a_idx_3_2 on par= t_abc_3_2 part_abc_8 Index Cond: (a >=3D (stable_one() + 1)) Filter: (d >=3D stable_one()) - -> Index Scan using part_abc_3_3_a_idx on par= t_abc_3_3 part_abc_9 + -> Index Scan using part_abc_a_idx_3_3 on par= t_abc_3_3 part_abc_9 Index Cond: (a >=3D (stable_one() + 1)) Filter: (d >=3D stable_one()) (35 rows) diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expecte= d/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 tabl= e | | = + testpart | child_30_40 | regress_partitioning_role | partitioned tabl= e | parent_tab | = + testpart | parent_index | regress_partitioning_role | partitioned inde= x | | parent_tab + testpart | parent_index_4 | regress_partitioning_role | partitioned inde= x | 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 | | p= arent_tab + testpart | parent_index_4 | regress_partitioning_role | parent_index | c= hild_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 tabl= e | | = + testpart | child_30_40 | regress_partitioning_role | partitioned tabl= e | parent_tab | = + testpart | parent_index | regress_partitioning_role | partitioned inde= x | | parent_tab + testpart | parent_index_4 | regress_partitioning_role | partitioned inde= x | 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 tabl= e | | = + testpart | child_30_40 | regress_partitioning_role | partitioned tabl= e | parent_tab | = + testpart | parent_index | regress_partitioning_role | partitioned inde= x | | parent_tab + testpart | parent_index_4 | regress_partitioning_role | partitioned inde= x | 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/e= xpected/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) TABLE= SPACE 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_cl= ass c where c.reltablespace =3D 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 =3D 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/reg= ress/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 =3D '[= 4,5)' WHERE id =3D '[1,2)'; UPDATE temporal_partitioned_fk_rng2rng SET id =3D '[1,2)' WHERE id =3D '[= 4,5)'; -- should fail: UPDATE temporal_partitioned_fk_rng2rng SET valid_at =3D daterange('2000-0= 1-01', '2000-04-01') WHERE id =3D '[1,2)'; -ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" +ERROR: conflicting key value violates exclusion constraint "temporal_par= titioned_fk_rng2rng_pk_1" DETAIL: Key (id, valid_at)=3D([1,2), [2000-01-01,2000-04-01)) conflicts = with existing key (id, valid_at)=3D([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 = =3D '[4,5)' WHERE id =3D '[1,2)' UPDATE temporal_partitioned_fk_mltrng2mltrng SET id =3D '[1,2)' WHERE id = =3D '[4,5)'; -- should fail: UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at =3D datemultira= nge(daterange('2000-01-01', '2000-04-01')) WHERE id =3D '[1,2)'; -ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" +ERROR: conflicting key value violates exclusion constraint "temporal_par= titioned_fk_mltrng2mltrng_pk_1" DETAIL: Key (id, valid_at)=3D([1,2), {[2000-01-01,2000-04-01)}) conflict= s with existing key (id, valid_at)=3D([1,2), {[2000-01-01,2000-04-01)}). -- -- partitioned FK referenced updates NO ACTION ------- =_aaaaaaaaaa0--