Re: postgres_fdw IMPORT SCHEMA and partitioned tables

On Wed, Mar 29, 2017 at 12:22 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 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.

Users like things that are friendly, and we are most likely going to
piss them off when using postgres_fdw if they need to list manually
each parent table from the IMPORT FOREIGN SCHEMA command.

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

I was just waiting for the end of the CF before sending in a patch,
allocating now some time to look at some patches pending for reviews.
--
Michael