Re: [Proposal] Arbitrary queries in postgres_fdw

rtorre(at)carto(dot)com writes:
> We stumbled upon a few cases in which retrieving information from the
> foreign server may turn pretty useful before creating any foreign
> table, especially info related to the catalog. E.g: a list of schemas
> or tables the user has access to.

> I thought of using dblink for it, but that requires duplication of
> server and user mapping details and it adds its own management of
> connections.

> Then I thought a better approach may be a mix of both: a function to
> issue arbitrary queries to the foreign server reusing all the details
> encapsulated in the server and user mapping. It would use the same
> pool of connections.

dblink can already reference a postgres_fdw "server" for connection
details, so I think this problem is already solved from the usability
end of things. And allowing arbitrary queries to go over a postgres_fdw
connection would be absolutely disastrous from a debuggability and
maintainability standpoint, because they might change the remote
session's state in ways that postgres_fdw isn't prepared to handle.
(In a dblink connection, the remote session's state is the user's
responsibility to manage, but this isn't the case for postgres_fdw.)
So I think this proposal has to be firmly rejected.

regards, tom lane