From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: 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, 15 Dec 2017 19:03:52 -0300
Message-ID: <20171215220352.itrvm2x3dgv6hwox@alvherre.pgsql> (raw)
In-Reply-To: <20171215210253.dhixou5smlw27kos@alvherre.pgsql>
Alvaro Herrera wrote:
> 3. Robert's: use CREATE INDEX ON ONLY <parent>, which creates a shell
> index on parent only (no recursion), followed by CREATE INDEX ON
> <partition>. DETACH is not provided. If you ATTACH an index for a
> partition that already has one index attached, then (1) the newly
> attached one replaces the original (i.e. effectively REPLACE) or (2)
> you get an error and we implement a separate ALTER INDEX REPLACE
> command. It's not clear to me how or when the shell index becomes a
> real index.
As I understand the whole purpose of this design is that there is no
point during the restore at which the index lacks indexes on partitions:
it is either complete, or it doesn't exist yet. If we create the index
on parent first, and later the indexes on partitions, that condition is
not satisfied. To solve this, we could create the children indexes
first and then the parent; but how do we indicate to the parent creation
which are the indexes that oughta be marked as children? ALTER INDEX
ATTACH PARTITION doesn't cut it, because it occurs after the index on
parent is created, which is too late. We would do something like
CREATE INDEX ON parent (columns) [other stuff]
ATTACH idx_on_child1, idx_on_child2, idx_on_child3;
but this seems mighty ugly.
Anyway if we do that, what is the point of ALTER INDEX ATTACH PARTITION?
We might as leave it out and just keep the door open for a later ALTER
INDEX REPLACE.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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, 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: <20171215220352.itrvm2x3dgv6hwox@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