Re: postgres_fdw IMPORT SCHEMA and partitioned tables

On Thu, Mar 9, 2017 at 8:47 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.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