Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1ePy5M-0007zc-QZ
	for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2017 22:04:04 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1ePy5L-0007si-CU
	for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2017 22:04:03 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <alvherre@alvh.no-ip.org>)
	id 1ePy5L-0007sX-67
	for pgsql-hackers@lists.postgresql.org; Fri, 15 Dec 2017 22:04:03 +0000
Received: from smtprelay0002.b.hostedemail.com ([64.98.42.2]
 helo=smtprelay.b.hostedemail.com)
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <alvherre@alvh.no-ip.org>)
	id 1ePy5G-0003vb-Ol
	for pgsql-hackers@postgresql.org; Fri, 15 Dec 2017 22:04:02 +0000
Received: from filter.hostedemail.com (10.5.19.248.rfc1918.com [10.5.19.248])
	by smtprelay02.b.hostedemail.com (Postfix) with ESMTP id A95FAD3813;
	Fri, 15 Dec 2017 22:03:55 +0000 (UTC)
X-Session-Marker: 616C76686572726540616C76682E6E6F2D69702E6F7267
X-Spam-Summary: 
 50,0,0,,d41d8cd98f00b204,alvherre@alvh.no-ip.org,:::::::::,RULES_HIT:41:355:379:599:960:967:973:988:989:1260:1277:1311:1312:1313:1314:1345:1359:1437:1515:1516:1518:1519:1534:1541:1593:1594:1595:1596:1711:1730:1747:1777:1792:2393:2525:2540:2560:2563:2682:2685:2692:2828:2859:2933:2937:2939:2942:2945:2947:2951:2954:3022:3138:3139:3140:3141:3142:3353:3865:3866:3867:3868:3871:3872:3873:3874:3934:3936:3938:3941:3944:3947:3950:3953:3956:3959:4250:4605:5007:6261:7809:7903:9025:9121:10004:10394:10400:10848:11026:11658:11914:13069:13311:13357:13439:13894:13895:14764:21080:21627:30054:30069,0,RBL:none,CacheIP:none,Bayesian:0.5,0.5,0.5,Netcheck:none,DomainCache:0,MSF:not
 bulk,SPF:,MSBL:0,DNSBL:none,Custom_rules:0:0:0,LFtime:2,LUA_SUMMARY:none
X-HE-Tag: bomb75_3f471e94e0138
X-Filterd-Recvd-Size: 2641
Received: from alvin.alvh.no-ip.org (unknown [179.57.113.18])
	(Authenticated sender: alvherre@alvh.no-ip.org)
	by omf14.b.hostedemail.com (Postfix) with ESMTPA;
	Fri, 15 Dec 2017 22:03:54 +0000 (UTC)
Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000)
	id 5E03190F; Fri, 15 Dec 2017 19:03:52 -0300 (-03)
Date: Fri, 15 Dec 2017 19:03:52 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>,
	Maksim Milyutin <milyutinma@gmail.com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Message-ID: <20171215220352.itrvm2x3dgv6hwox@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20171215210253.dhixou5smlw27kos@alvherre.pgsql>
User-Agent: NeoMutt/20170306-137-4415bd-dirty (1.8.0)
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

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