Horton

Thread

Commits

  1. Document piecemeal construction of partitioned indexes

  1. Documentation for partitioned indexes?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-26T18:19:19Z

    We appear to have a fair amount of support now for operations like
constructing a partitioned index piecemeal, e.g. adding indexes
to the partitions one at a time and then attaching them to a
parent partitioned index, with the parent ultimately transitioning
from "not valid" to "valid" once all the pieces are attached.

However, I cannot find any coherent documentation explaining how
to do this (or why you'd want to).  Am I just looking in the wrong
places?

			regards, tom lane

  2. Re: Documentation for partitioned indexes?

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-04-26T18:53:33Z

    On 2019-Apr-26, Tom Lane wrote:

> We appear to have a fair amount of support now for operations like
> constructing a partitioned index piecemeal, e.g. adding indexes
> to the partitions one at a time and then attaching them to a
> parent partitioned index, with the parent ultimately transitioning
> from "not valid" to "valid" once all the pieces are attached.
> 
> However, I cannot find any coherent documentation explaining how
> to do this (or why you'd want to).  Am I just looking in the wrong
> places?

Hmm.  Under Notes for CREATE INDEX there is a paragraph on this:

	When CREATE INDEX is invoked on a partitioned 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 ONLY option is specified, no
	recursion is done, and the index is marked invalid. (ALTER INDEX
	... ATTACH PARTITION marks the index valid, once all partitions
	acquire matching indexes.) Note, however, that any partition
	that is created in the future using CREATE TABLE ... PARTITION
	OF will automatically have a matching index, regardless of
	whether ONLY is specified.

I suppose I better add something in Chapter 5 (DDL), possibly inside the
5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
Partitioned Tables" perhaps?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: Documentation for partitioned indexes?

    Jonathan S. Katz <jkatz@postgresql.org> — 2019-04-26T19:07:32Z

    On 4/26/19 2:53 PM, Alvaro Herrera wrote:
> On 2019-Apr-26, Tom Lane wrote:
> 
>> We appear to have a fair amount of support now for operations like
>> constructing a partitioned index piecemeal, e.g. adding indexes
>> to the partitions one at a time and then attaching them to a
>> parent partitioned index, with the parent ultimately transitioning
>> from "not valid" to "valid" once all the pieces are attached.
>>
>> However, I cannot find any coherent documentation explaining how
>> to do this (or why you'd want to).  Am I just looking in the wrong
>> places?
> 
> Hmm.  Under Notes for CREATE INDEX there is a paragraph on this:
> 
> 	When CREATE INDEX is invoked on a partitioned 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 ONLY option is specified, no
> 	recursion is done, and the index is marked invalid. (ALTER INDEX
> 	... ATTACH PARTITION marks the index valid, once all partitions
> 	acquire matching indexes.) Note, however, that any partition
> 	that is created in the future using CREATE TABLE ... PARTITION
> 	OF will automatically have a matching index, regardless of
> 	whether ONLY is specified.
> 
> I suppose I better add something in Chapter 5 (DDL), possibly inside the
> 5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
> Partitioned Tables" perhaps?

+1; (though note on devel it's section 5.11, not that it matters in the
SGML)

I'd suggest keeping the title of the section similar to the one with
constraints, i.e. "Partitioning and Indexes"

Thanks,

Jonathan

  4. Re: Documentation for partitioned indexes?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-26T19:14:32Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> I suppose I better add something in Chapter 5 (DDL), possibly inside the
> 5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
> Partitioned Tables" perhaps?

I was expecting to find it in "5.11.2.2. Partition Maintenance".

The structure of 5.11 seems a bit weird already, in that important
(IMO) topics like preferred maintenance procedures are buried at
a lower nesting level than essentially-historical trivia like
"5.11.3. Implementation Using Inheritance".  But maybe right now
is not the time to redesign that.

BTW, is there anything equivalent for unique/pkey constraints?
I tried "add constraint unique ... not valid" and just got a
raspberry.

			regards, tom lane

  5. Re: Documentation for partitioned indexes?

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-05-16T20:10:22Z

    On 2019-Apr-26, Tom Lane wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > I suppose I better add something in Chapter 5 (DDL), possibly inside the
> > 5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
> > Partitioned Tables" perhaps?
> 
> I was expecting to find it in "5.11.2.2. Partition Maintenance".

Here's a first attempt at doing that.  I vote to backpatch this to pg11
(since this functionality is all there).

> BTW, is there anything equivalent for unique/pkey constraints?
> I tried "add constraint unique ... not valid" and just got a
> raspberry.

Sure, just "alter table only parent", without explicitly marking it not
valid.  Then the indexes on children must be attached to the parent
indexes; this searches for constraints and does the right thing.

On 2019-Apr-26, Jonathan S. Katz wrote:

> I'd suggest keeping the title of the section similar to the one with
> constraints, i.e. "Partitioning and Indexes"

In the end, it seemed material far too short to have its own subsection,
but maybe if we redesign the whole section we could lay it out
differently?  (One idea would be to leave 5.11 for declarative
partitioning, and add a new section 5.12 for legacy inheritance.
Something to think about for pg13)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  6. Re: Documentation for partitioned indexes?

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-06-04T20:45:30Z

    On 2019-May-16, Alvaro Herrera wrote:

> On 2019-Apr-26, Tom Lane wrote:
> 
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > > I suppose I better add something in Chapter 5 (DDL), possibly inside the
> > > 5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
> > > Partitioned Tables" perhaps?
> > 
> > I was expecting to find it in "5.11.2.2. Partition Maintenance".
> 
> Here's a first attempt at doing that.  I vote to backpatch this to pg11
> (since this functionality is all there).

Not hearing any contrary votes, I have backpatched this to pg11.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services