Re: postgres_fdw: IMPORT FOREIGN SCHEMA ... LIMIT TO (partition)

On Mon, 22 Mar 2021 at 21:16, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
>
> Hi,
>
> I took a look at this patch.

Thanks!

> Patch applies on current master.
>
> Documentation and adjusted regression tests included.
> Regression tests passes without errors.
>
> The patch changes IMPORT FOREIGN SCHEMA to explicitely allow partition
> child tables in the LIMIT TO clause of the IMPORT FOREIGN SCHEMA
> command by relaxing the checks introduced with commit [1]. The reason
> behind [1] are discussed in [2].

I should've included potentially interested parties earlier, but never
too late. Stephen, Michael, Amit, would you have an opinion on lifting
this restriction for the LIMIT TO clause, seeing your involvement in
the implementation of removing partitions from IFS?

> So the original behavior this patch wants to address was done
> intentionally, so what needs to be discussed here is whether we want to
> relax that a little. One argument for the original behavior since then
> was that it is cleaner to just automatically import the parent, which
> allows access to the childs through the foreign table anways and
> exclude partition childs when querying pg_class.

Yes, but it should be noted that the main reason that was mentioned as
a reason to exclude partitions is to not cause table catalog bloat,
and I argue that this argument is not as solid in the case of the
explicitly named tables of the LIMIT TO clause. Except if SQL standard
prescribes otherwise, I think allowing partitions in LIMIT TO clauses
is an improvement overall.

> I haven't seen demand for the implemented feature here myself, but i
> could imagine use cases where just a single child or a set of child
> tables are candidates. For example, i think it's possible that users
> can query only specific childs and want them to have imported on
> another foreign server.

I myself have had this need, in that I've had to import some
partitions manually as a result of this limitation. IMPORT FORAIGN
SCHEMA really is great when it works, but limitations like these are
crippling for some more specific use cases (e.g. allowing
long-duration read-only access to one partition in the partition tree
while also allowing the partition layout of the parents to be
modified).

With regards,

Matthias.