On Fri, May 14, 2021 at 6:35 AM tsunakawa.ta...@fujitsu.com <tsunakawa.ta...@fujitsu.com> wrote: > > From: Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.com> > > I think it will be useful to allow foreign tables to be VACUUMed if > > the underlying FDW supports, currently VACUUM doesn't support foreign > > tables, see [1]. > > Could you let us imagine more concretely how useful it will be? While > TRUNCATE can be part of an application's data processing as alternative to > DELETE, I think VACUUM is purely the data storage maintenance that's > performed by the DBA and can be done naturally locally on the server where > the table resides. (The existing ANALYZE on FDW is an exception; it's useful > to also have data statistics locally.)
This can be useful in situations like where there are many remote postgres servers that are connected to a single coordinator on which foreign tables are defined for each of the remote tables. In this case, the DBA (or whoever is responsible to do that job) doesn't have to figure out which remote server should be logged onto to perform the VACUUM. They can issue VACUUM command on the foreign table from the coordinator server. > > this may not be much useful for FDWs that connect to remote non-MVCC > > databases where the concept of VACUUM may not apply, but for > > postgres_fdw and others it might help. > > Can you show some examples of "others"? I believe we should be careful not > to make the FDW interface a swamp for functions that are only convenient for > PostgreSQL. There are other databases that have MVCC implemented for which the bloat clean up might be necessary at some point. They may not have the same terminology that postgres has for cleaning up the bloat. For instance, MySQL (instead of VACUUM they have OPTIMIZE TABLE command) which can be connected to postgres_fdw using supported fdw And see [1] for the databases that have MVCC support. I'm not sure if all of them have a FDW to connect to postgres. [1] https://dbdb.io/browse?concurrency-control=multi-version-concurrency-control-mvcc > How about adding a routine to the FDW interface that allows to execute an > arbitrary command like the following? VACUUM will be able to use this. > > PGresult *DoCommandPathThrough(ForeignTable *table, const char *command); > > Or, maybe it's more flexible to use ForeignServer instead of ForeignTable. I agree with Michael Paquier's response to this point that it can be an issue from a security standpoint. But we could have had such kind of a generic API for commands like TRUNCATE, ANALYZE, VACUUM etc. void ExecRemoteCommand(ForeignServer *server, const char *command, void *input_params, void *output_params); with the API knowing all the supported commands and erroring out on unsupported commands. Now, we have a separate API for each of the supported commands which looks actually cleaner. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
