(English version)
In verschiedenen Projekten habe ich mich genauer mit den Optionen des PostgreSQL-JDBC-Treibers beschäftigen müssen und damit konkrete Probleme von Java-Anwendungen, unter anderem RapidMiner gelöst. Ich möchte meine Erfahrungen hier teilen und einige der JDBC-Parameter genauer beschreiben.
defaultRowFetchSize (default: 0)
Dieser Parameter steuert die Anzahl der auf einmal angeforderten Zeilen in einem ResultSet. Der Vorgabewert von 0 ergibt eine gute Performance, solange das Abfrageergebnis nicht zu groß wird. Wenn aber sehr viele Daten zurückgegeben werden, die das Programm dann nicht mal braucht, kann der Speicher des Java-Programms (oder RapidMiner Studio) vollständig verbraucht werden.
Meine Empfehlung: zur Sicherheit eine hohe, aber noch sinnvoll in den Speicher passende Zeilenanzahl wählen, z. B. 10.000 oder 100.000. Dadurch wird nicht alles gleich aus der Datenbank geladen, sondern erst wenn die Anwendung die nachfolgende Zeile anfordert.
RapidMiner lädt die Abfrageergebnisse komplett in den Speicher, diese Einstellung bewirkt also nicht so viel wie bei einem Programm, das die Daten sequenziell liest und verarbeitet. Sie kann aber beim Einschränken des Speicherverbrauchs eines Prozesses helfen, und auch bei Verwendung von Stream Database.
stringtype (default: VARCHAR)
Prepared Statements sind bei der Entwicklung von Datenbankanwendungen sehr empfehlenswert. Auch RapidMiner verwendet sie z. B. in Write Database, Update Database und optional in Read Database.
Diese Statements haben folgende Form:
SELECT a, b, c FROM tabelle WHERE c1 = ? and c2 = ?;
INSERT INTO tabelle (a, b, c) VALUES (?, ?, ?);
Für jedes Fragezeichen wird ein Wert mit Datentyp eingegeben. Das vermeidet Typkonversionsfehler und SQL Injections, die sonst viel zu leicht auftreten (das ‚O’Connor‘-Problem).
stringtype = VARCHAR (die Standardeinstellung) bewirkt, daß PostgreSQL als String übergebene Parameter explizit als VARCHAR betrachtet. Das verursacht in einigen Situationen Probleme:
SELECT ... WHERE datumsfeld = ?; -- Datum als String übergeben
INSERT INTO booltabelle (boolfeld) VALUES (?); -- 'true' oder 'false' als String übergeben
Meine Empfehlung: stringtype = unspecified. Das erlaubt z. B. in RapidMiner die Verwendung von korrekt formatierten Datumsangaben in Nominal-Feldern, oder auch von Booleans (true/false) mit Write Database. Es hilft auch bei Legacy-Anwendungen, die mit einer anderen Datenbank entwickelt wurden, und z. B. Zeitstempel als Text an die Datenbank übergeben.
Es wären Situationen denkbar, in denen die Einstellung „unspecified“ Probleme verursachen kann, z. B. wenn die Eingabe nicht auf korrekt formatierte Daten eingeschränkt ist. Dann würde nicht schon das erste Einfügen fehlschlagen, sondern erst der Datensatz mit den falschen Daten. Die Ergebnisse in der Datenbank wären also unvollständig.
ApplicationName
Dieser Parameter kann mit einem beliebigen String belegt werden. Für Datenbank-Admins hilft es manchmal zu wissen, welche Anwendung und welcher User z. B. eine aufwändige Abfrage ausführt.
Meine Empfehlung: Den Namen der eigenen Anwendung eintragen.
currentSchema
Mit diesem Parameter kann der Suchpfad (search_path) festgelegt werden. PostgreSQL sucht standardmäßig in den Schemata pg_catalog, $user und public. Wenn die eigenen Tabellen z. B. im Schema „datamining“ liegen, können sie mit currentSchema=datamining direkt angesprochen werden.
Diese Einstellung kann in speziellen Situationen helfen, wenn z. B. eine migrierte Anwendung ein eigenes Schema verwendet und nicht darauf vorbereitet ist, Schema-Namen anzugeben.
Meine Empfehlung: Nur in begründeten Sonderfällen ändern. Die Änderung von search_path bewirkt, daß die JDBC-Verbindung sich anders verhält als z. B. ein SQL-Client (solange man diesen nicht auch umstellt). Das kann zu subtilen, erst später bemerkten Fehlern führen.
Angabe der JDBC-Parameter
Im Allgemeinen werden die Parameter an die URL der JDBC-Verbindung angehängt, z. B. so:
jdbc:postgresql://server/database?stringtype=unspecified&defaultRowFetchSize=10000&ApplicationName=user@RapidMiner
RapidMiner bietet in Manage Database Connections unter Advanced eine Liste der einstellbaren Parameter, die aber defaultRowFetchSize nicht anbietet. Solche Parameter kann man einfach an den Inhalt des Feldes „Database scheme“ anhängen und die URL überprüfen.
PostgreSQL JDBC parameters for RapidMiner and other software
Some of my projects required working with parameters of the PostgreSQL JDBC driver and using them to solve problems in Java applications like RapidMiner. I’d like to share what I learned and describe some of the parameters in detail.
defaultRowFetchSize (default: 0)
This parameter sets the number of rows that are fetched together in a ResultSet. The default value of 0 results in a good performance as long as the query result doesn’t become too large. But if the query returns a huge amount of data, the memory usage of the Java application (e. g. RapidMiner Studio) grows by a large amount.
I recommend setting a large number that limits the memory requirements, for example 10,000 or 100,000 according to the capabilities of the server and the application. This limits the number of rows fetched from the database initially. The subsequent rows will be fetched when requested by the application.
RapidMiner loads query results into the memory anyway, so this setting doesn’t change its behaviour much. Still, it can help limiting the memory usage of a process, and when using Stream Database.
stringtype (default: VARCHAR)
The recommended way to write SQL statements in application development is to use prepared statements. RapidMiner uses these in Write Database, Update Database and optionally in Read Database.
Prepared statements look like this:
SELECT a, b, c FROM tablename WHERE c1 = ? and c2 = ?;
INSERT INTO tablename (a, b, c) VALUES (?, ?, ?);
Each question mark gets a typed value assigned. This avoids type conversion errors and SQL injections that are all too easy to make otherwise (the ‚O’Connor‘ problem).
The default setting stringtype=VARCHAR forces PostgreSQL to always interpret string parameters with the VARCHAR type. This causes problems in some situations:
SELECT ... WHERE datecolumn = ?; -- date specified as string
INSERT INTO booltable (boolcolumn) VALUES (?); -- 'true' or 'false' strings
I recommend setting stringtype=unspecified. This adds flexibility e. g. in RapidMiner, for example nominal fields with dates or booleans (true/false) with the Write Database operator. It also helps with legacy applications developed with a different database in mind, that use strings to represent timestamps in the database.
There might be situations where „unspecified“ is not the right setting. If the input is not always correct, the process would only fail when encountering the wrong data, so it might end up with an incomplete data set in the database.
ApplicationName
This parameter takes an arbitrary string. It is helpful for database admins so they can see the application and the user behind long-running queries.
I recommend setting the name of the application in this parameter.
currentSchema
This parameter changes the search_path of the database. PostgreSQL searches for non-schema qualified objects in the following schemas: pg_catalog, $user, public. If your tables are in the schema „datamining“, you can set currentSchema=datamining so you can refer to them directly.
This setting can be useful in migration settings, e. g. when the legacy application uses tables in a schema but isn’t prepared to specify the schema name in queries.
I recommend leaving this setting empty and only changing it when required. When you change search_path, your JDBC connection in the application will work differently from e. g. the JDBC client (if that is left with the default settings). This might cause subtle problems later.
Setting JDBC parameters
Usually you can simply append the parameters to the JDBC connection URL like this:
jdbc:postgresql://server/database?stringtype=unspecified&defaultRowFetchSize=10000&ApplicationName=user@RapidMiner
RapidMiner offers a list of parameters in Manage Database Connections with the Advanced button. This list doesn’t offer defaultRowFetchSize, however. You can still specify this parameter by appending it to the „Database scheme“ input field and checking the URL below.
