Informix Foreign Data Wrapper für PostgreSQL^®

Seit PostgreSQL^® 9.1 implementiert PostgreSQL^® eine Schnittstelle für den Zugriff auf externe Datenquellen. Die im SQL Standard festgelegte Schnittstelle (SQL/MED) gestattet den transparenten Zugriff und Manipulation externer Datenquellen in der gleichen Art und Weise wie mit PostgreSQL^®-eigenen Tabellen. Die externen Datenquellen erscheinen als Tabellen in der jeweiligen Datenbank und lassen sich in SQL-Anweisung uneingeschränkt verwenden. Der Zugriff wird über einen sogenannten Foreign Data Wrapper (FDW) implementiert, der die Schnittstelle zwischen PostgreSQL^® und der externen Datenquelle bildet. Der FDW ist auch dafür verantwortlich, Datentypen oder nicht-relationale Datenquellen entsprechend auf die Tabellenstruktur abzubilden. Dies ermöglicht somit auch die Anbindung von nicht-relationalen Datenquellen wie Hadoop, Redis u.a. Eine Übersicht einiger verfügbarer Foreign Data Wrapper gibt es im PostgreSQL^® Wiki.

Informix FDW

Im Kontext vieler Informixmigrationen entwickelte die credativ GmbH einen FDW für Informixdatenbanken. Dies unterstützt die Migration, aber auch die Integration von PostgreSQL^® in bestehende Informixinstallationen, um Datenaustausch und -verarbeitung zu erleichtern. Der Informix FDW unterstützt alle PostgreSQL^® Versionen mit SQL/MED Implementierung, also von 9.1 bis aktuell 9.3. Der Informix FDW unterstützt mit PostgreSQL^® 9.3 darüber hinaus schreibende Operationen auf Foreign Tables.

Installation

Die Installation setzt mindestens ein vorhandenes CSDK von IBM für Informix voraus. Das CSDK kann direkt per Download bezogen werden.  Im Folgenden gehen wir davon aus, dass eine CSDK-Installation in /opt/IBM/informix vorhanden ist. Der Pfad kann je nach Installation variieren. Die Quellen des Informix FDW lassen sich direkt vom credativ github Repository klonen, oder man besorgt sich ein Release Tarball (zum Zeitpunkt dieses Artikels Version 0.2.1). Die folgende Installationsbeschreibung geht von einem System mit CentOS 6 aus, kann jedoch mit Abweichungen auf jedem anderen Linuxsystem erfolgen.

% wget 'https://github.com/credativ/informix_fdw/archive/REL0_2_1.tar.gz'
% tar -xzf REL0_2_1.tar.gz

Anschließend lässt sich der FDW mit Angabe des CSDK-Installationsordners bauen:

% export INFORMIXDIR=/opt/IBM/informix
% export PATH=$INFORMIXDIR/bin:$PATH
% PG_CONFIG=/usr/pgsql-9.3/bin/pg_config make

Im Listing wurde die Umgebungsvariable PG_CONFIG explizit auf die PostgreSQL^® 9.3 Installation gesetzt. Es handelt sich hierbei um eine Installation von PGDG-RPM Paketen auf CentOS 6, in der pg_config außerhalb des Standardpfades liegt. Ferner muss das Paket postgresql93-devel installiert sein. Wurden alle Pfade richtig gesetzt kann der FDW installiert werden.

% PG_CONFIG=/usr/pgsql-9.3/bin/pg_config make install

Dies installiert alle notwendigen Bibliotheken. Damit diese korrekt funktionieren und beim Laden in die PostgreSQL^® Instanz ebenfalls die benötigten Informixbibliotheken finden, müssen letztere noch im Dynamic Linker des Systems konfiguriert werden. Ausgehend von der bereits angesprochenen CentOS 6 Plattform geschieht dies am einfachsten über eine zusätzliche Konfigurationsdatei in /etc/ld.so.conf.d (dies erfordert in diesem Fall root-Berechtigung!):

% vim /etc/ld.so.conf.d/informix.conf

Diese Datei sollte die Pfade zu den benötigten Informixbibliotheken enthalten:

/opt/IBM/informix/lib
/opt/IBM/informix/lib/esql

Anschließend muss der Cache des Dynamic Linker erneuert werden:

% ldconfig

Die Installation des Informix FDW sollte nun einsatzbereit sein.

Konfiguration

Um den Informix FDW in einer Datenbank verwenden zu können, muss der FDW in die betreffende Datenbank geladen werden. Der FDW ist eine sogenannte EXTENSION und diese werden mit dem CREATE EXTENSION Kommando geladen:

#= CREATE EXTENSION informix_fdw;
CREATE EXTENSION

