Re: Support for VACUUMing Foreign Tables
От | Bharath Rupireddy |
---|---|
Тема | Re: Support for VACUUMing Foreign Tables |
Дата | |
Msg-id | CALj2ACXnPn43ihTXub62LnFpXncfO3B41QO0w1NqdfJ5VY5jfg@mail.gmail.com обсуждение исходный текст |
Ответ на | RE: Support for VACUUMing Foreign Tables ("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>) |
Ответы |
RE: Support for VACUUMing Foreign Tables
("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>)
|
Список | pgsql-hackers |
On Fri, May 14, 2021 at 6:35 AM tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com> wrote: > > From: Bharath Rupireddy <bharath.rupireddyforpostgres@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 processingas alternative to DELETE, I think VACUUM is purely the data storage maintenance that's performed by the DBA andcan be done naturally locally on the server where the table resides. (The existing ANALYZE on FDW is an exception; it'suseful 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 functionsthat 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? VACUUMwill 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
В списке pgsql-hackers по дате отправления:
Следующее
От: vignesh CДата:
Сообщение: Added missing tab completion for alter subscription set option