Features of the MySQL Foreign Data Wrapper¶
The key features of the MySQL Foreign Data Wrapper are listed below:
Writable FDW¶
The MySQL Foreign Data Wrapper allows you to modify content stored on a MySQL server from a PostgreSQL server. Users can INSERT, UPDATE and DELETE data in the remote MySQL tables by inserting, updating and deleting the data locally in the foreign tables. MySQL foreign data wrapper uses the Postgres type casting mechanism to provide opposite type casting between MySQL and Postgres data types.
Note
The first column of MySQL table must have unique/primary key for DML to work.
See also:
Connection Pooling¶
The MySQL_FDW establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and reused for subsequent queries in the same session.
WHERE Clause Push-down¶
The MySQL Foreign Data Wrapper allows the push-down of WHERE clauses to the foreign server for execution. This feature optimizes remote queries to reduce the number of rows transferred from foreign servers.
Column Push-down¶
The MySQL Foreign Data Wrapper supports selective column push-down. As a result, the query brings back only those columns that are a part of the select target list.
Prepared Statement¶
The MySQL Foreign Data Wrapper supports prepared statements. The select queries uses prepared statements instead of simple query protocol.
Import Foreign Schema¶
The MySQL Foreign Data Wrapper allows you to import foreign schemas; this allows the local host to import table definitions to the Advanced Server host from the MySQL server. The new foreign tables are created with the corresponding column types and same table name as that of remote tables in the existing local schema.
See also:
Automated Cleanup¶
The MySQL Foreign Data Wrappper allows the cleanup of foreign tables in a single operation using the DROP EXTENSION command. This feature is specifically useful when a foreign table is created for a temporary purpose, as in case of data migration. The syntax is:
DROP EXTENSION mysql_fdw CASCADE;
For more information, see DROP EXTENSION.
