Re: postgres_fdw IMPORT SCHEMA and partitioned tables

On Thu, Mar 9, 2017 at 8:47 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> It is not as straight-forward as it seems. A foreign table can be
>> defined as a child (use of PARTITION OF), but not as a parent (use
>> PARTITION BY), and IMPORT SCHEMA has to issue queries to create
>> foreign tables. It seems to me that the correct fix here is to ignore
>> child tables that are part of a partition, and just include the parent
>> in what is imported so as when querying the parent through
>> postgres_fdw all the child partitions are considered automatically.
>> Thoughts?
>
> I think that makes sense. The query in postgresImportForeignSchema() that
> fetches the information about remote tables should be fixed to include
> relkind = 'P' tables (partitioned tables) but exclude relispartition =
> true (partitions). Something like below:
>
> - "WHERE c.relkind IN ('r', 'v', 'f', 'm') "
> + "WHERE c.relkind IN ('r', 'v', 'f', 'm', 'P') "
> + " AND NOT c.relispartition "
>
> It means we don't import tables that are supposed to be partitions of some
> table. If we allow importing the latter, we get access to those
> partitions anyway.
>
> I would like to hear more opinions of course.

For the most part, I'm not very exercised about this either way. I
think that the above definition seems reasonably likely to be a useful
one, but I certainly wouldn't try to insist on it in the face of
opposition; I don't think it's 100% clear what users will want here.

However, if we're going to do something about this, I think it should
be done soon. Otherwise, I'm going to advocate for reclassifying this
issue from "open item" to "possible area for future development".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company