Date: Fri, 29 Mar 2019 10:55:19 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: stepya@ukr.net, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15724: Can't create foreign table as partition
Message-ID: <20190329135519.GA21373@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="CE+1k2dSO48ffgeK"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <15724-d5a58fa9472eef4f@postgresql.org>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--CE+1k2dSO48ffgeK
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2019-Mar-29, PG Bug reporting form wrote:

> CREATE FOREIGN TABLE staging.msg_json PARTITION of msg_json  
>     FOR VALUES FROM (20170101) TO (20180101)
>     SERVER postgres_big_data_arch
>     OPTIONS (schema_name 'fix_capture', table_name 'msg_json');
> 
> 
> I got error !!!!
> 
> SQL Error [42809]: ERROR: cannot create index on foreign table "msg_json"
>   ERROR: cannot create index on foreign table "msg_json"
>   ERROR: cannot create index on foreign table "msg_json"

Ah, this is because we try to propagate the index to the partition,
which appears to be less than desirable.  Maybe this patch fixes it?  I
have not tested it, only verified that it compiles.

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

--CE+1k2dSO48ffgeK
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="partforeign-no-indexes.patch"

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f8ee4b0a84b..f2da75d9b9c 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -921,6 +921,14 @@ DefineIndex(Oid relationId,
 				int			maplen;
 
 				childrel = heap_open(childRelid, lockmode);
+
+				/* don't try to create indexes on foreign tables, though */
+				if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+				{
+					heap_close(childrel, lockmode);
+					continue;
+				}
+
 				childidxs = RelationGetIndexList(childrel);
 				attmap =
 					convert_tuples_by_name_map(RelationGetDescr(childrel),
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 23c2e923757..eb34914f5ec 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14934,6 +14934,10 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
 	MemoryContext cxt;
 	MemoryContext oldcxt;
 
+	/* don't try to create indexes on foreign tables */
+	if (attachrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+		return;
+
 	cxt = AllocSetContextCreate(CurrentMemoryContext,
 								"AttachPartitionEnsureIndexes",
 								ALLOCSET_DEFAULT_SIZES);

--CE+1k2dSO48ffgeK--