pgsql: Local partitioned indexes
Local partitioned indexes
When CREATE INDEX is run on a partitioned table, create catalog entries
for an index on the partitioned table (which is just a placeholder since
the table proper has no data of its own), and recurse to create actual
indexes on the existing partitions; create them in future partitions
also.
As a convenience gadget, if the new index definition matches some
existing index in partitions, these are picked up and used instead of
creating new ones. Whichever way these indexes come about, they become
attached to the index on the parent table and are dropped alongside it,
and cannot be dropped on isolation unless they are detached first.
To support pg_dump'ing these indexes, add commands
CREATE INDEX ON ONLY <table>
(which creates the index on the parent partitioned table, without
recursing) and
ALTER INDEX ATTACH PARTITION
(which is used after the indexes have been created individually on each
partition, to attach them to the parent index). These reconstruct prior
database state exactly.
Reviewed-by: (in alphabetical order) Peter Eisentraut, Robert Haas, Amit
Langote, Jesper Pedersen, Simon Riggs, David Rowley
Discussion: /messages/by-id/20171113170646.gzweigyrgg6pwsg4@alvherre.pgsql
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/8b08f7d4820fd7a8ef6152a9dd8c6e3cb01e5f99
Modified Files
--------------
doc/src/sgml/catalogs.sgml | 23 +
doc/src/sgml/ref/alter_index.sgml | 14 +
doc/src/sgml/ref/alter_table.sgml | 8 +-
doc/src/sgml/ref/create_index.sgml | 33 +-
doc/src/sgml/ref/reindex.sgml | 5 +
src/backend/access/common/reloptions.c | 1 +
src/backend/access/heap/heapam.c | 9 +-
src/backend/access/index/indexam.c | 3 +-
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/aclchk.c | 9 +-
src/backend/catalog/dependency.c | 14 +-
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 203 +++++++-
src/backend/catalog/objectaddress.c | 5 +-
src/backend/catalog/pg_depend.c | 13 +-
src/backend/catalog/pg_inherits.c | 80 ++++
src/backend/catalog/toasting.c | 2 +
src/backend/commands/indexcmds.c | 397 +++++++++++++++-
src/backend/commands/tablecmds.c | 653 +++++++++++++++++++++++---
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/parser/gram.y | 33 +-
src/backend/parser/parse_utilcmd.c | 65 ++-
src/backend/tcop/utility.c | 22 +
src/backend/utils/adt/amutils.c | 3 +-
src/backend/utils/adt/ruleutils.c | 17 +-
src/backend/utils/cache/relcache.c | 39 +-
src/bin/pg_dump/common.c | 107 ++++-
src/bin/pg_dump/pg_dump.c | 102 +++-
src/bin/pg_dump/pg_dump.h | 11 +
src/bin/pg_dump/pg_dump_sort.c | 56 ++-
src/bin/pg_dump/t/002_pg_dump.pl | 95 ++++
src/bin/psql/describe.c | 20 +-
src/bin/psql/tab-complete.c | 34 +-
src/include/catalog/dependency.h | 15 +
src/include/catalog/index.h | 10 +
src/include/catalog/pg_class.h | 1 +
src/include/catalog/pg_inherits_fn.h | 3 +
src/include/commands/defrem.h | 3 +-
src/include/nodes/execnodes.h | 1 +
src/include/nodes/parsenodes.h | 7 +-
src/include/parser/parse_utilcmd.h | 3 +
src/test/regress/expected/alter_table.out | 65 ++-
src/test/regress/expected/indexing.out | 757 ++++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/alter_table.sql | 16 +
src/test/regress/sql/indexing.sql | 388 +++++++++++++++
49 files changed, 3172 insertions(+), 182 deletions(-)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
Local partitioned indexes
Evidently you're not there yet. I'm suspicious that the continuing
failures on dromedary may trace to its use of -DCOPY_PARSE_PLAN_TREES
... try looking for a missed field addition in copyfuncs.c.
regards, tom lane
Tom Lane wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
Local partitioned indexes
Evidently you're not there yet. I'm suspicious that the continuing
failures on dromedary may trace to its use of -DCOPY_PARSE_PLAN_TREES
... try looking for a missed field addition in copyfuncs.c.
I had already tried COPY_PARSE_PLAN_TREES locally, but that doesn't
reproduce the problem in my machine.
Peter E. noticed that the factor in common in these failures is that the
machines are 32 bits -- so now that I'm back from lunch I can now
reproduce in a 32bit VM and I'm looking into it.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018/01/19 23:55, Alvaro Herrera wrote:
Local partitioned indexes
Modified Files
--------------
doc/src/sgml/catalogs.sgml | 23 +
doc/src/sgml/ref/alter_index.sgml | 14 +
doc/src/sgml/ref/alter_table.sgml | 8 +-
doc/src/sgml/ref/create_index.sgml | 33 +-
doc/src/sgml/ref/reindex.sgml | 5 +
I noticed that the declarative partitioning section in ddl.sgml hasn't
been updated to reflect the features added by this commit. Attached patch
is an attempt to fix that.
Thanks,
Amit
Attachments:
v1-0001-Update-ddl.sgml-to-reflect-features-added-in-8b08.patchtext/plain; charset=UTF-8; name=v1-0001-Update-ddl.sgml-to-reflect-features-added-in-8b08.patchDownload
From 2007545c0d1c1cdfac079161a108a086091e8735 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 29 Jan 2018 16:59:03 +0900
Subject: [PATCH v1] Update ddl.sgml to reflect features added in 8b08f7d4820
---
doc/src/sgml/ddl.sgml | 21 +++++++++++++++------
1 file changed, 15 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1e1f3428a6..9959e7ae6c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3159,6 +3159,18 @@ CREATE INDEX ON measurement_y2007m12 (logdate);
CREATE INDEX ON measurement_y2008m01 (logdate);
</programlisting>
</para>
+
+ <para>
+ Alternatively, you can simply create the index in the parent table,
+ which is same as creating it in every partition that is currently
+ defined. Moreover, once created in the parent table, the index is
+ automatically created in partitions that will be added in the future.
+ </para>
+
+<programlisting>
+CREATE INDEX ON measurement (logdate);
+</programlisting>
+ </para>
</listitem>
<listitem>
@@ -3273,12 +3285,9 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<itemizedlist>
<listitem>
<para>
- There is no facility available to create the matching indexes on all
- partitions automatically. Indexes must be added to each partition with
- separate commands. This also means that there is no way to create a
- primary key, unique constraint, or exclusion constraint spanning all
- partitions; it is only possible to constrain each leaf partition
- individually.
+ There is no way to create a primary key, unique constraint, or
+ exclusion constraint spanning all partitions; it is only possible
+ to constrain each leaf partition individually.
</para>
</listitem>
--
2.11.0
Amit Langote wrote:
On 2018/01/19 23:55, Alvaro Herrera wrote:
Local partitioned indexes
I noticed that the declarative partitioning section in ddl.sgml hasn't
been updated to reflect the features added by this commit. Attached patch
is an attempt to fix that.
Thanks! I considered that keeping the old-style instructions creating
per partition indexes individually was not necessary, so I removed them
and pushed.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sat, Feb 10, 2018 at 10:09 PM, Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:
Amit Langote wrote:
On 2018/01/19 23:55, Alvaro Herrera wrote:
Local partitioned indexes
I noticed that the declarative partitioning section in ddl.sgml hasn't
been updated to reflect the features added by this commit. Attached patch
is an attempt to fix that.Thanks! I considered that keeping the old-style instructions creating
per partition indexes individually was not necessary, so I removed them
and pushed.
Ah, thanks. What you've committed looks perfect.
Regards,
Amit
On 2018/02/10 23:32, Amit Langote wrote:
On Sat, Feb 10, 2018 at 10:09 PM, Alvaro Herrera
<alvherre@alvh.no-ip.org> wrote:Amit Langote wrote:
On 2018/01/19 23:55, Alvaro Herrera wrote:
Local partitioned indexes
I noticed that the declarative partitioning section in ddl.sgml hasn't
been updated to reflect the features added by this commit. Attached patch
is an attempt to fix that.Thanks! I considered that keeping the old-style instructions creating
per partition indexes individually was not necessary, so I removed them
and pushed.Ah, thanks. What you've committed looks perfect.
Sorry, I'd missed reporting one more sentence that doesn't apply anymore.
Attached gets rid of that.
Thanks,
Amit
Attachments:
ddl-partition-index.patchtext/plain; charset=UTF-8; name=ddl-partition-index.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8c3be5b103..8c44ac08dd 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2922,7 +2922,7 @@ VALUES ('Albany', NULL, NULL, 'NY');
Partitions may themselves be defined as partitioned tables, using what is
called <firstterm>sub-partitioning</firstterm>. Partitions may have their
own indexes, constraints and default values, distinct from those of other
- partitions. Indexes must be created separately for each partition. See
+ partitions. See
<xref linkend="sql-createtable"/> for more details on creating partitioned
tables and partitions.
</para>
Amit Langote wrote:
Sorry, I'd missed reporting one more sentence that doesn't apply anymore.
Attached gets rid of that.
Thanks, applied.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services