Um eine Informixdatenbank über den Informix FDW nun anzubinden, benötigt man zunächst eine Definition, wie auf die externe Datenquelle zugegriffen werden soll. Hierzu erstellt man mit dem CREATE SERVER Kommando eine Definition mit dem vom Informix FDW benötigten Informationen. Zu beachten ist, dass die Optionen, die einer SERVER-Direktive mitgegeben werden, vom jeweiligen FDW abhängig ist. Für den Informix FDW benötigt man mindestens folgende Parameter:

• informixdir – Installationsverzeichnis des CSDK
• informixserver – Name der Serververbindung, die über das Informix CSDK konfiguriert wurde (siehe hierzu die Informix Dokumentation, bzw. $INFORMIXDIR/etc/sqlhosts)

Das Erstellen der SERVER-Definition erfolgt dann wie folgt:

=# CREATE SERVER centosifx_tcp
   FOREIGN DATA WRAPPER informix_fdw
   OPTIONS(informixdir '/opt/IBM/informix',
           informixserver 'ol_informix1210');

Die Variable informixserver ist der Instanzname der Informixinstanz, hier muss man einfach den Vorgaben der Informixinstallation folgen. Der nächste Schritt erzeugt nun ein sogenanntes Usermapping, mit der man eine PostgreSQL^®-Rolle (bzw. Benutzer) auf die Informixzugangsdaten bindet. Ist diese Rolle am PostgreSQL^®-Server angemeldet, so nutzt diese automatisch über das Usermapping die angegebenen Logininformation, um sich am Informixserver centosifx_tcp anzumelden.

=# CREATE USER MAPPING FOR bernd
   SERVER centosifx_tcp
   OPTIONS(username 'informix',
           password 'informix')

Auch hier sind die in der OPTIONS-Direktive spezifizierten Parameter vom jeweiligen FDW abhängig. Nun können sogenannte Foreign Tables zum Einbinden von Informixtabellen angelegt werden. Im folgenden Beispiel wird eine einfache Relation mit Strassennamen vom Informixserver ol_informix1210 in die PostgreSQL^®-Instanz eingebunden. Wichtig ist, dass die Konvertierungen von Datentypen von Informix zu PostgreSQL^® mit kompatiblen Datentypen erfolgt. Zunächst die Definition der Informixtabelle, wie sie in der Informixinstanz angelegt wurde:

CREATE TABLE osm_roads(id bigint primary key,
                       name varchar(255),
                       highway varchar(32),
                       area varchar(10));

Die Definition in PostgreSQL^® sollte analog erfolgen, können Datentypen nicht konvertiert werden wird eine Fehler beim Erzeugen der Foreign Table geworfen. Wichtig beim Konvertieren von Zeichenkettentypen wie bspw. varchar ist auch, dass beim Erzeugen der Foreign Table eine entsprechende Zielkonvertierung definiert wird. Der Informix FDW verlangt daher folgende Parameter beim Anlegen einer Foreign Table:

• client_locale – Locale des Clients (sollte identisch zum Server Encoding der PostgreSQL^®-Instanz sein)
• db_locale – Locale des Datenbankservers
• table oder query – Die Tabelle bzw. SQL Abfrage, auf der die Foreign Table basiert. Eine auf einer Abfrage basierende Foreign Table kann keine modifizierenden SQL-Operationen ausführen.

#= CREATE FOREIGN TABLE osm_roads(id bigint,
                                  name varchar(255),
                                  highway varchar(32),
                                  area varchar(10))
   SERVER centosifx_tcp
   OPTIONS(table 'osm_roads',
           database 'kettle',
           client_locale 'en_US.utf8',
           db_locale 'en_US.819');

Wer sich nicht sicher ist, wie die Standardlocale der PostgreSQL^® Datenbank aussehen, kann diese sich am einfachsten per SQL anzeigen lassen:

=# SELECT name, setting FROM pg_settings WHERE name IN ('lc_ctype', 'lc_collate');
    name    |  setting   
------------+------------
 lc_collate | en_US.utf8
 lc_ctype   | en_US.utf8
(2 rows)

Die Locale- und Encodingeinstellungen sollten unbedingt möglichst übereinstimmend gewählt werden. Sind alle Einstellungen korrekt gewählt worden, kann direkt über PostgreSQL^® auf die Datenbestände der Tabelle osm_roads zugegriffen werden:

=# SELECT id, name FROM osm_roads WHERE name = 'Albert-Einstein-Straße';
  id  |          name          
------+------------------------
 1002 | Albert-Einstein-Straße
(1 row)

Je nach Anzahl an Tupel in der Foreign Table kann das Selektieren eine signifikante Dauer aufweisen, da bei inkompatiblen WHERE-Klauseln die gesamte Datenmenge per Full Table Scan erst an den PostgreSQL^®-Server übermittelt werden muss. Der Informix FDW unterstützt jedoch Predicate Pushdown unter bestimmten Voraussetzungen. Darunter versteht man die Fähigkeit, Teile der WHERE-Bedingung, die die Foreign Table betreffen an den Remote Server zu übermitteln und dort bereits die Filterbedingung anzusetzen. Das erspart die Übertragung an sich nutzloser Tupel, da diese ja im PostgreSQL^® Server herausgefiltert werden würden. Obiges Beispiel sieht im Ausführungsplan beispielsweise so aus:

