Thread
Commits
-
Mention partitioned indexes in "Data Definition" chapter
- fad15f4a547a 11.0 landed
-
Local partitioned indexes
- 8b08f7d4820f 11.0 cited
-
pgsql: Local partitioned indexes
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2018-01-19T14:55:08Z
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: https://postgr.es/m/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(-) -
Re: pgsql: Local partitioned indexes
Tom Lane <tgl@sss.pgh.pa.us> — 2018-01-19T16:59:59Z
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
-
Re: pgsql: Local partitioned indexes
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2018-01-19T19:10:16Z
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
-
Re: pgsql: Local partitioned indexes
Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2018-01-29T08:04:40Z
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
-
Re: pgsql: Local partitioned indexes
Alvaro Herrera <alvherre@alvh.no-ip.org> — 2018-02-10T13:09:47Z
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
-
Re: pgsql: Local partitioned indexes
Amit Langote <amitlangote09@gmail.com> — 2018-02-10T14:32:21Z
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
-
Re: pgsql: Local partitioned indexes
Amit Langote <langote_amit_f8@lab.ntt.co.jp> — 2018-02-13T02:35:50Z
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
-
Re: pgsql: Local partitioned indexes
Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-03-12T15:53:36Z
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