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

Hi Matthias,

On Wed, Mar 24, 2021 at 9:23 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
> On Mon, 22 Mar 2021 at 21:16, Bernd Helmle <mailings(at)oopsware(dot)de> wrote:
> > 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?

Sorry that I'm replying to this a bit late.

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

FWIW, I agree that it would be nice to have this.

--
Amit Langote
EDB: http://www.enterprisedb.com