#= EXPLAIN (ANALYZE, VERBOSE)
   SELECT id, name 
   FROM osm_roads WHERE name = 'Albert-Einstein-Straße';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.osm_roads  (cost=2925.00..3481.13 rows=55613 width=24) (actual time=85.726..4324.341 rows=1 loops=1)
   Output: id, name
   Filter: ((osm_roads.name)::text = 'Albert-Einstein-Straße'::text)
   Rows Removed by Filter: 55612
   Informix costs: 2925.00
   Informix query: SELECT *, rowid FROM osm_roads
 Total runtime: 4325.351 ms

Die Filter-Anzeige in diesem Ausführungsplan zeigt, dass insgesamt 55612 Tuple ausgefiltert wurde, letztlich nur ein einziges Tupel wurde zurückgegeben, da es die Filterbedingung erfüllte. Das Problem liegt hier am impliziten Cast, den PostgreSQL^® in der WHERE-Bedingung über die Zeichenkettenspalte name legt. Aktuelle Versionen des Informix FDW berücksichtigen dies noch nicht. Jedoch können Prädikate an den Foreign Server übermittelt werden, wenn diese bspw. Ganzahltypen entsprechen:

#= EXPLAIN (ANALYZE, VERBOSE) SELECT id, name FROM osm_roads WHERE id = 1002;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.osm_roads  (cost=2925.00..2980.61 rows=5561 width=40) (actual time=15.849..16.410 rows=1 loops=1)
   Output: id, name
   Filter: (osm_roads.id = 1002)
   Informix costs: 2925.00
   Informix query: SELECT *, rowid FROM osm_roads WHERE   (id = 1002)
 Total runtime: 17.145 ms
(6 rows)

Datenmodifikation

Mit PostgreSQL^® 9.3 unterstützt der Informix FDW auch das Manipulieren (UPDATE), Löschen (DELETE) und Einfügen (INSERT) von Daten in die Foreign Table. Darüber hinaus interagieren Transaktionen, die im PostgreSQL^® Client gestartet wurden implizit mit Transaktionen auf dem Foreign Server (unter der Voraussetzung, dass die Informixdatenbank mit Logging angelegt wurde und daher transaktionsfähig ist). Folgendes Beispiel demonstriert diese Fähigkeit:

=# BEGIN;
BEGIN
 
*=# INSERT INTO osm_roads(id, name, highway) VALUES(55614, 'Hans-Mustermann-Straße', 'no');
INSERT 0 1
 
*=# SAVEPOINT A;
SAVEPOINT
 
*=# UPDATE osm_roads SET area = 'Nordrhein-Westfalen' WHERE id = 55614;
ERROR:  value too long for type character varying(10)
 
!=# ROLLBACK TO A;
ROLLBACK
 
*=# UPDATE osm_roads SET area = 'NRW' WHERE id = 55614;
UPDATE 1
 
*=# COMMIT;
COMMIT
 
=# SELECT * FROM osm_roads WHERE id = 55614;
  id   |          name          | highway | area 
-------+------------------------+---------+------
 55614 | Hans-Mustermann-Straße | no      | NRW
(1 row)

Das Beispiel erzeugt zunächst einen neuen Datensatz nach dem Start einer Transaktion. Anschließend wird ein SAVEPOINT gesetzt, um den aktuellen Zustand dieser Transaktion zu sichern. Im nächsten Schritt wird der neue Datensatz noch modifiziert, da man vergessen hatte, die Lokalität des neuen Datensatzes zu spezifizieren. Da jedoch nur Länderkürzel erlaubt sind, schlägt dies aufgrund der Überlänge des neuen Bezeichners fehl, die Transaktion wird ungültig. Durch den SAVEPOINT wird auf den zuletzt angelegten Sicherungspunkt der Transaktion zurückgerollt, der Informix FDW setzt ebenfalls implizit auf diesen SAVEPOINT auf den Foreign Server zurück. Anschließend kann innerhalb dieser Transaktion der korrekte Ländercode eingetragen werden. Nach dem COMMIT bestätigt der Informix FDW ebenfalls die Transaktion auf dem Foreign Server, der Datensatz ist korrekt eingetragen worden.

Zusammenfassung

Foreign Data Wrapper sind ein sehr leistungsfähiges und flexibles Werkzeug, um PostgreSQL^® in heterogenen Datenbanklandschaften zu integrieren. Dabei sind diese Schnittstellen keinesfalls auf reine relationale Datenquellen beschränkt. Mit der Unterstützung von modifizierenden SQL-Abfragen (DML) ermöglicht die FDW-API ferner auch die Integration schreibender Datenquellen in PostgreSQL^®-Datenbanken.