From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Maksim Milyutin <milyutinma@gmail.com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Date: Fri, 22 Dec 2017 12:10:23 -0300
Message-ID: <20171222151023.ie4zrbgwtva7flij@alvherre.pgsql> (raw)
In-Reply-To: <242e9c8d-113e-22e5-05bf-550eb0801f7b@redhat.com>
Hello Jesper,
Jesper Pedersen wrote:
> Passes check-world here too w/ TAP + cassert.
Great, thanks for checking.
> index.c:
> [and a few other comments]
I believe these are all fixed by the attached delta patch.
If you have wording suggestions for the doc changes, please send them
along.
Thanks for reading,
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 24ec874da257cf617359cce253ff4f8ad33166d7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Fri, 22 Dec 2017 11:41:15 -0300
Subject: [PATCH] some fixes per Jesper
---
doc/src/sgml/ref/create_index.sgml | 28 +++++++++++++++++-----------
doc/src/sgml/ref/reindex.sgml | 5 +++++
src/backend/catalog/index.c | 2 +-
src/bin/psql/tab-complete.c | 7 ++-----
4 files changed, 25 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex b7b0506d6d..5137fe6383 100644--- a/doc/src/sgml/ref/create_index.sgml+++ b/doc/src/sgml/ref/create_index.sgml@@ -557,17 +557,23 @@ Indexes:
<para>
When <literal>CREATE INDEX</literal> is invoked on a partitioned
- table, the default behavior is to recurse to all partitions to- ensure they all have a matching indexes. Each partition is first- checked to determine whether equivalent index already exists,- and if so, that index will become attached as a partition index to- the index being created, which will be its parent index. If no- matching index exists, a new index will be created and automatically- attached. If <literal>ONLY</literal> is specified, no recursion- is done. Note, however, that any partition that is created in the- future using <command>CREATE TABLE .. PARTITION OF</command> will- automatically contain the index regardless of whether this option- was specified.+ table, the default behavior is to recurse to all partitions to ensure+ they all have matching indexes.+ Each partition is first checked to determine whether an equivalent+ index already exists, and if so, that index will become attached as a+ partition index to the index being created, which will become its+ parent index.+ If no matching index exists, a new index will be created and+ automatically attached; the name of the new index in each partition+ will be determined as if no index name had been specified in the+ command.+ If the <literal>ONLY</literal> option is specified, no recursion+ is done, and the index is marked invalid+ (<command>ALTER INDEX ... ATTACH PARTITION</command> turns the index+ valid, once all partitions acquire the index.) Note, however, that+ any partition that is created in the future using+ <command>CREATE TABLE ... PARTITION OF</command> will automatically+ contain the index regardless of whether this option was specified.
</para>
<para>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 79f6931c6a..1c21fafb80 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -231,6 +231,11 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } <replacea
reindex anything.
</para>
+ <para>+ Reindexing partitioned tables or partitioned indexes is not supported.+ Each individual partition can be reindexed separately instead.+ </para>+
</refsect1>
<refsect1>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex a62fe158ce..eee16dde25 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -629,7 +629,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indexrelid - 1] = ObjectIdGetDatum(indexoid);
values[Anum_pg_index_indrelid - 1] = ObjectIdGetDatum(heapoid);
- values[Anum_pg_index_indparentidx - 1 ] = ObjectIdGetDatum(parentIndexOid);+ values[Anum_pg_index_indparentidx - 1] = ObjectIdGetDatum(parentIndexOid);
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex 6af773f1bf..159ce4129e 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1695,14 +1695,11 @@ psql_completion(const char *text, int start, int end)
/* ALTER INDEX <name> */
else if (Matches3("ALTER", "INDEX", MatchAny))
COMPLETE_WITH_LIST7("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
- "RESET", "ATTACH PARTITION", "DETACH PARTITION");- else if (Matches4("ALTER", "INDEX", MatchAny, "ATTACH|DETACH"))+ "RESET", "ATTACH PARTITION");+ else if (Matches4("ALTER", "INDEX", MatchAny, "ATTACH"))
COMPLETE_WITH_CONST("PARTITION");
else if (Matches5("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
- /* can be improved to show only indexes that are already attached */- else if (Matches5("ALTER", "INDEX", MatchAny, "DETACH", "PARTITION"))- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
/* ALTER INDEX <name> ALTER COLUMN <colnum> */
else if (Matches6("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny))
COMPLETE_WITH_CONST("SET STATISTICS");
--
2.11.0
Attachments:
[text/plain] 0001-some-fixes-per-Jesper.patch (4.9K, ../20171222151023.ie4zrbgwtva7flij@alvherre.pgsql/2-0001-some-fixes-per-Jesper.patch)
download | inline diff:
From 24ec874da257cf617359cce253ff4f8ad33166d7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Fri, 22 Dec 2017 11:41:15 -0300
Subject: [PATCH] some fixes per Jesper
---
doc/src/sgml/ref/create_index.sgml | 28 +++++++++++++++++-----------
doc/src/sgml/ref/reindex.sgml | 5 +++++
src/backend/catalog/index.c | 2 +-
src/bin/psql/tab-complete.c | 7 ++-----
4 files changed, 25 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex b7b0506d6d..5137fe6383 100644--- a/doc/src/sgml/ref/create_index.sgml+++ b/doc/src/sgml/ref/create_index.sgml@@ -557,17 +557,23 @@ Indexes:
<para>
When <literal>CREATE INDEX</literal> is invoked on a partitioned
- table, the default behavior is to recurse to all partitions to- ensure they all have a matching indexes. Each partition is first- checked to determine whether equivalent index already exists,- and if so, that index will become attached as a partition index to- the index being created, which will be its parent index. If no- matching index exists, a new index will be created and automatically- attached. If <literal>ONLY</literal> is specified, no recursion- is done. Note, however, that any partition that is created in the- future using <command>CREATE TABLE .. PARTITION OF</command> will- automatically contain the index regardless of whether this option- was specified.+ table, the default behavior is to recurse to all partitions to ensure+ they all have matching indexes.+ Each partition is first checked to determine whether an equivalent+ index already exists, and if so, that index will become attached as a+ partition index to the index being created, which will become its+ parent index.+ If no matching index exists, a new index will be created and+ automatically attached; the name of the new index in each partition+ will be determined as if no index name had been specified in the+ command.+ If the <literal>ONLY</literal> option is specified, no recursion+ is done, and the index is marked invalid+ (<command>ALTER INDEX ... ATTACH PARTITION</command> turns the index+ valid, once all partitions acquire the index.) Note, however, that+ any partition that is created in the future using+ <command>CREATE TABLE ... PARTITION OF</command> will automatically+ contain the index regardless of whether this option was specified.
</para>
<para>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgmlindex 79f6931c6a..1c21fafb80 100644--- a/doc/src/sgml/ref/reindex.sgml+++ b/doc/src/sgml/ref/reindex.sgml@@ -231,6 +231,11 @@ REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } <replacea
reindex anything.
</para>
+ <para>+ Reindexing partitioned tables or partitioned indexes is not supported.+ Each individual partition can be reindexed separately instead.+ </para>+
</refsect1>
<refsect1>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex a62fe158ce..eee16dde25 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -629,7 +629,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indexrelid - 1] = ObjectIdGetDatum(indexoid);
values[Anum_pg_index_indrelid - 1] = ObjectIdGetDatum(heapoid);
- values[Anum_pg_index_indparentidx - 1 ] = ObjectIdGetDatum(parentIndexOid);+ values[Anum_pg_index_indparentidx - 1] = ObjectIdGetDatum(parentIndexOid);
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex 6af773f1bf..159ce4129e 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1695,14 +1695,11 @@ psql_completion(const char *text, int start, int end)
/* ALTER INDEX <name> */
else if (Matches3("ALTER", "INDEX", MatchAny))
COMPLETE_WITH_LIST7("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
- "RESET", "ATTACH PARTITION", "DETACH PARTITION");- else if (Matches4("ALTER", "INDEX", MatchAny, "ATTACH|DETACH"))+ "RESET", "ATTACH PARTITION");+ else if (Matches4("ALTER", "INDEX", MatchAny, "ATTACH"))
COMPLETE_WITH_CONST("PARTITION");
else if (Matches5("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
- /* can be improved to show only indexes that are already attached */- else if (Matches5("ALTER", "INDEX", MatchAny, "DETACH", "PARTITION"))- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes, NULL);
/* ALTER INDEX <name> ALTER COLUMN <colnum> */
else if (Matches6("ALTER", "INDEX", MatchAny, "ALTER", "COLUMN", MatchAny))
COMPLETE_WITH_CONST("SET STATISTICS");
--
2.11.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, jesper.pedersen@redhat.com, robertmhaas@gmail.com, david.rowley@2ndquadrant.com, simon@2ndquadrant.com, milyutinma@gmail.com
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
In-Reply-To: <20171222151023.ie4zrbgwtva7flij@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox