Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table. - Mailing list pgsql-bugs
| From | Tom Lane |
|---|---|
| Subject | Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table. |
| Date | |
| Msg-id | 1138732.1750265195@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: BUG #18959: Name collisions of expression indexes during parallel Index creations on a pratitioned table. (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-bugs |
I wrote:
> This seems very closely related to commit 3db61db48 [1], which fixed
> a similar behavior for child foreign key constraints. Per that commit
> message, it's a good idea for the child objects to have names related
> to the parent objects, so we ought to change this behavior regardless
> of any concurrent-failure considerations.
I experimented with the attached, which borrows a couple of ideas
from 3db61db48 to produce names like "parent_index_2" when cloning
indexes. While it should help with the immediate problem, I'm not
sure if this is acceptable, because there are a *lot* of ensuing
changes in the regression tests, many more than 3db61db48 caused.
(Note that I didn't bother to fix places where the tests rely on
a generated name that has changed; the delta in the test outputs
is merely meant to give an idea of how much churn there is.
I didn't check non-core test suites, either.)
Also, looking at the error message changes, I'm less sure that
this is a UX improvement than I was about 3db61db48. Do people
care which partition a uniqueness constraint failed in? In
the current behavior, the index name will reflect that, but
with this behavior, not so much.
Anyway, maybe this is a good idea or maybe it isn't. Thoughts?
regards, tom lane
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index c3ec2076a52..8eb6f429383 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1367,6 +1367,7 @@ DefineIndex(Oid tableId,
{
Oid childRelid = part_oids[i];
Relation childrel;
+ Oid childNamespace;
Oid child_save_userid;
int child_save_sec_context;
int child_save_nestlevel;
@@ -1376,6 +1377,7 @@ DefineIndex(Oid tableId,
bool found = false;
childrel = table_open(childRelid, lockmode);
+ childNamespace = RelationGetNamespace(childrel);
GetUserIdAndSecContext(&child_save_userid,
&child_save_sec_context);
@@ -1507,6 +1509,7 @@ DefineIndex(Oid tableId,
* original IndexStmt might not be.
*/
childStmt = generateClonedIndexStmt(NULL,
+ childNamespace,
parentIndex,
attmap,
NULL);
@@ -2584,6 +2587,8 @@ makeObjectName(const char *name1, const char *name2, const char *label)
* name1, name2, and label are used the same way as for makeObjectName(),
* except that the label can't be NULL; digits will be appended to the label
* if needed to create a name that is unique within the specified namespace.
+ * If the given label is empty, we only consider names that include at least
+ * one added digit.
*
* If isconstraint is true, we also avoid choosing a name matching any
* existing constraint in the same namespace. (This is stricter than what
@@ -2609,8 +2614,11 @@ ChooseRelationName(const char *name1, const char *name2,
char *relname = NULL;
char modlabel[NAMEDATALEN];
- /* try the unmodified label first */
- strlcpy(modlabel, label, sizeof(modlabel));
+ /* try the unmodified label first, unless it's empty */
+ if (label[0] != '\0')
+ strlcpy(modlabel, label, sizeof(modlabel));
+ else
+ snprintf(modlabel, sizeof(modlabel), "%s%d", label, ++pass);
for (;;)
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ea96947d813..7fe357ffa0a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1295,7 +1295,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
RelationGetDescr(parent),
false);
idxstmt =
- generateClonedIndexStmt(NULL, idxRel,
+ generateClonedIndexStmt(NULL,
+ RelationGetNamespace(rel),
+ idxRel,
attmap, &constraintOid);
DefineIndex(RelationGetRelid(rel),
idxstmt,
@@ -20654,6 +20656,7 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel)
Oid conOid;
stmt = generateClonedIndexStmt(NULL,
+ RelationGetNamespace(attachrel),
idxRel, attmap,
&conOid);
DefineIndex(RelationGetRelid(attachrel), stmt, InvalidOid,
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 62015431fdf..df217c87da4 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1541,6 +1541,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
/* Build CREATE INDEX statement to recreate the parent_index */
index_stmt = generateClonedIndexStmt(heapRel,
+ RelationGetNamespace(childrel),
parent_index,
attmap,
NULL);
@@ -1656,6 +1657,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
* heapRel is stored into the IndexStmt's relation field, but we don't use it
* otherwise; some callers pass NULL, if they don't need it to be valid.
* (The target relation might not exist yet, so we mustn't try to access it.)
+ * The namespace OID for the target relation must be provided, though.
*
* Attribute numbers in expression Vars are adjusted according to attmap.
*
@@ -1668,7 +1670,9 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
* complain if that fails to happen).
*/
IndexStmt *
-generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
+generateClonedIndexStmt(RangeVar *heapRel,
+ Oid heapNamespace,
+ Relation source_idx,
const AttrMap *attmap,
Oid *constraintOid)
{
@@ -1746,14 +1750,6 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
index->if_not_exists = false;
index->reset_default_tblspc = false;
- /*
- * We don't try to preserve the name of the source index; instead, just
- * let DefineIndex() choose a reasonable name. (If we tried to preserve
- * the name, we'd get duplicate-relation-name failures unless the source
- * table was in a different schema.)
- */
- index->idxname = NULL;
-
/*
* If the index is marked PRIMARY or has an exclusion condition, it's
* certainly from a constraint; else, if it's not marked UNIQUE, it
@@ -1832,6 +1828,17 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
else
index->isconstraint = false;
+ /*
+ * Choose a name for the new index. Ideally we'd preserve the name of the
+ * source index, but that would lead to duplicate-relation-name failures
+ * if the new table is in the same schema. Instead use ChooseRelationName,
+ * which will append digits as needed to make a unique name.
+ */
+ index->idxname = ChooseRelationName(RelationGetRelationName(source_idx),
+ NULL, "",
+ heapNamespace,
+ index->isconstraint);
+
/* Get the index expressions, if any */
datum = SysCacheGetAttr(INDEXRELID, ht_idx,
Anum_pg_index_indexprs, &isnull);
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 9f2b58de797..1553a4a646c 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -37,6 +37,7 @@ extern PartitionBoundSpec *transformPartitionBound(ParseState *pstate, Relation
extern List *expandTableLikeClause(RangeVar *heapRel,
TableLikeClause *table_like_clause);
extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel,
+ Oid heapNamespace,
Relation source_idx,
const struct AttrMap *attmap,
Oid *constraintOid);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 476266e3f4b..91ab5dcfe6e 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -158,13 +158,14 @@ CREATE TABLE part_attmp (a int primary key) partition by range (a);
CREATE TABLE part_attmp1 PARTITION OF part_attmp FOR VALUES FROM (0) TO (100);
ALTER INDEX part_attmp_pkey RENAME TO part_attmp_index;
ALTER INDEX part_attmp1_pkey RENAME TO part_attmp1_index;
+ERROR: relation "part_attmp1_pkey" does not exist
ALTER TABLE part_attmp RENAME TO part_at2tmp;
ALTER TABLE part_attmp1 RENAME TO part_at2tmp1;
SET ROLE regress_alter_table_user1;
ALTER INDEX part_attmp_index RENAME TO fail;
ERROR: must be owner of index part_attmp_index
ALTER INDEX part_attmp1_index RENAME TO fail;
-ERROR: must be owner of index part_attmp1_index
+ERROR: relation "part_attmp1_index" does not exist
ALTER TABLE part_at2tmp RENAME TO fail;
ERROR: must be owner of table part_at2tmp
ALTER TABLE part_at2tmp1 RENAME TO fail;
@@ -457,7 +458,7 @@ CREATE TABLE like_constraint_rename_cache
--------+---------+-----------+----------+---------
a | integer | | not null |
Indexes:
- "like_constraint_rename_cache_pkey" PRIMARY KEY, btree (a)
+ "constraint_rename_pkey_new_1" PRIMARY KEY, btree (a)
Check constraints:
"chk_a_new" CHECK (a > 0)
@@ -2182,8 +2183,8 @@ create index on at_partitioned (a);
b | text | | |
Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
Indexes:
- "at_part_1_a_idx" btree (a)
- "at_part_1_b_idx" btree (b)
+ "at_partitioned_a_idx_1" btree (a)
+ "at_partitioned_b_idx_1" btree (b)
\d at_part_2
Table "public.at_part_2"
@@ -2201,8 +2202,8 @@ alter table at_partitioned attach partition at_part_2 for values from (1000) to
a | integer | | |
Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
Indexes:
- "at_part_2_a_idx" btree (a)
- "at_part_2_b_idx" btree (b)
+ "at_partitioned_a_idx_2" btree (a)
+ "at_partitioned_b_idx_2" btree (b)
alter table at_partitioned alter column b type numeric using b::numeric;
\d at_part_1
@@ -2213,8 +2214,8 @@ alter table at_partitioned alter column b type numeric using b::numeric;
b | numeric | | |
Partition of: at_partitioned FOR VALUES FROM (0) TO (1000)
Indexes:
- "at_part_1_a_idx" btree (a)
- "at_part_1_b_idx" btree (b)
+ "at_partitioned_a_idx_1" btree (a)
+ "at_partitioned_b_idx_1" btree (b)
\d at_part_2
Table "public.at_part_2"
@@ -2224,8 +2225,8 @@ Indexes:
a | integer | | |
Partition of: at_partitioned FOR VALUES FROM (1000) TO (2000)
Indexes:
- "at_part_2_a_idx" btree (a)
- "at_part_2_b_idx" btree (b)
+ "at_partitioned_a_idx_2" btree (a)
+ "at_partitioned_b_idx_2" btree (b)
drop table at_partitioned;
-- Alter column type when no table rewrite is required
@@ -2237,11 +2238,15 @@ comment on index at_partitioned_id_name_key is 'parent index';
create table at_partitioned_0 partition of at_partitioned
for values with (modulus 2, remainder 0);
comment on constraint at_partitioned_0_id_name_key on at_partitioned_0 is 'child 0 constraint';
+ERROR: constraint "at_partitioned_0_id_name_key" for table "at_partitioned_0" does not exist
comment on index at_partitioned_0_id_name_key is 'child 0 index';
+ERROR: relation "at_partitioned_0_id_name_key" does not exist
create table at_partitioned_1 partition of at_partitioned
for values with (modulus 2, remainder 1);
comment on constraint at_partitioned_1_id_name_key on at_partitioned_1 is 'child 1 constraint';
+ERROR: constraint "at_partitioned_1_id_name_key" for table "at_partitioned_1" does not exist
comment on index at_partitioned_1_id_name_key is 'child 1 index';
+ERROR: relation "at_partitioned_1_id_name_key" does not exist
insert into at_partitioned values(1, 'foo');
insert into at_partitioned values(3, 'bar');
create temp table old_oids as
@@ -2259,24 +2264,24 @@ select relname,
from pg_class c left join old_oids using (relname)
where relname like 'at_partitioned%'
order by relname;
- relname | orig_oid | storage | desc
-------------------------------+----------+---------+---------------
+ relname | orig_oid | storage | desc
+------------------------------+----------+---------+--------------
at_partitioned | t | none |
at_partitioned_0 | t | own |
- at_partitioned_0_id_name_key | t | own | child 0 index
at_partitioned_1 | t | own |
- at_partitioned_1_id_name_key | t | own | child 1 index
at_partitioned_id_name_key | t | none | parent index
+ at_partitioned_id_name_key_1 | t | own |
+ at_partitioned_id_name_key_2 | t | own |
(6 rows)
select conname, obj_description(oid, 'pg_constraint') as desc
from pg_constraint where conname like 'at_partitioned%'
order by conname;
- conname | desc
-------------------------------+--------------------
- at_partitioned_0_id_name_key | child 0 constraint
- at_partitioned_1_id_name_key | child 1 constraint
+ conname | desc
+------------------------------+-------------------
at_partitioned_id_name_key | parent constraint
+ at_partitioned_id_name_key_1 |
+ at_partitioned_id_name_key_2 |
(3 rows)
alter table at_partitioned alter column name type varchar(127);
@@ -2296,10 +2301,10 @@ select relname,
------------------------------+----------+---------+--------------
at_partitioned | t | none |
at_partitioned_0 | t | own |
- at_partitioned_0_id_name_key | f | own |
at_partitioned_1 | t | own |
- at_partitioned_1_id_name_key | f | own |
at_partitioned_id_name_key | f | none | parent index
+ at_partitioned_id_name_key_1 | f | own |
+ at_partitioned_id_name_key_2 | f | own |
(6 rows)
select conname, obj_description(oid, 'pg_constraint') as desc
@@ -2307,9 +2312,9 @@ select conname, obj_description(oid, 'pg_constraint') as desc
order by conname;
conname | desc
------------------------------+-------------------
- at_partitioned_0_id_name_key |
- at_partitioned_1_id_name_key |
at_partitioned_id_name_key | parent constraint
+ at_partitioned_id_name_key_1 |
+ at_partitioned_id_name_key_2 |
(3 rows)
-- Don't remove this DROP, it exposes bug #15672
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index ad6aaab7385..4be20ddad7c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -606,22 +606,22 @@ SELECT conname, conrelid::regclass FROM pg_constraint
WHERE conname LIKE 'parted_uniq%' ORDER BY conname;
conname | conrelid
-------------------------+-------------------
- parted_uniq_tbl_1_i_key | parted_uniq_tbl_1
- parted_uniq_tbl_2_i_key | parted_uniq_tbl_2
parted_uniq_tbl_i_key | parted_uniq_tbl
+ parted_uniq_tbl_i_key_1 | parted_uniq_tbl_1
+ parted_uniq_tbl_i_key_2 | parted_uniq_tbl_2
(3 rows)
BEGIN;
INSERT INTO parted_uniq_tbl VALUES (1);
SAVEPOINT f;
INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation
-ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_i_key_1"
DETAIL: Key (i)=(1) already exists.
ROLLBACK TO f;
SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;
INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
COMMIT;
-ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"
+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_i_key_1"
DETAIL: Key (i)=(1) already exists.
DROP TABLE parted_uniq_tbl;
-- test naming a constraint in a partition when a conflict exists
@@ -1281,7 +1281,7 @@ Not-null constraints:
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
Indexes:
- "notnull_tbl4_lk2_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
+ "notnull_tbl4_pkey_1" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
Not-null constraints:
"notnull_tbl4_a_not_null" NOT NULL "a"
@@ -1291,7 +1291,7 @@ Not-null constraints:
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | not null | | plain | |
Indexes:
- "notnull_tbl4_lk3_pkey" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
+ "notnull_tbl4_pkey_2" PRIMARY KEY, btree (a) DEFERRABLE INITIALLY DEFERRED
Not-null constraints:
"a_nn" NOT NULL "a"
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 76604705a93..c391b7a58e6 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1125,9 +1125,9 @@ Number of partitions: 1 (Use \d+ to list them.)
b | integer | | |
Partition of: part_column_drop FOR VALUES FROM (1) TO (10)
Indexes:
- "part_column_drop_1_10_b_idx" btree (b) WHERE b = 1
- "part_column_drop_1_10_d_idx" btree (d) WHERE d = 2
- "part_column_drop_1_10_expr_idx" btree ((b = 1))
- "part_column_drop_1_10_expr_idx1" btree ((d = 2))
+ "part_column_drop_b_expr_1" btree ((b = 1))
+ "part_column_drop_b_pred_1" btree (b) WHERE b = 1
+ "part_column_drop_d_expr_1" btree ((d = 2))
+ "part_column_drop_d_pred_1" btree (d) WHERE d = 2
drop table part_column_drop;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index bf34289e984..afb26e12cc7 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -284,7 +284,7 @@ DROP TABLE test_like_6, test_like_6c;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
-ERROR: duplicate key value violates unique constraint "inhg_pkey"
+ERROR: duplicate key value violates unique constraint "inhx_pkey_1"
DETAIL: Key (xx)=(10) already exists.
DROP TABLE inhg;
/* Multiple primary keys creation should fail */
@@ -310,7 +310,7 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES);
x | text | | |
xx | text | | not null |
Indexes:
- "inhz_pkey" PRIMARY KEY, btree (xx)
+ "inhx_pkey_1" PRIMARY KEY, btree (xx)
Foreign-key constraints:
"inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx)
Referenced by:
@@ -419,7 +419,7 @@ NOTICE: merging column "a" with inherited definition
b | text | | | | extended | |
c | text | | | | external | | C
Indexes:
- "ctlt13_like_expr_idx" btree ((a || c))
+ "ctlt3_fnidx_1" btree ((a || c))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -443,9 +443,9 @@ CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "ctlt_all_pkey" PRIMARY KEY, btree (a)
- "ctlt_all_b_idx" btree (b)
- "ctlt_all_expr_idx" btree ((a || b))
+ "ctlt1_pkey_1" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_1" btree (b)
+ "ctlt1_fnidx_1" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -456,10 +456,10 @@ Not-null constraints:
"ctlt1_a_not_null" NOT NULL "a"
SELECT c.relname, objsubid, description FROM pg_description, pg_index i, pg_class c WHERE classoid =
'pg_class'::regclassAND objoid = i.indexrelid AND c.oid = i.indexrelid AND i.indrelid = 'ctlt_all'::regclass ORDER BY
c.relname,objsubid;
- relname | objsubid | description
-----------------+----------+-------------
- ctlt_all_b_idx | 0 | index b_key
- ctlt_all_pkey | 0 | index pkey
+ relname | objsubid | description
+---------------+----------+-------------
+ ctlt1_b_key_1 | 0 | index b_key
+ ctlt1_pkey_1 | 0 | index pkey
(2 rows)
SELECT s.stxname, objsubid, description FROM pg_description, pg_statistic_ext s WHERE classoid =
'pg_statistic_ext'::regclassAND objoid = s.oid AND s.stxrelid = 'ctlt_all'::regclass ORDER BY s.stxname, objsubid;
@@ -486,9 +486,9 @@ CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "pg_attrdef_pkey" PRIMARY KEY, btree (a)
- "pg_attrdef_b_idx" btree (b)
- "pg_attrdef_expr_idx" btree ((a || b))
+ "ctlt1_pkey_2" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_2" btree (b)
+ "ctlt1_fnidx_2" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
@@ -511,9 +511,9 @@ CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL);
a | text | | not null | | main | | A
b | text | | | | extended | | B
Indexes:
- "ctlt1_pkey" PRIMARY KEY, btree (a)
- "ctlt1_b_idx" btree (b)
- "ctlt1_expr_idx" btree ((a || b))
+ "ctlt1_pkey_1" PRIMARY KEY, btree (a)
+ "ctlt1_b_key_1" btree (b)
+ "ctlt1_fnidx_1" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 7b2198eac6f..c003e5f8288 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -642,13 +642,13 @@ REINDEX TABLE CONCURRENTLY concur_reindex_part;
-- Now add some indexes.
CREATE INDEX concur_reindex_partidx ON concur_reindex_part (id);
REINDEX INDEX concur_reindex_partidx;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX INDEX CONCURRENTLY concur_reindex_partidx;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX TABLE concur_reindex_part;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
REINDEX TABLE CONCURRENTLY concur_reindex_part;
-NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_child_id_idx
+NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.concur_reindex_partidx_1
DROP TABLE concur_reindex_part;
-- Clean up
DROP EVENT TRIGGER regress_reindex_start;
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index bcf1db11d73..f49997e44dc 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -37,16 +37,16 @@ select relname, relkind, relhassubclass, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
left join pg_inherits on (ix.indexrelid = inhrelid)
where relname like 'idxpart%' order by relname;
- relname | relkind | relhassubclass | inhparent
------------------+---------+----------------+----------------
- idxpart | p | t |
- idxpart1 | r | f |
- idxpart1_a_idx | i | f | idxpart_a_idx
- idxpart2 | p | t |
- idxpart21 | r | f |
- idxpart21_a_idx | i | f | idxpart2_a_idx
- idxpart2_a_idx | I | t | idxpart_a_idx
- idxpart_a_idx | I | t |
+ relname | relkind | relhassubclass | inhparent
+-------------------+---------+----------------+-----------------
+ idxpart | p | t |
+ idxpart1 | r | f |
+ idxpart2 | p | t |
+ idxpart21 | r | f |
+ idxpart_a_idx | I | t |
+ idxpart_a_idx_1 | i | f | idxpart_a_idx
+ idxpart_a_idx_2 | I | t | idxpart_a_idx
+ idxpart_a_idx_2_1 | i | f | idxpart_a_idx_2
(8 rows)
drop table idxpart;
@@ -99,28 +99,11 @@ alter table idxpart attach partition idxpart1 for values from (0) to (10);
c | text | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
\d+ idxpart1_a_idx
- Index "public.idxpart1_a_idx"
- Column | Type | Key? | Definition | Storage | Stats target
---------+---------+------+------------+---------+--------------
- a | integer | yes | a | plain |
-Partition of: idxparti
-No partition constraint
-btree, for table "public.idxpart1"
-
\d+ idxpart1_b_c_idx
- Index "public.idxpart1_b_c_idx"
- Column | Type | Key? | Definition | Storage | Stats target
---------+---------+------+------------+----------+--------------
- b | integer | yes | b | plain |
- c | text | yes | c | extended |
-Partition of: idxparti2
-No partition constraint
-btree, for table "public.idxpart1"
-
-- Forbid ALTER TABLE when attaching or detaching an index to a partition.
create index idxpart_c on only idxpart (c);
create index idxpart1_c on idxpart1 (c);
@@ -173,8 +156,7 @@ create table idxpart (a int) partition by range (a);
create index on idxpart (a);
create table idxpart1 partition of idxpart for values from (0) to (10);
drop index idxpart1_a_idx; -- no way
-ERROR: cannot drop index idxpart1_a_idx because index idxpart_a_idx requires it
-HINT: You can drop index idxpart_a_idx instead.
+ERROR: index "idxpart1_a_idx" does not exist
drop index concurrently idxpart_a_idx; -- unsupported
ERROR: cannot drop partitioned index "idxpart_a_idx" concurrently
drop index idxpart_a_idx; -- both indexes go away
@@ -203,8 +185,7 @@ create index on idxpart_temp(a);
create temp table idxpart1_temp partition of idxpart_temp
for values from (0) to (10);
drop index idxpart1_temp_a_idx; -- error
-ERROR: cannot drop index idxpart1_temp_a_idx because index idxpart_temp_a_idx requires it
-HINT: You can drop index idxpart_temp_a_idx instead.
+ERROR: index "idxpart1_temp_a_idx" does not exist
-- non-concurrent drop is enforced here, so it is a valid case.
drop index concurrently idxpart_temp_a_idx;
select relname, relkind from pg_class
@@ -277,8 +258,8 @@ Indexes:
"idxpart1_a_a1_idx" btree (a, a)
"idxpart1_a_idx" hash (a)
"idxpart1_a_idx1" btree (a) WHERE b > 1
- "idxpart1_a_idx2" btree (a)
"idxpart1_expr_idx" btree ((a + 0))
+ "idxpart_a_idx_1" btree (a)
drop table idxpart;
-- If CREATE INDEX ONLY, don't create indexes on partitions; and existing
@@ -302,7 +283,7 @@ create index on idxpart (a);
a | integer | | |
Partition of: idxpart FOR VALUES FROM (0) TO (100)
Indexes:
- "idxpart1_a_idx" btree (a)
+ "idxpart_a_idx_1" btree (a)
\d idxpart2
Partitioned table "public.idxpart2"
@@ -328,10 +309,10 @@ where indexrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
indexrelid | indrelid | inhparent
-----------------+-----------+---------------
- idxpart1_a_idx | idxpart1 | idxpart_a_idx
idxpart22_a_idx | idxpart22 |
idxpart2_a_idx | idxpart2 | idxpart_a_idx
idxpart_a_idx | idxpart |
+ idxpart_a_idx_1 | idxpart1 | idxpart_a_idx
(4 rows)
alter index idxpart2_a_idx attach partition idxpart22_a_idx;
@@ -341,10 +322,10 @@ where indexrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
indexrelid | indrelid | inhparent
-----------------+-----------+----------------
- idxpart1_a_idx | idxpart1 | idxpart_a_idx
idxpart22_a_idx | idxpart22 | idxpart2_a_idx
idxpart2_a_idx | idxpart2 | idxpart_a_idx
idxpart_a_idx | idxpart |
+ idxpart_a_idx_1 | idxpart1 | idxpart_a_idx
(4 rows)
-- attaching idxpart22 is not enough to set idxpart22_a_idx valid ...
@@ -391,21 +372,21 @@ create table idxpart1 (like idxpart including indexes);
c | text | | |
d | boolean | | |
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
left join pg_inherits on (ix.indexrelid = inhrelid)
where relname like 'idxpart%' order by relname;
- relname | relkind | inhparent
-------------------+---------+-----------
- idxpart | p |
- idxpart1 | r |
- idxpart1_a_idx | i |
- idxpart1_b_c_idx | i |
- idxparti | I |
- idxparti2 | I |
+ relname | relkind | inhparent
+-------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxparti | I |
+ idxparti2 | I |
+ idxparti2_1 | i |
+ idxparti_1 | i |
(6 rows)
alter table idxpart attach partition idxpart1 for values from (0) to (10);
@@ -419,21 +400,21 @@ alter table idxpart attach partition idxpart1 for values from (0) to (10);
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
left join pg_inherits on (ix.indexrelid = inhrelid)
where relname like 'idxpart%' order by relname;
- relname | relkind | inhparent
-------------------+---------+-----------
- idxpart | p |
- idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
- idxparti | I |
- idxparti2 | I |
+ relname | relkind | inhparent
+-------------+---------+-----------
+ idxpart | p |
+ idxpart1 | r |
+ idxparti | I |
+ idxparti2 | I |
+ idxparti2_1 | i | idxparti2
+ idxparti_1 | i | idxparti
(6 rows)
-- While here, also check matching when creating an index after the fact.
@@ -448,9 +429,9 @@ create index on idxpart1 ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
@@ -460,11 +441,11 @@ select relname, relkind, inhparent::regclass
-------------------+---------+-----------
idxpart | p |
idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
idxpart1_expr_idx | i |
idxparti | I |
idxparti2 | I |
+ idxparti2_1 | i | idxparti2
+ idxparti_1 | i | idxparti
(7 rows)
create index idxparti3 on idxpart ((a+b)) where d = true;
@@ -478,9 +459,9 @@ create index idxparti3 on idxpart ((a+b)) where d = true;
d | boolean | | |
Partition of: idxpart FOR VALUES FROM (0) TO (10)
Indexes:
- "idxpart1_a_idx" btree (a)
- "idxpart1_b_c_idx" btree (b, c)
"idxpart1_expr_idx" btree ((a + b)) WHERE d = true
+ "idxparti2_1" btree (b, c)
+ "idxparti_1" btree (a)
select relname, relkind, inhparent::regclass
from pg_class left join pg_index ix on (indexrelid = oid)
@@ -490,12 +471,12 @@ select relname, relkind, inhparent::regclass
-------------------+---------+-----------
idxpart | p |
idxpart1 | r |
- idxpart1_a_idx | i | idxparti
- idxpart1_b_c_idx | i | idxparti2
idxpart1_expr_idx | i | idxparti3
idxparti | I |
idxparti2 | I |
+ idxparti2_1 | i | idxparti2
idxparti3 | I |
+ idxparti_1 | i | idxparti
(8 rows)
drop table idxpart;
@@ -551,16 +532,16 @@ alter table idxpart attach partition idxpart1 for values from (0000) to (1000);
alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
- idxpart_a_idx | I
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(8 rows)
-- a) after detaching partitions, the indexes can be dropped independently
@@ -569,16 +550,20 @@ alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart1_a_idx;
drop index idxpart2_a_idx;
+ERROR: index "idxpart2_a_idx" does not exist
drop index idxpart3_a_idx;
+ERROR: index "idxpart3_a_idx" does not exist
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart2 | r
- idxpart3 | r
- idxpart_a_idx | I
-(5 rows)
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
+(7 rows)
drop table idxpart, idxpart1, idxpart2, idxpart3;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
@@ -596,16 +581,16 @@ alter table idxpart attach partition idxpart2 for values from (1000) to (2000);
create table idxpart3 partition of idxpart for values from (2000) to (3000);
-- b) after detaching, dropping the index on parent does not remove the others
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
- idxpart_a_idx | I
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx | I
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(8 rows)
alter table idxpart detach partition idxpart1;
@@ -613,15 +598,15 @@ alter table idxpart detach partition idxpart2;
alter table idxpart detach partition idxpart3;
drop index idxpart_a_idx;
select relname, relkind from pg_class where relname like 'idxpart%' order by relname;
- relname | relkind
-----------------+---------
- idxpart | p
- idxpart1 | r
- idxpart1_a_idx | i
- idxpart2 | r
- idxpart2_a_idx | i
- idxpart3 | r
- idxpart3_a_idx | i
+ relname | relkind
+-----------------+---------
+ idxpart | p
+ idxpart1 | r
+ idxpart1_a_idx | i
+ idxpart2 | r
+ idxpart3 | r
+ idxpart_a_idx_1 | i
+ idxpart_a_idx_2 | i
(7 rows)
drop table idxpart, idxpart1, idxpart2, idxpart3;
@@ -643,7 +628,7 @@ alter table idxpart detach partition idxpart2;
b | integer | | |
c | integer | | |
Indexes:
- "idxpart2_c_idx" btree (c)
+ "idxpart_c_idx_2" btree (c)
alter table idxpart2 drop column c;
\d idxpart2
@@ -667,11 +652,11 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
from pg_class join pg_inherits on inhrelid = oid,
lateral pg_get_indexdef(pg_class.oid)
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
- child | parent | childdef
--------------------+------------------+---------------------------------------------------------------------------
- idxpart1_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((a + b)))
- idxpart2_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((a + b)))
- idxpart3_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart3_expr_idx ON public.idxpart3 USING btree (((a + b)))
+ child | parent | childdef
+--------------------+------------------+----------------------------------------------------------------------------
+ idxpart1_expr_idx | idxpart_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((a + b)))
+ idxpart_expr_idx_1 | idxpart_expr_idx | CREATE INDEX idxpart_expr_idx_1 ON public.idxpart2 USING btree (((a + b)))
+ idxpart_expr_idx_2 | idxpart_expr_idx | CREATE INDEX idxpart_expr_idx_2 ON public.idxpart3 USING btree (((a + b)))
(3 rows)
drop table idxpart;
@@ -693,13 +678,13 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
child | parent | childdef
-----------------+---------------+--------------------------------------------------------------------------------
- idxpart1_a_idx | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a COLLATE "C")
idxpart2_a_idx | | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a COLLATE "POSIX")
idxpart2_a_idx1 | | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a)
idxpart2_a_idx2 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx2 ON public.idxpart2 USING btree (a COLLATE "C")
- idxpart3_a_idx | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a COLLATE "C")
- idxpart4_a_idx | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a COLLATE "C")
idxpart_a_idx | | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a COLLATE "C")
+ idxpart_a_idx_1 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a COLLATE "C")
+ idxpart_a_idx_2 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_2 ON public.idxpart3 USING btree (a COLLATE "C")
+ idxpart_a_idx_3 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_3 ON public.idxpart4 USING btree (a COLLATE "C")
(7 rows)
drop table idxpart;
@@ -720,12 +705,12 @@ select relname as child, inhparent::regclass as parent, pg_get_indexdef as child
where relkind in ('i', 'I') and relname like 'idxpart%' order by relname;
child | parent | childdef
-----------------+---------------+------------------------------------------------------------------------------------
- idxpart1_a_idx | idxpart_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a text_pattern_ops)
idxpart2_a_idx | | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
- idxpart2_a_idx1 | idxpart_a_idx | CREATE INDEX idxpart2_a_idx1 ON public.idxpart2 USING btree (a text_pattern_ops)
- idxpart3_a_idx | idxpart_a_idx | CREATE INDEX idxpart3_a_idx ON public.idxpart3 USING btree (a text_pattern_ops)
- idxpart4_a_idx | idxpart_a_idx | CREATE INDEX idxpart4_a_idx ON public.idxpart4 USING btree (a text_pattern_ops)
idxpart_a_idx | | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a text_pattern_ops)
+ idxpart_a_idx_1 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a text_pattern_ops)
+ idxpart_a_idx_2 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_2 ON public.idxpart2 USING btree (a text_pattern_ops)
+ idxpart_a_idx_3 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_3 ON public.idxpart3 USING btree (a text_pattern_ops)
+ idxpart_a_idx_4 | idxpart_a_idx | CREATE INDEX idxpart_a_idx_4 ON public.idxpart4 USING btree (a text_pattern_ops)
(6 rows)
drop index idxpart_a_idx;
@@ -792,12 +777,12 @@ select c.relname, pg_get_indexdef(indexrelid)
order by indexrelid::regclass::text collate "C";
relname | pg_get_indexdef
------------------+---------------------------------------------------------------------
- idxpart1_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a)
- idxpart1_c_b_idx | CREATE INDEX idxpart1_c_b_idx ON public.idxpart1 USING btree (c, b)
idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a)
idxpart2_c_b_idx | CREATE INDEX idxpart2_c_b_idx ON public.idxpart2 USING btree (c, b)
idxparti | CREATE INDEX idxparti ON ONLY public.idxpart USING btree (a)
idxparti2 | CREATE INDEX idxparti2 ON ONLY public.idxpart USING btree (c, b)
+ idxparti2_1 | CREATE INDEX idxparti2_1 ON public.idxpart1 USING btree (c, b)
+ idxparti_1 | CREATE INDEX idxparti_1 ON public.idxpart1 USING btree (a)
(6 rows)
drop table idxpart;
@@ -817,14 +802,14 @@ select c.relname, pg_get_indexdef(indexrelid)
from pg_class c join pg_index i on c.oid = i.indexrelid
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- relname | pg_get_indexdef
--------------------+------------------------------------------------------------------------------
- idxpart1_abs_idx | CREATE INDEX idxpart1_abs_idx ON public.idxpart1 USING btree (abs(b))
- idxpart1_expr_idx | CREATE INDEX idxpart1_expr_idx ON public.idxpart1 USING btree (((b + 1)))
- idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USING btree (abs(b))
- idxpart2_expr_idx | CREATE INDEX idxpart2_expr_idx ON public.idxpart2 USING btree (((b + 1)))
- idxpart_abs_idx | CREATE INDEX idxpart_abs_idx ON ONLY public.idxpart USING btree (abs(b))
- idxpart_expr_idx | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpart USING btree (((b + 1)))
+ relname | pg_get_indexdef
+--------------------+------------------------------------------------------------------------------
+ idxpart2_abs_idx | CREATE INDEX idxpart2_abs_idx ON public.idxpart2 USING btree (abs(b))
+ idxpart_abs_idx | CREATE INDEX idxpart_abs_idx ON ONLY public.idxpart USING btree (abs(b))
+ idxpart_abs_idx_1 | CREATE INDEX idxpart_abs_idx_1 ON public.idxpart1 USING btree (abs(b))
+ idxpart_expr_idx | CREATE INDEX idxpart_expr_idx ON ONLY public.idxpart USING btree (((b + 1)))
+ idxpart_expr_idx_1 | CREATE INDEX idxpart_expr_idx_1 ON public.idxpart2 USING btree (((b + 1)))
+ idxpart_expr_idx_2 | CREATE INDEX idxpart_expr_idx_2 ON public.idxpart1 USING btree (((b + 1)))
(6 rows)
drop table idxpart;
@@ -843,11 +828,11 @@ select c.relname, pg_get_indexdef(indexrelid)
from pg_class c join pg_index i on c.oid = i.indexrelid
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- relname | pg_get_indexdef
-----------------+------------------------------------------------------------------------------------
- idxpart1_a_idx | CREATE INDEX idxpart1_a_idx ON public.idxpart1 USING btree (a) WHERE (b > 1000)
- idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a) WHERE (b > 1000)
- idxpart_a_idx | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a) WHERE (b > 1000)
+ relname | pg_get_indexdef
+-----------------+------------------------------------------------------------------------------------
+ idxpart2_a_idx | CREATE INDEX idxpart2_a_idx ON public.idxpart2 USING btree (a) WHERE (b > 1000)
+ idxpart_a_idx | CREATE INDEX idxpart_a_idx ON ONLY public.idxpart USING btree (a) WHERE (b > 1000)
+ idxpart_a_idx_1 | CREATE INDEX idxpart_a_idx_1 ON public.idxpart1 USING btree (a) WHERE (b > 1000)
(3 rows)
drop table idxpart;
@@ -1047,7 +1032,7 @@ create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000
c | text | | |
Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000)
Indexes:
- "idxpart1_pkey" PRIMARY KEY, btree (a, b)
+ "idxpart_pkey_1" PRIMARY KEY, btree (a, b)
drop table idxpart;
-- use ALTER TABLE to add a unique constraint
@@ -1117,26 +1102,26 @@ alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 3
select conname, contype, conrelid::regclass, conindid::regclass, conkey
from pg_constraint where conrelid::regclass::text like 'idxpart%'
order by conrelid::regclass::text, conname;
- conname | contype | conrelid | conindid | conkey
----------------------+---------+-----------+----------------+--------
- idxpart_a_not_null | n | idxpart | - | {1}
- idxpart_b_not_null | n | idxpart | - | {2}
- idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
- idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2}
- idxpart_a_not_null | n | idxpart1 | - | {1}
- idxpart_b_not_null | n | idxpart1 | - | {2}
- idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
- idxpart_a_not_null | n | idxpart2 | - | {1}
- idxpart_b_not_null | n | idxpart2 | - | {2}
- idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
- idxpart_a_not_null | n | idxpart21 | - | {1}
- idxpart_b_not_null | n | idxpart21 | - | {2}
- idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}
- idxpart_a_not_null | n | idxpart22 | - | {1}
- idxpart_b_not_null | n | idxpart22 | - | {2}
- idxpart3_a_not_null | n | idxpart3 | - | {2}
- idxpart3_b_not_null | n | idxpart3 | - | {1}
- idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}
+ conname | contype | conrelid | conindid | conkey
+---------------------+---------+-----------+------------------+--------
+ idxpart_a_not_null | n | idxpart | - | {1}
+ idxpart_b_not_null | n | idxpart | - | {2}
+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
+ idxpart_a_not_null | n | idxpart1 | - | {1}
+ idxpart_b_not_null | n | idxpart1 | - | {2}
+ idxpart_pkey_1 | p | idxpart1 | idxpart_pkey_1 | {1,2}
+ idxpart_a_not_null | n | idxpart2 | - | {1}
+ idxpart_b_not_null | n | idxpart2 | - | {2}
+ idxpart_pkey_2 | p | idxpart2 | idxpart_pkey_2 | {1,2}
+ idxpart_a_not_null | n | idxpart21 | - | {1}
+ idxpart_b_not_null | n | idxpart21 | - | {2}
+ idxpart_pkey_2_1 | p | idxpart21 | idxpart_pkey_2_1 | {1,2}
+ idxpart_a_not_null | n | idxpart22 | - | {1}
+ idxpart_b_not_null | n | idxpart22 | - | {2}
+ idxpart_pkey_2_2 | p | idxpart22 | idxpart_pkey_2_2 | {1,2}
+ idxpart3_a_not_null | n | idxpart3 | - | {2}
+ idxpart3_b_not_null | n | idxpart3 | - | {1}
+ idxpart_pkey_3 | p | idxpart3 | idxpart_pkey_3 | {2,1}
(18 rows)
drop table idxpart;
@@ -1163,17 +1148,17 @@ create table idxpart21 partition of idxpart2 for values from (0) to (1000);
select conname, contype, conrelid::regclass, conindid::regclass, conkey
from pg_constraint where conrelid::regclass::text like 'idxpart%'
order by conrelid::regclass::text, conname;
- conname | contype | conrelid | conindid | conkey
---------------------+---------+-----------+----------------+--------
- idxpart_a_not_null | n | idxpart | - | {1}
- idxpart_b_not_null | n | idxpart | - | {2}
- idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
- idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
- idxpart_a_not_null | n | idxpart2 | - | {1}
- idxpart_b_not_null | n | idxpart2 | - | {2}
- idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
- idxpart_a_not_null | n | idxpart21 | - | {1}
- idxpart_b_not_null | n | idxpart21 | - | {2}
+ conname | contype | conrelid | conindid | conkey
+--------------------+---------+-----------+------------------+--------
+ idxpart_a_not_null | n | idxpart | - | {1}
+ idxpart_b_not_null | n | idxpart | - | {2}
+ idxpart_pkey | p | idxpart | idxpart_pkey | {1,2}
+ idxpart_a_not_null | n | idxpart2 | - | {1}
+ idxpart_b_not_null | n | idxpart2 | - | {2}
+ idxpart_pkey_1 | p | idxpart2 | idxpart_pkey_1 | {1,2}
+ idxpart_a_not_null | n | idxpart21 | - | {1}
+ idxpart_b_not_null | n | idxpart21 | - | {2}
+ idxpart_pkey_1_1 | p | idxpart21 | idxpart_pkey_1_1 | {1,2}
(9 rows)
drop table idxpart;
@@ -1192,23 +1177,22 @@ select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid
left join pg_constraint con on (idx.indexrelid = con.conindid)
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
-----------+---------------+--------------+------------+---------------+------------+-------------+--------------+--------------
- idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t
- idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | f | t
- idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
+----------+----------------+--------------+------------+----------------+------------+-------------+--------------+--------------
+ idxpart0 | idxpart0_pkey | idxpart_pkey | t | idxpart0_pkey | f | 1 | t | t
+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ idxpart1 | idxpart_pkey_1 | idxpart_pkey | t | idxpart_pkey_1 | f | 1 | f | t
(3 rows)
drop index idxpart0_pkey; -- fail
ERROR: cannot drop index idxpart0_pkey because index idxpart_pkey requires it
HINT: You can drop index idxpart_pkey instead.
drop index idxpart1_pkey; -- fail
-ERROR: cannot drop index idxpart1_pkey because index idxpart_pkey requires it
-HINT: You can drop index idxpart_pkey instead.
+ERROR: index "idxpart1_pkey" does not exist
alter table idxpart0 drop constraint idxpart0_pkey; -- fail
ERROR: cannot drop inherited constraint "idxpart0_pkey" of relation "idxpart0"
alter table idxpart1 drop constraint idxpart1_pkey; -- fail
-ERROR: cannot drop inherited constraint "idxpart1_pkey" of relation "idxpart1"
+ERROR: constraint "idxpart1_pkey" of relation "idxpart1" does not exist
alter table idxpart drop constraint idxpart_pkey; -- ok
select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid,
conname, conislocal, coninhcount, connoinherit, convalidated
@@ -1295,11 +1279,11 @@ select indrelid::regclass, indexrelid::regclass, inhparent::regclass, indisvalid
left join pg_constraint con on (idx.indexrelid = con.conindid)
where indrelid::regclass::text like 'idxpart%'
order by indexrelid::regclass::text collate "C";
- indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
-----------+----------------+--------------+------------+---------------+------------+-------------+--------------+--------------
- idxpart1 | idxpart1_a_idx | | t | | | | |
- idxpart1 | idxpart1_pkey | idxpart_pkey | t | idxpart1_pkey | f | 1 | f | t
- idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ indrelid | indexrelid | inhparent | indisvalid | conname | conislocal | coninhcount | connoinherit |
convalidated
+----------+----------------+--------------+------------+----------------+------------+-------------+--------------+--------------
+ idxpart1 | idxpart1_a_idx | | t | | | | |
+ idxpart | idxpart_pkey | | t | idxpart_pkey | t | 0 | t | t
+ idxpart1 | idxpart_pkey_1 | idxpart_pkey | t | idxpart_pkey_1 | f | 1 | f | t
(3 rows)
drop table idxpart;
@@ -1323,15 +1307,15 @@ create unique index on idxpart (a);
alter table idxpart attach partition idxpart2 for values from (100000) to (1000000);
insert into idxpart values (0, 'zero'), (42, 'life'), (2^16, 'sixteen');
insert into idxpart select 2^g, format('two to power of %s', g) from generate_series(15, 17) g;
-ERROR: duplicate key value violates unique constraint "idxpart1_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_1"
DETAIL: Key (a)=(65536) already exists.
insert into idxpart values (16, 'sixteen');
insert into idxpart (b, a) values ('one', 142857), ('two', 285714);
insert into idxpart select a * 2, b || b from idxpart where a between 2^16 and 2^19;
-ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_2"
DETAIL: Key (a)=(285714) already exists.
insert into idxpart values (572814, 'five');
-ERROR: duplicate key value violates unique constraint "idxpart2_a_idx"
+ERROR: duplicate key value violates unique constraint "idxpart_a_idx_2"
DETAIL: Key (a)=(572814) already exists.
insert into idxpart values (857142, 'six');
select tableoid::regclass, * from idxpart order by a;
@@ -1358,18 +1342,18 @@ explain (costs off) select * from idxpart where b = 'abcd';
-------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (b = 'abcd'::text)
- -> Bitmap Index Scan on idxpart1_b_idx
+ -> Bitmap Index Scan on idxpart_brin_1
Index Cond: (b = 'abcd'::text)
(4 rows)
drop index idxpart_brin;
create index idxpart_spgist on idxpart using spgist(b);
explain (costs off) select * from idxpart where b = 'abcd';
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (b = 'abcd'::text)
- -> Bitmap Index Scan on idxpart1_b_idx
+ -> Bitmap Index Scan on idxpart_spgist_1
Index Cond: (b = 'abcd'::text)
(4 rows)
@@ -1380,7 +1364,7 @@ explain (costs off) select * from idxpart where c @> array[42];
----------------------------------------------
Bitmap Heap Scan on idxpart1 idxpart
Recheck Cond: (c @> '{42}'::integer[])
- -> Bitmap Index Scan on idxpart1_c_idx
+ -> Bitmap Index Scan on idxpart_gin_1
Index Cond: (c @> '{42}'::integer[])
(4 rows)
@@ -1430,14 +1414,14 @@ create table covidxpart1 partition of covidxpart for values in (1);
create table covidxpart2 partition of covidxpart for values in (2);
insert into covidxpart values (1, 1);
insert into covidxpart values (1, 1);
-ERROR: duplicate key value violates unique constraint "covidxpart1_a_b_idx"
+ERROR: duplicate key value violates unique constraint "covidxpart_a_b_idx_1"
DETAIL: Key (a)=(1) already exists.
create table covidxpart3 (b int, c int, a int);
alter table covidxpart3 drop c;
alter table covidxpart attach partition covidxpart3 for values in (3);
insert into covidxpart values (3, 1);
insert into covidxpart values (3, 1);
-ERROR: duplicate key value violates unique constraint "covidxpart3_a_b_idx"
+ERROR: duplicate key value violates unique constraint "covidxpart_a_b_idx_3"
DETAIL: Key (a)=(3) already exists.
create table covidxpart4 (b int, a int);
create unique index on covidxpart4 (a) include (b);
@@ -1454,16 +1438,18 @@ DETAIL: UNIQUE constraint on table "covidxpart" lacks column "a" which is part
create table parted_pk_detach_test (a int primary key) partition by list (a);
create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1);
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey; -- should fail
-ERROR: cannot drop inherited constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1"
+ERROR: constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1" does not exist
alter table parted_pk_detach_test detach partition parted_pk_detach_test1;
alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;
+ERROR: constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1" does not exist
drop table parted_pk_detach_test, parted_pk_detach_test1;
create table parted_uniq_detach_test (a int unique) partition by list (a);
create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1);
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key; -- should fail
-ERROR: cannot drop inherited constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1"
+ERROR: constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1" does not exist
alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
+ERROR: constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1" does not exist
drop table parted_uniq_detach_test, parted_uniq_detach_test1;
-- check that dropping a column takes with it any partitioned indexes
-- depending on it.
@@ -1500,7 +1486,7 @@ Number of partitions: 2 (Use \d+ to list them.)
Partition of: parted_index_col_drop FOR VALUES IN (1)
Partition key: LIST (a)
Indexes:
- "parted_index_col_drop1_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_1" btree (b)
Number of partitions: 1 (Use \d+ to list them.)
\d parted_index_col_drop2
@@ -1512,7 +1498,7 @@ Number of partitions: 1 (Use \d+ to list them.)
Partition of: parted_index_col_drop FOR VALUES IN (2)
Partition key: LIST (a)
Indexes:
- "parted_index_col_drop2_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_2" btree (b)
Number of partitions: 0
\d parted_index_col_drop11
@@ -1523,7 +1509,7 @@ Number of partitions: 0
b | integer | | |
Partition of: parted_index_col_drop1 FOR VALUES IN (1)
Indexes:
- "parted_index_col_drop11_b_idx" btree (b)
+ "parted_index_col_drop_b_idx_1_1" btree (b)
drop table parted_index_col_drop;
-- Check that invalid indexes are not selected when attaching a partition.
@@ -1546,13 +1532,13 @@ select indexrelid::regclass, indisvalid,
pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'parted_inval%'
order by indexrelid::regclass::text collate "C";
- indexrelid | indisvalid | indrelid | inhparent
-----------------------------+------------+----------------------+--------------------------
- parted_inval_idx | t | parted_inval_tab |
- parted_inval_ixd_1 | f | parted_inval_tab_1 |
- parted_inval_tab_1_1_a_idx | t | parted_inval_tab_1_1 | parted_inval_tab_1_a_idx
- parted_inval_tab_1_2_a_idx | t | parted_inval_tab_1_2 | parted_inval_tab_1_a_idx
- parted_inval_tab_1_a_idx | t | parted_inval_tab_1 | parted_inval_idx
+ indexrelid | indisvalid | indrelid | inhparent
+----------------------+------------+----------------------+--------------------
+ parted_inval_idx | t | parted_inval_tab |
+ parted_inval_idx_1 | t | parted_inval_tab_1 | parted_inval_idx
+ parted_inval_idx_1_1 | t | parted_inval_tab_1_1 | parted_inval_idx_1
+ parted_inval_idx_1_2 | t | parted_inval_tab_1_2 | parted_inval_idx_1
+ parted_inval_ixd_1 | f | parted_inval_tab_1 |
(5 rows)
drop table parted_inval_tab;
@@ -1581,13 +1567,13 @@ select indexrelid::regclass, indisvalid,
pg_inherits inh on (idx.indexrelid = inh.inhrelid)
where indexrelid::regclass::text like 'parted_isvalid%'
order by indexrelid::regclass::text collate "C";
- indexrelid | indisvalid | indrelid | inhparent
---------------------------------+------------+-----------------------+-------------------------------
- parted_isvalid_idx | f | parted_isvalid_tab |
- parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_tab_1_expr_idx
- parted_isvalid_tab_12_expr_idx | t | parted_isvalid_tab_12 | parted_isvalid_tab_1_expr_idx
- parted_isvalid_tab_1_expr_idx | f | parted_isvalid_tab_1 | parted_isvalid_idx
- parted_isvalid_tab_2_expr_idx | t | parted_isvalid_tab_2 | parted_isvalid_idx
+ indexrelid | indisvalid | indrelid | inhparent
+------------------------+------------+-----------------------+----------------------
+ parted_isvalid_idx | f | parted_isvalid_tab |
+ parted_isvalid_idx_1 | f | parted_isvalid_tab_1 | parted_isvalid_idx
+ parted_isvalid_idx_11 | f | parted_isvalid_tab_11 | parted_isvalid_idx_1
+ parted_isvalid_idx_1_1 | t | parted_isvalid_tab_12 | parted_isvalid_idx_1
+ parted_isvalid_idx_2 | t | parted_isvalid_tab_2 | parted_isvalid_idx
(5 rows)
drop table parted_isvalid_tab;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f9b0c415cfd..b6484683837 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -3272,45 +3272,45 @@ drop table parted_minmax;
create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c);
-- MergeAppend must be used when a default partition exists
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Merge Append
Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
- -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7
+ -> Index Scan using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6 on mcrparted5 mcrparted_6
+ -> Index Scan using mcrparted_a_abs_c_idx_7 on mcrparted_def mcrparted_7
(9 rows)
drop table mcrparted_def;
-- Append is used for a RANGE partitioned table with no default
-- and no subpartitions
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
+ -> Index Scan using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6 on mcrparted5 mcrparted_6
(7 rows)
-- Append is used with subpaths in reverse order with backwards index scans
explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------
Append
- -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
- -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_6 on mcrparted5 mcrparted_6
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan Backward using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
(7 rows)
-- check that Append plan is used containing a MergeAppend for sub-partitions
@@ -3320,18 +3320,18 @@ create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (
create table mcrparted5a partition of mcrparted5 for values in(20);
create table mcrparted5_def partition of mcrparted5 default;
explain (costs off) select * from mcrparted order by a, abs(b), c;
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
-> Merge Append
Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c
- -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7
- -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8
+ -> Index Scan using mcrparted_a_abs_c_idx_6_1 on mcrparted5a mcrparted_7
+ -> Index Scan using mcrparted_a_abs_c_idx_6_2 on mcrparted5_def mcrparted_8
(10 rows)
drop table mcrparted5_def;
@@ -3339,30 +3339,30 @@ drop table mcrparted5_def;
-- into the main Append when the sub-partition is unordered but contains
-- just a single sub-partition.
explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
- -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
- -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6
+ -> Index Scan using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_5 on mcrparted4 mcrparted_5
+ -> Index Scan using mcrparted_a_abs_c_idx_6_1 on mcrparted5a mcrparted_6
(7 rows)
-- check that Append is used when the sub-partitioned tables are pruned
-- during planning.
explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Append
- -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
+ -> Index Scan using mcrparted_a_abs_c_idx_1 on mcrparted0 mcrparted_1
Index Cond: (a < 20)
- -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
+ -> Index Scan using mcrparted_a_abs_c_idx_2 on mcrparted1 mcrparted_2
Index Cond: (a < 20)
- -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
+ -> Index Scan using mcrparted_a_abs_c_idx_3 on mcrparted2 mcrparted_3
Index Cond: (a < 20)
- -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
+ -> Index Scan using mcrparted_a_abs_c_idx_4 on mcrparted3 mcrparted_4
Index Cond: (a < 20)
(9 rows)
@@ -3374,11 +3374,11 @@ create table mclparted2 partition of mclparted for values in(2);
create index on mclparted (a);
-- Ensure an Append is used for a list partition with an order by.
explain (costs off) select * from mclparted order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
(3 rows)
-- Ensure a MergeAppend is used when a partition exists with interleaved
@@ -3386,24 +3386,24 @@ explain (costs off) select * from mclparted order by a;
create table mclparted3_5 partition of mclparted for values in(3,5);
create table mclparted4 partition of mclparted for values in(4);
explain (costs off) select * from mclparted order by a;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
- -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_3
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_3 on mclparted3_5 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
(6 rows)
explain (costs off) select * from mclparted where a in(3,4,5) order by a;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_3 on mclparted3_5 mclparted_1
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_2
Index Cond: (a = ANY ('{3,4,5}'::integer[]))
(6 rows)
@@ -3412,28 +3412,28 @@ create table mclparted_null partition of mclparted for values in(null);
create table mclparted_def partition of mclparted default;
-- Append can be used providing we don't scan the interleaved partition
explain (costs off) select * from mclparted where a in(1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
(7 rows)
explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclparted_4
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_null mclparted_4
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
(9 rows)
@@ -3442,61 +3442,61 @@ drop table mclparted_null;
create table mclparted_0_null partition of mclparted for values in(0,null);
-- Ensure MergeAppend is used since 0 and NULLs are in the same partition.
explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclparted_1
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_2
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_3
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
(10 rows)
explain (costs off) select * from mclparted where a in(0,1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
+ -> Index Only Scan using mclparted_a_idx_5 on mclparted_0_null mclparted_1
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_2
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_3
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_4
Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
(10 rows)
-- Ensure Append is used when the null partition is pruned
explain (costs off) select * from mclparted where a in(1,2,4) order by a;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Append
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4}'::integer[]))
(7 rows)
-- Ensure MergeAppend is used when the default partition is not pruned
explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Merge Append
Sort Key: mclparted.a
- -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
+ -> Index Only Scan using mclparted_a_idx_1 on mclparted1 mclparted_1
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
+ -> Index Only Scan using mclparted_a_idx_2 on mclparted2 mclparted_2
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
+ -> Index Only Scan using mclparted_a_idx_4 on mclparted4 mclparted_3
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
- -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4
+ -> Index Only Scan using mclparted_a_idx_6 on mclparted_def mclparted_4
Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
(10 rows)
@@ -3548,11 +3548,11 @@ create table bool_lp_true partition of bool_lp for values in(true);
create table bool_lp_false partition of bool_lp for values in(false);
create index on bool_lp (b);
explain (costs off) select * from bool_lp order by b;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_lp_false_b_idx on bool_lp_false bool_lp_1
- -> Index Only Scan using bool_lp_true_b_idx on bool_lp_true bool_lp_2
+ -> Index Only Scan using bool_lp_b_idx_1 on bool_lp_false bool_lp_1
+ -> Index Only Scan using bool_lp_b_idx_2 on bool_lp_true bool_lp_2
(3 rows)
drop table bool_lp;
@@ -3564,42 +3564,42 @@ create table bool_rp_false_2k partition of bool_rp for values from (false,1000)
create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000);
create index on bool_rp (b,a);
explain (costs off) select * from bool_rp where b = true order by b,a;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
- -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
(5 rows)
explain (costs off) select * from bool_rp where b = false order by b,a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_1 on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
- -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_2 on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
(5 rows)
explain (costs off) select * from bool_rp where b = true order by a;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_3 on bool_rp_true_1k bool_rp_1
Index Cond: (b = true)
- -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_4 on bool_rp_true_2k bool_rp_2
Index Cond: (b = true)
(5 rows)
explain (costs off) select * from bool_rp where b = false order by a;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Append
- -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
+ -> Index Only Scan using bool_rp_b_a_idx_1 on bool_rp_false_1k bool_rp_1
Index Cond: (b = false)
- -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
+ -> Index Only Scan using bool_rp_b_a_idx_2 on bool_rp_false_2k bool_rp_2
Index Cond: (b = false)
(5 rows)
@@ -3611,19 +3611,19 @@ create table range_parted1 partition of range_parted for values from (0,0) to (1
create table range_parted2 partition of range_parted for values from (10,10) to (20,20);
create index on range_parted (a,b,c);
explain (costs off) select * from range_parted order by a,b,c;
- QUERY PLAN
--------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Append
- -> Index Only Scan using range_parted1_a_b_c_idx on range_parted1 range_parted_1
- -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 range_parted_2
+ -> Index Only Scan using range_parted_a_b_c_idx_1 on range_parted1 range_parted_1
+ -> Index Only Scan using range_parted_a_b_c_idx_2 on range_parted2 range_parted_2
(3 rows)
explain (costs off) select * from range_parted order by a desc,b desc,c desc;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Append
- -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_parted2 range_parted_2
- -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1 range_parted_1
+ -> Index Only Scan Backward using range_parted_a_b_c_idx_2 on range_parted2 range_parted_2
+ -> Index Only Scan Backward using range_parted_a_b_c_idx_1 on range_parted1 range_parted_1
(3 rows)
drop table range_parted;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index d5368186caa..9fd5285b628 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2530,9 +2530,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
Filter: (a = prtx1_1.a)
-> BitmapAnd
- -> Bitmap Index Scan on prtx2_1_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_1
Index Cond: (b = prtx1_1.b)
- -> Bitmap Index Scan on prtx2_1_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_1
Index Cond: (c = 123)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
@@ -2541,9 +2541,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
Filter: (a = prtx1_2.a)
-> BitmapAnd
- -> Bitmap Index Scan on prtx2_2_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_2
Index Cond: (b = prtx1_2.b)
- -> Bitmap Index Scan on prtx2_2_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_2
Index Cond: (c = 123)
(23 rows)
@@ -2571,9 +2571,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
Filter: (a = prtx1_1.a)
-> BitmapOr
- -> Bitmap Index Scan on prtx2_1_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_1
Index Cond: (b = (prtx1_1.b + 1))
- -> Bitmap Index Scan on prtx2_1_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_1
Index Cond: (c = 99)
-> Nested Loop Anti Join
-> Seq Scan on prtx1_2
@@ -2582,9 +2582,9 @@ where not exists (select 1 from prtx2
Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
Filter: (a = prtx1_2.a)
-> BitmapOr
- -> Bitmap Index Scan on prtx2_2_b_idx
+ -> Bitmap Index Scan on prtx2_b_idx_2
Index Cond: (b = (prtx1_2.b + 1))
- -> Bitmap Index Scan on prtx2_2_c_idx
+ -> Bitmap Index Scan on prtx2_c_idx_2
Index Cond: (c = 99)
(23 rows)
@@ -3075,8 +3075,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_a
-- 3-way join where not every pair of relations can do partitioned join
EXPLAIN (COSTS OFF)
SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) INNER JOIN prt1_adv
t3ON (t1.b = t3.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a, t3.a;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------
Sort
Sort Key: t1.b, t2.a
-> Append
@@ -3084,9 +3084,9 @@ SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2
-> Nested Loop
-> Seq Scan on prt2_adv_p1 t1_1
Filter: (a = 0)
- -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t3_1
+ -> Index Scan using prt1_adv_a_idx_1 on prt1_adv_p1 t3_1
Index Cond: (a = t1_1.b)
- -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t2_1
+ -> Index Scan using prt1_adv_a_idx_1 on prt1_adv_p1 t2_1
Index Cond: (a = t1_1.b)
-> Hash Right Join
Hash Cond: (t2_2.a = t1_2.b)
@@ -5228,53 +5228,53 @@ SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = on;
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id
-> Merge Left Join
Merge Cond: (x_1.id = y_1.id)
- -> Index Only Scan using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
-> Merge Left Join
Merge Cond: (x_2.id = y_2.id)
- -> Index Only Scan using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
(11 rows)
EXPLAIN (COSTS OFF)
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id DESC
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan Backward using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
Index Cond: (id = x_1.id)
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan Backward using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
Index Cond: (id = x_2.id)
(11 rows)
EXPLAIN (COSTS OFF) -- Should use NestLoop with parameterised inner scan
SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id)
ORDER BY x.id DESC LIMIT 2;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------
Limit
-> Merge Append
Sort Key: x.id DESC
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
- -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
+ -> Index Only Scan Backward using fract_t_pkey_1 on fract_t0 x_1
+ -> Index Only Scan using fract_t_pkey_1 on fract_t0 y_1
Index Cond: (id = x_1.id)
-> Nested Loop Left Join
- -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
- -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
+ -> Index Only Scan Backward using fract_t_pkey_2 on fract_t1 x_2
+ -> Index Only Scan using fract_t_pkey_2 on fract_t1 y_2
Index Cond: (id = x_2.id)
(11 rows)
@@ -5307,8 +5307,8 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
-- Increase number of tuples requested and an IndexScan will be chosen
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Limit
-> Append
-> Nested Loop
@@ -5316,21 +5316,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
- -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ -> Index Scan using pht1_c_idx_1 on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
-> Seq Scan on pht1_p2 p1_2
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
- -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ -> Index Scan using pht1_c_idx_2 on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
-> Seq Scan on pht1_p3 p1_3
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
- -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ -> Index Scan using pht1_c_idx_3 on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
(23 rows)
@@ -5361,8 +5361,8 @@ SET max_parallel_workers_per_gather = 1;
SET debug_parallel_query = on;
-- Partial paths should also be smart enough to employ limits
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Gather
Workers Planned: 1
Single Copy: true
@@ -5373,21 +5373,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
-> Memoize
Cache Key: p1_1.c
Cache Mode: logical
- -> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
+ -> Index Scan using pht1_c_idx_1 on pht1_p1 p2_1
Index Cond: (c = p1_1.c)
-> Nested Loop
-> Seq Scan on pht1_p2 p1_2
-> Memoize
Cache Key: p1_2.c
Cache Mode: logical
- -> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
+ -> Index Scan using pht1_c_idx_2 on pht1_p2 p2_2
Index Cond: (c = p1_2.c)
-> Nested Loop
-> Seq Scan on pht1_p3 p1_3
-> Memoize
Cache Key: p1_3.c
Cache Mode: logical
- -> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
+ -> Index Scan using pht1_c_idx_3 on pht1_p3 p2_3
Index Cond: (c = p1_3.c)
(26 rows)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index d1966cd7d82..9e885c3375b 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1903,16 +1903,16 @@ select * from
from int4_tbl touter) ss,
asptab
where asptab.id > ss.b::int;
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Nested Loop
-> Seq Scan on int4_tbl touter
-> Append
- -> Index Only Scan using asptab0_pkey on asptab0 asptab_1
+ -> Index Only Scan using asptab_pkey_1 on asptab0 asptab_1
Index Cond: (id > (EXISTS(SubPlan 3))::integer)
SubPlan 4
-> Seq Scan on int4_tbl tinner_2
- -> Index Only Scan using asptab1_pkey on asptab1 asptab_2
+ -> Index Only Scan using asptab_pkey_2 on asptab1 asptab_2
Index Cond: (id > (EXISTS(SubPlan 3))::integer)
SubPlan 3
-> Seq Scan on int4_tbl tinner_1
@@ -3566,16 +3566,16 @@ create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Merge Append (actual rows=2.00 loops=1)
Sort Key: ma_test.b
Subplans Removed: 1
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_1 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_2 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
@@ -3589,12 +3589,12 @@ execute mt_q1(15);
(2 rows)
explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25);
- QUERY PLAN
---------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Merge Append (actual rows=1.00 loops=1)
Sort Key: ma_test.b
Subplans Removed: 2
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_1 (actual rows=1.00 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 9
Index Searches: 1
@@ -3636,24 +3636,24 @@ explain (analyze, verbose, costs off, summary off, timing off, buffers off) exec
deallocate mt_q2;
-- ensure initplan params properly prune partitions
explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b)
fromma_test_p2) order by b;
- QUERY PLAN
---------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
Merge Append (actual rows=20.00 loops=1)
Sort Key: ma_test.b
InitPlan 2
-> Result (actual rows=1.00 loops=1)
InitPlan 1
-> Limit (actual rows=1.00 loops=1)
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 (actual rows=1.00 loops=1)
Index Cond: (b IS NOT NULL)
Index Searches: 1
- -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
+ -> Index Scan using ma_test_b_idx_1 on ma_test_p1 ma_test_1 (never executed)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 0
- -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1)
+ -> Index Scan using ma_test_b_idx_2 on ma_test_p2 ma_test_2 (actual rows=10.00 loops=1)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 1
- -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1)
+ -> Index Scan using ma_test_b_idx_3 on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 1
(18 rows)
@@ -4216,8 +4216,8 @@ create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
explain (analyze on, costs off, timing off, summary off, buffers off)
select * from rangep where b IN((select 1),(select 2)) order by a;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
Append (actual rows=0.00 loops=1)
InitPlan 1
-> Result (actual rows=1.00 loops=1)
@@ -4225,16 +4225,16 @@ select * from rangep where b IN((select 1),(select 2)) order by a;
-> Result (actual rows=1.00 loops=1)
-> Merge Append (actual rows=0.00 loops=1)
Sort Key: rangep_2.a
- -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_1_1 on rangep_0_to_100_1 rangep_2 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
- -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_1_2 on rangep_0_to_100_2 rangep_3 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
- -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed)
+ -> Index Scan using rangep_a_idx_1_3 on rangep_0_to_100_3 rangep_4 (never executed)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 0
- -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1)
+ -> Index Scan using rangep_a_idx_2 on rangep_100_to_200 rangep_5 (actual rows=0.00 loops=1)
Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
Index Searches: 1
(19 rows)
@@ -4768,16 +4768,16 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1
+ -> Index Scan using part_abc_a_idx_2 on part_abc_2 part_abc_1
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
-> Merge Append
Sort Key: part_abc_3.a
Subplans Removed: 1
- -> Index Scan using part_abc_3_1_a_idx on part_abc_3_1 part_abc_3
+ -> Index Scan using part_abc_a_idx_3_1 on part_abc_3_1 part_abc_3
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
- -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_4
+ -> Index Scan using part_abc_a_idx_3_2 on part_abc_3_2 part_abc_4
Index Cond: (a >= (stable_one() + 1))
Filter: (d <= stable_one())
-> Subquery Scan on "*SELECT* 2"
@@ -4785,16 +4785,16 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6
+ -> Index Scan using part_abc_a_idx_2 on part_abc_2 part_abc_6
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
-> Merge Append
Sort Key: a
Subplans Removed: 1
- -> Index Scan using part_abc_3_2_a_idx on part_abc_3_2 part_abc_8
+ -> Index Scan using part_abc_a_idx_3_2 on part_abc_3_2 part_abc_8
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
- -> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9
+ -> Index Scan using part_abc_a_idx_3_3 on part_abc_3_3 part_abc_9
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
(35 rows)
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..961e4bb09c4 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5054,13 +5054,13 @@ insert into parent_tab values (generate_series(30,39));
(1 row)
\dP testpart.*
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
\dP
@@ -5080,31 +5080,31 @@ insert into parent_tab values (generate_series(30,39));
(2 rows)
\dPin
- List of partitioned indexes
- Schema | Name | Owner | Parent name | Table
-----------+--------------------+---------------------------+--------------+-------------
- testpart | parent_index | regress_partitioning_role | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | parent_index | child_30_40
+ List of partitioned indexes
+ Schema | Name | Owner | Parent name | Table
+----------+----------------+---------------------------+--------------+-------------
+ testpart | parent_index | regress_partitioning_role | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | parent_index | child_30_40
(2 rows)
\dPn
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
\dPn testpart.*
- List of partitioned relations
- Schema | Name | Owner | Type | Parent name | Table
-----------+--------------------+---------------------------+-------------------+--------------+-------------
- testpart | parent_tab | regress_partitioning_role | partitioned table | |
- testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
- testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
- testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
+ List of partitioned relations
+ Schema | Name | Owner | Type | Parent name | Table
+----------+----------------+---------------------------+-------------------+--------------+-------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table | |
+ testpart | child_30_40 | regress_partitioning_role | partitioned table | parent_tab |
+ testpart | parent_index | regress_partitioning_role | partitioned index | | parent_tab
+ testpart | parent_index_4 | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
drop table parent_tab cascade;
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..6cb35ab97f5 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -330,12 +330,10 @@ CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace;
CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx' ORDER BY relname;
- relname | spcname
--------------+------------------
- part1_a_idx | regress_tblspace
- part2_a_idx | regress_tblspace
- part_a_idx | regress_tblspace
-(3 rows)
+ relname | spcname
+------------+------------------
+ part_a_idx | regress_tblspace
+(1 row)
\d testschema.part
Partitioned table "testschema.part"
@@ -365,7 +363,7 @@ Partitions: testschema.part1 FOR VALUES IN (1),
a | integer | | |
Partition of: testschema.part FOR VALUES IN (1)
Indexes:
- "part1_a_idx" btree (a), tablespace "regress_tblspace"
+ "part_a_idx_1" btree (a), tablespace "regress_tblspace"
\d+ testschema.part1
Table "testschema.part1"
@@ -375,7 +373,7 @@ Indexes:
Partition of: testschema.part FOR VALUES IN (1)
Partition constraint: ((a IS NOT NULL) AND (a = 1))
Indexes:
- "part1_a_idx" btree (a), tablespace "regress_tblspace"
+ "part_a_idx_1" btree (a), tablespace "regress_tblspace"
\d testschema.part_a_idx
Partitioned index "testschema.part_a_idx"
@@ -392,8 +390,8 @@ Tablespace: "regress_tblspace"
--------+---------+------+------------+---------+--------------
a | integer | yes | a | plain |
btree, for table "testschema.part"
-Partitions: testschema.part1_a_idx,
- testschema.part2_a_idx
+Partitions: testschema.part_a_idx_1,
+ testschema.part_a_idx_2
Tablespace: "regress_tblspace"
-- partitioned rels cannot specify the default tablespace. These fail:
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index ea607bed0a4..4563eb46275 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -69,7 +69,7 @@ CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL);
id | int4range | | not null |
valid_at | daterange | | not null |
Indexes:
- "temporal_rng2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ "temporal_rng_pk_1" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
DROP TABLE temporal_rng2;
-- no PK from INHERITS:
@@ -2304,7 +2304,7 @@ UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)';
UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)';
-- should fail:
UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)';
-ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey"
+ERROR: conflicting key value violates exclusion constraint "temporal_partitioned_fk_rng2rng_pk_1"
DETAIL: Key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)) conflicts with existing key (id, valid_at)=([1,2),
[2000-01-01,2000-04-01)).
--
-- partitioned FK referenced updates NO ACTION
@@ -2426,7 +2426,7 @@ UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[4,5)' WHERE id = '[1,2)'
UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[1,2)' WHERE id = '[4,5)';
-- should fail:
UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2000-01-01', '2000-04-01'))
WHEREid = '[1,2)';
-ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey"
+ERROR: conflicting key value violates exclusion constraint "temporal_partitioned_fk_mltrng2mltrng_pk_1"
DETAIL: Key (id, valid_at)=([1,2), {[2000-01-01,2000-04-01)}) conflicts with existing key (id, valid_at)=([1,2),
{[2000-01-01,2000-04-01)}).
--
-- partitioned FK referenced updates NO ACTION
pgsql-bugs by date: