Date: Wed, 19 Jun 2019 20:36:44 -0400
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: <20190620003644.GA24765@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="y0ulUmNC+osPPQO6"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20190329135519.GA21373@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


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

On 2019-Mar-29, Alvaro Herrera wrote:

> 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.

The previously proposed patch doesn't work; there are a few other places
that needed patching.  Here's a proposed patch.  I'm not sure whether
this is a bug fix or a new feature.

With this patch, an index creation will no longer fail in the presence
of a partition that is a foreign table, as long as the index is not a
constraint index (not unique, not primary key).  Conversely,
creating/attaching a partition that is a foreign table does not fail if
the partitioned table only has non-constraint indexes.

With the current code, these commands would all fail, and the resulting
behavior is pretty unhelpful, as reported in this thread and elsewhere.

Any opinions on backpatching this?  I'm considering pg11, but also
considering not to backpatch at all.

(There *is* a much smaller bugfix here, which is that we don't raise an
error when unique/pk indexes exist and a foreign table is attached as
partition.)

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

--y0ulUmNC+osPPQO6
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-Fix-index-creation-with-foreign-partitions.patch"