From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: stepya@ukr.net
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15724: Can't create foreign table as partition
Date: Wed, 19 Jun 2019 20:36:44 -0400
Message-ID: <20190620003644.GA24765@alvherre.pgsql> (raw)
In-Reply-To: <20190329135519.GA21373@alvherre.pgsql>
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
Attachments:
[text/x-diff] 0001-Fix-index-creation-with-foreign-partitions.patch (0B, ../20190620003644.GA24765@alvherre.pgsql/2-0001-Fix-index-creation-with-foreign-partitions.patch)
download
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-bugs@postgresql.org
Cc: alvherre@2ndquadrant.com, stepya@ukr.net, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15724: Can't create foreign table as partition
In-Reply-To: <20190620003644.GA24765@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