Re: postgres_fdw IMPORT SCHEMA and partitioned tables

On 2017/03/10 10:26, Michael Paquier wrote:
> On Thu, Mar 9, 2017 at 11:15 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> While reviewing Amit Langote's patch to handle partitioned tables
>> properly in various contrib modules (mostly by throwing an error since
>> things like pageinspect aren't going to work on the empty 'parent'
>> table), I went looking through contrib for other modules that do
>> something with relkind and noticed that postgres_fdw's IMPORT SCHEMA
>> would pull in the child tables (relkind = 'r') but would ignore the
>> parent table (relkind = 'P', or soon to be 'p', I guess).
>
> 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.

> Of course this should be documented.

+1

>> I tend to view this as an issue which should be added to the open items
>> list and resolved before PG10 (though perhaps it could be done after
>> feature freeze), but I could see an argument that it should be just a
>> documented limitation of postgres_fdw and that adding such support would
>> be a new feature.
>
> Agreed. I think that this is a bug, because any database having one
> partitioning set of tables would fail to import automatically except
> by excluding some tables, and that's annoying for the user.

Agreed too.

>> In any case, this seems like an issue that should be addressed one way
>> or the other, so I'll add it to the open items list. I'm not planning
>> to work on fixing it myself, but if someone proposes a patch which looks
>> reasonable, I'll try to find time for it.
>
> Well, I worked on IMPORT SCHEMA, so I'm fine to do something. After
> the CF is done though.

Thanks.

Regards,
Amit