Foreign Data Wrapper in PostgreSQL sind eine sehr elegante Möglichkeit, andere Datenbanken und andere Datenquellen wie normale Tabellen anzusprechen.
Ich habe den MySQL-FDW eingehend getestet und möchte die Erfahrungen teilen.
Generell lassen sich auch Tabellen anlegen, die aus verschiedenen Gründen danach nicht vollständig in PostgreSQL nutzbar sind. Z. B. verursachen die berüchtigten ungültigen Timestamps von MySQL (0000-00-00 00:00) Lesefehler beim Abfragen der betreffenden Zeilen. Einige spezielle Datentypen, die in MySQL und PostgreSQL gleich heißen, sind trotzdem nicht lesbar, z. B. der Datentyp POINT.
Abgesehen von diesen Problemen geht das Lesen üblicherweise problemlos. In einigen Konstruktionen (z. B. wenn Werte verglichen werden sollen) versucht das Foreign-Wrapper-Modul, PostgreSQL-Sprachelemente wie ::TYP (statt CAST(… AS TYP)) an die fremde Datenbank zu senden, was einen Fehler ergibt.
Die Lösung ist, die betreffenden Werte selbst mit der standardkonformen CAST-Syntax umzuwandeln.
INSERTs und UPDATEs sind ein anderes Thema. Hat man sich mühsam eine Abfragesyntax erarbeitet, die von beiden Datenbanken akzeptiert wird, stürzen PostgreSQL-Backends sporadisch und nicht reproduzierbar ab, sodaß das Datenbanksystem sich automatisch neu startet und dabei alle bestehenden Verbindungen abreißt. Leider wird dabei nichts geloggt, sodaß ich den Grund noch nicht herausgefunden habe. Derzeit kann ich diese Funktionalität jedenfalls nicht verwenden.
Die Anwendung wäre, Änderungen an Datensätzen mit Hilfe von Triggern direkt in der anderen Datenbank nachzuziehen. So läuft weiterhin ein periodischer Pentaho-Data-Integration-Prozesses.
Update 2016-03-06: Mit aktuellen Versionen des Foreign Data Wrappers treten keine Abstürze mehr auf. INSERTs und UPDATEs sind jetzt stabil. Somit können datenbankübergreifende Trigger-Funktionen geschrieben werden, um Werte in der MySQL-Datenbank zu aktualisieren, wenn sich etwas in PostgreSQL ändert.
Practical experience with PostgreSQL and the MySQL Foreign Data Wrapper
Foreign Data Wrappers in PostgreSQL are a very elegant way to access other datasources and use them like normal database tables.
I’d like to share the results of my tests of the MySQL FDW.
It is possible to create tables that are not fully usable in PostgreSQL afterwards, for different reasons. For example, the infamous invalid MySQL timestamp (0000-00-00 00:00) makes the record unreadable. There are data types like POINT that are called the same in MySQL and PostgreSQL but they are different enough not to be readable, as well.
Aside from these problems, reading data usually works without issues. In some queries (e. g. when comparing values not having the same data type), the foreign data wrapper module tries to use PostgreSQL-specific syntax like ::TYPE instead of the standard CAST(… AS TYPE) in the foreign database, returning errors.
The solution for this problem is to explicitly cast the values with the standard CAST syntax.
INSERT and UPDATE don’t work quite as well. After finding a query syntax that is accepted by both database systems, PostgreSQL backends start crashing sporadically and in a non-reproducible way. The database server restarts automatically but kills all existing connections. Nothing is logged, so I couldn’t find the reason yet. Therefore, I can’t use this functionality yet.
I wanted to use write access to the MySQL tables to quickly enter changes in the foreign database from a PostgreSQL trigger function. As this doesn’t work, a Pentaho Data Integration process is started periodically for the same purpose.
Update 2016-03-06: Current versions of the Foreign Data Wrapper don’t crash the PostgreSQL backend anymore. INSERT and UPDATE on foreign tables is stable now. Therefore it is possible to write trigger functions that update values in the MySQL database automatically when a value changes in PostgreSQL.