Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fGRKi-00078n-Ts for pgsql-hackers@arkaria.postgresql.org; Wed, 09 May 2018 15:48:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fGRKf-0002fC-2E for pgsql-hackers@arkaria.postgresql.org; Wed, 09 May 2018 15:48:45 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fGRKe-0002f3-Iv for pgsql-hackers@lists.postgresql.org; Wed, 09 May 2018 15:48:44 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fGRKa-0006mm-NT for pgsql-hackers@lists.postgresql.org; Wed, 09 May 2018 15:48:43 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id BBC0921C1EE2; Wed, 9 May 2018 18:48:37 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from ars-thinkpad (nat03-43-2.netorn.net [188.35.130.88]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 800C421C1D25; Wed, 9 May 2018 18:48:37 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1525880917; bh=vBpLY8ZSO7JaQzZvitGrhU+MDQhs78Scbw7S0TFGwzk=; h=References:From:To:Cc:Subject:In-reply-to:Date; b=WsKRcr/vi+dh4lKYPWEehaUvafHdKXaqmFk3ZHSm0zCbTpw0P6zQayx5s81KXxuzu UCuHURwNcjUYUTIwUh7vXJEQtB+8YVhn08qgsWLgliBnZ7HafBSqCf8jjr8wd8A05Q VqS5JHhjoxKI63qoWZcVZaj0QDiewRSjtocx14rc= References: <87sh71cakz.fsf@ars-thinkpad> <87r2mkdhwl.fsf@ars-thinkpad> User-agent: mu4e 1.1.0; emacs 26.0.50 From: Arseny Sher To: Simon Riggs Cc: Arseny Sher , "pgsql-hackers\@lists.postgresql.org" Subject: Re: Indexes on partitioned tables and foreign partitions In-reply-to: Date: Wed, 09 May 2018 18:48:37 +0300 Message-ID: <87po24de3u.fsf@ars-thinkpad> MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Simon Riggs writes: > Indexes on foreign tables cause an ERROR, so yes, we already just > don't create them. > > You're suggesting silently skipping the ERROR. I can't see a reason for that. Truly, I was inaccurate. I mean that index propagation is a nice feature, and making it available for mix of foreign and local partitions skipping the foreign ones is useful thing for users who understand the consequences (of course there should be a warning in the docs -- my patch was just an illustration of the idea). As I said, FDW-based sharding often involves mix of foreign and local partitions, even in simple manual forms. Imagine that you have a table which is too big to fit into one server, so you partition it and move some partiitons to another machine; you still would like to access the whole table to avoid manual tuple routing. Or, you partition table by timestamps and keep recent data on fast primary server, gradually moving old partitions to another box. Again, it would be nice to access table as a whole to e.g. calculate some analytics. Anyway, given other opinions, I assume that the community has chosen more conservative approach. Indeed, we can't guarantee uniqueness this way; that's fully users responsiblity. -- Arseny Sher Postgres Professional: http://www.postgrespro.com The Russian Postgres Company