Neuerungen in PostgreSQL 8.4

Inhaltsverzeichnis

Seit der Einführung vierteljährlicher "Commit Fests" schreitet die Entwicklung von PostgreSQL zügig voran. Jetzt ist das freie Datenbanksystem ist Version 8.4 erschienen.

Im Laufe des vergangenen Jahres sind in die To-do-Liste des PostgreSQL-Entwicklerteams Hunderte von Patches eingeflossen. Sie bildet die Grundlage für den folgenden Überblick über die wichtigsten neuen Features in Version 8.4 der freien Datenbank.

Zahlreiche Verbesserungen bekam das Kommandozeilenprogramm psql spendiert. So ist die Hilfe freundlicher gestaltet, weil immer wieder Nutzer die Startmeldung schlicht ignorieren. Die überarbeitete Tabulator-Komplettierung zeigt jetzt alle Möglichkeiten an, nicht nur wie bisher eine Auswahl. Verschiedene Funktionen (zum Beispiel \d+) informieren über die Größe der Objekte in der Datenbank oder die Zugriffsrechte:

ix_84=# \dt+
		      Liste der Relationen
 Schema | Name	|  Typ	| Eigentümer |	 Größe	 ...
--------+-------+-------+------------+-----------...
 public | daten | table | ads	     | 8192 bytes

ix_84=# \l+
		      Liste der Datenbanken
  Name | Eigentümer | Kodierung | ...  Access Privileges   ...	       
-------+------------+-----------+-... ------------------------------ ...
 ix_84 | ads	    | UTF8	| ... {=Tc/ads,ads=CTc/ads,ix=C/ads} ...

Der Befehl zum Anzeigen der Rechte (\z) umbricht die unter Umständen recht lange Liste zur besseren Lesbarkeit nun:

	      Zugriffsrechte
 Schema | Name	|  Typ	| Zugriffsrechte
--------+-------+-------+-----------------
 public | daten | table | ads=arwdDxt/ads
			: ix=r/ads
			: u_84=r/ads

Mehr Bedienkomfort in psql

Verschiedene Schalter wie \timing lassen sich nicht mehr nur umschalten, sondern mit on oder off explizit auf den gewünschten Wert einstellen. \dT zum Anzeigen von Datentypen gibt die Werte eines ENUM an. psql beachtet die Umgebungsvariable $COLUMNS und passt die Breite der Ausgabe entsprechend an. Ein weiteres nützliches Detail: Das Programm listet bei einer Tabelle alle auf sie verweisenden Referenzen. Bisher musste man sie umständlich per Hand heraussuchen.

Der SQL-Befehl TRUNCATE zum Löschen aller Datensätze aus einer Tabelle löst alle operationsbezogenen Trigger aus (statement-based Trigger). Auf Datensätze (row-based) bezogene Trigger bleiben weiterhin unbeachtet. Sinn von TRUNCATE ist es gerade, das Auslösen der zeilenbezogenen Trigger zu umgehen.

TRUNCATE wertet die Option RESTART IDENTITY aus, indem es alle SEQUENCEs der Tabelle auf ihre Anfangswerte zurücksetzt. Anschließende INSERT-Befehle beginnen also wieder mit den ursprünglichen Werten der Sequenz. Außerdem gibt es das neue Recht truncate, das der DBA wie gehabt mit GRANT und REVOKE vergibt.

PostgreSQL kennt neben Funktionen mit OUT- und INOUT-Parametern solche, die TABLE(...) zurückliefern. Dieses Verhalten entspricht dem SQL:2003-Standard. Des Weiteren können Funktionen eine variable Anzahl von Parametern akzeptieren. Das spart umständliche Wrapper, die nur wieder "die eine" Funktion mit passenden Parametern aufrufen. Den Spaß beim Erstellen von Funktionen vervollständigen Default-Parameter, die beim Aufruf fehlende Werte ersetzen.

Zugriffsrechte

Zugriffsrechte für einzelne Spalten

Bisher ließen sich Spalten zu einem VIEW nur durch sein Löschen und Neuerstellen hinzufügen. In Zukunft reicht ein CREATE OR REPLACE VIEW dafür. Allerdings dürfen sich die bisherigen Spalten, ihre Reihenfolge und die Datentypen nicht ändern.

Ab Version 8.4 bietet PostgreSQL spaltenorientierte Zugriffsrechte. Ein einzelnes Recht wie SELECT oder DELETE gilt dabei nicht nur für die ganze Tabelle, sondern auch für einzelne Spalten. Die Tabellenrechte haben Vorrang, erst wenn kein passendes Recht auf Tabellenebene vorhanden ist, wertet die Datenbank die spaltenbasierten Rechte aus. Im folgenden Beispiel bekommen alle Nutzer (PUBLIC) nur Zugriff auf den Benutzernamen und die ID, das Passwort bleibt geheim:

CREATE TABLE benutzer
  (id SERIAL, benutzername TEXT, passwort TEXT);
REVOKE SELECT ON benutzer FROM public; 
GRANT SELECT (id, benutzername) ON benutzer TO public;

Arrays haben die Entwickler mit einigen nützliche Funktionen ausgerüstet: array_fill() belegt Werte vor;

SELECT array_fill(23, ARRAY[4]); 

etwa gibt ein Array aus, dessen vier Elemente mit 23 besetzt sind.

Die Funktion array_length() ermittelt die Größe eines (mehrdimensionalen) Array. Im folgenden Beispiel wählt 1 die erste Dimension aus

SELECT array_length(ARRAY[1,2,3], 1);

sodass das Ergebnis der Abfrage 3 lautet.

Windowing Functions

Eine der größten Detailverbesserungen sind "Windowing Functions" beziehungsweise "Common Table Expressions". Damit lassen sich umfangreiche Abfragen erstellen, wie sie bei Data-Warehouse-Anwendungen üblich sind. Auch rekursive Anfragen und Baumstrukturen stellen mit dieser Neuerung keine Hürde mehr dar, die neuen Möglichkeiten sind sehr vielfältig. Das Beispiel im folgenden Listing erzeugt eine Tabelle mit abhängigen Einträgen: ein Land, Bundesländer, Orte, Ortsteile. Jede Angabe gehört zu einem übergeordneten Element, nur das Land bildet die Spitze des Baumes und hat keinen Elterneintrag. Die Anfrage liest die Daten rekursiv aus und sortiert sie gleichzeitig in den Baum ein. Als Ergebnis erhält man eine Liste mit Ortsbezeichnungen, in der die abhängigen Einträge jeweils unter dem Elterneintrag stehen.

CREATE TABLE orte (
  id INT NOT NULL PRIMARY KEY,
  gehoert_zu INT,
  name TEXT NOT NULL UNIQUE
);
-- irgendwo muss ein Baum beginnen
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (1, NULL, 'Deutschland');
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (2, 1, 'Sachsen-Anhalt');
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (3, 1, 'Niedersachsen');
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (4, 2, 'Magdeburg');
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (5, 4, 'Magdeburg/Hasselbachplatz');
INSERT INTO orte (id, gehoert_zu, name)
     VALUES (6, 3, 'Hannover');
WITH RECURSIVE rekursion(eintrag, name, pfad) AS (
  -- selektiere erst mal alles aus Deutschland
  -- das ist der Kopf des Baumes, ohne Zugehoerigkeit  SELECT id, name,
ARRAY[id] FROM orte WHERE gehoert_zu IS NULL
  UNION ALL
  -- hier die Rekursion erzeugen
  SELECT o.id, o.name,
	 rekursion.pfad || ARRAY[o.id]
    FROM orte o
    JOIN rekursion ON (o.gehoert_zu = rekursion.eintrag)
   WHERE id NOT IN (rekursion.eintrag)
)
  SELECT *
    FROM rekursion
ORDER BY pfad;
 eintrag |	     name	     |	 pfad
---------+---------------------------+-----------
       1 | Deutschland		     | {1}
       2 | Sachsen-Anhalt	     | {1,2}
       4 | Magdeburg		     | {1,2,4}
       5 | Magdeburg/Hasselbachplatz | {1,2,4,5}
       3 | Niedersachsen	     | {1,3}
       6 | Hannover		     | {1,3,6}

Die integrierte Programmiersprache pl/pgSQL hat einige nützliche Detailverbesserungen erfahren. So lassen sich mit CASE-WHEN-Statements zahllose IF-THEN-Zeilen vermeiden. Prozeduren können Prepared Statements in dynamisch generierten Anfragen erzeugen und dort Parameter mittels USING beim Ausführen einfügen.

Dynamische Abfragen mit Parametern

Im Zusammenhang mit dem ebenfalls neuen RETURN QUERY können Entwickler Funktionen wesentlich kürzer und übersichtlicher schreiben. Das folgende Listing zeigt den Einsatz von USING anhand eines Beispiels, das übergebenen Text (enthält Hochkommas) als Parameter einfach in die Anfrage einfügt. Da die Bearbeitung mit quote_literal() zum Schutz der Hochkommas entfällt, erhöhen sich die Lesbarkeit und Sicherheit der Funktion.

CREATE OR REPLACE FUNCTION using_test (TEXT, TEXT)
       RETURNS TEXT
AS $$
DECLARE
    param1 ALIAS FOR $1;
    param2 ALIAS FOR $2;
    anfrage TEXT;
BEGIN
    RAISE NOTICE 'Nutze Parameter: %, %', param1, param2
    USING detail = 'Parameter werden ausgegeben',
	  hint = 'Auf Hochkommas achten';
    EXECUTE 'SELECT $1 || ' ' || $2' 
       INTO anfrage USING param1, param2;
    RETURN anfrage;
END;
$$
LANGUAGE 'plpgsql';
SELECT using_test(E'O\'Reilly', 'Bücher');
NOTICE:  Nutze Parameter: O'Reilly, Bücher
DETAIL:  Parameter werden ausgegeben
TIP:  Auf Hochkommas achten
   using_test
-----------------
 O'Reilly Bücher

Benutzern können Funktionen nun mehr Informationen geben. Dazu lassen sich mit RAISE-Optionen weitere Details ausgeben. Das kann ein Hinweis (hint), der Fehlercode oder ein Log-Eintrag sein. Das zuvor gezeigte Listing nutzt diese Erweiterung und gibt zur Laufzeit einige nützliche Hinweise aus.

Der neue Konfigurationsparameter track_functions veranlasst den Server, die Häufigkeit und Gesamtlaufzeit von Funktionsaufrufen in der Systemtabelle pg_stat_user_functions zu protokollieren. Den Zugriff darauf vereinfachen einige Hilfsfunktionen.

Wie jede größere Release dreht 8.4 an der Performance-Schraube. Statistikdaten schreibt der Server in der Regel einmal pro Minute (durch Autovacuum ausgelöst) oder wenn ein Benutzer Statistiken abruft. Vorher erledigte er das zweimal pro Sekunde, meistens viel zu häufig. Außerdem gab es viele Verbesserungen im Planer, um Anfragen effektiver zu berechnen und durchzuführen.

Wenn vorhanden, nutzt PostgreSQL die POSIX-Funktion fadvise(). Damit teilt es dem Betriebssystem mit, wie es Daten von den Festplatten voraussichtlich lesen wird, zum Beispiel sequenziell oder wahlfrei.

SELECT DISTINCT arbeitet jetzt schneller und verliert damit seinen Nachteil gegenüber GROUP BY. Allerdings benötigt die Klausel immer ein ORDER BY, wenn sortierte Ausgaben gewünscht sind. Das funktionierte bei SELECT DISTINCT bisher auch ohne diese Angabe, aber ab 8.4 sind die Ergebnisse nicht mehr von Haus aus sortiert – das soll mehr Tempo bringen. Wenn sich Applikationen also auf die geordnete Ausgabe verlassen, ist hier Nacharbeit angesagt.

Geschwindigkeit

Mehr Tempo für den Staubsauger

Bisher waren die beiden Parameter max_fsm_pages und max_fsm_relations eine Quelle für schlechte VACUUM-Performance, da die Default-Konfiguration beide Werte recht niedrig setzt. Für PostgreSQL 8.4 schrieben die Entwickler den Algorithmus der Free Space Map komplett um und ließen beide Parameter fallen. Den notwendigen Speicherplatz für die Verwaltung reserviert PostgreSQL dynamisch. Außerdem bilden diese Änderungen die Grundlage für ein Feature in einer späteren Version: Mit dem Code können sogenannte "visibility maps" abgebildet werden, die VACUUM wiederum genauer informieren, welche Dateiblöcke es überhaupt bearbeiten muss.

Eine komplette Datenbank kann der DBA mit PostgreSQL 8.4 auf einen anderen Tablespace verschieben:

ALTER DATABASE <datenbankname> SET
  TABLESPACE <tablespace name>;

In der Datei pg_hba.conf dürfen statt Benutzernamen in Zukunft reguläre Ausdrücke stehen. Damit lassen sich zum Beispiel Benutzergruppen anhand einer Gruppenkennung im Namen erkennen. Das Neuladen von pg_hba.conf nach einer Änderung scheitert jetzt bei einem Fehler, und die alte Version bleibt aktiv. Wie bislang startet die Datenbank jedoch nicht, wenn die Datei bereits einen Fehler enthält.

Da SSL-Verbindungen jetzt auch Client-Zertifikate akzeptieren, ist beidseitige Authentifizierung und Verschlüsselung möglich. Der neue Konfigurationsparameter IntervalStyle erlaubt die Formatierung von Intervallen (das Ergebnis einer Operation mit zwei Zeiten) entsprechend dem SQL-Standard. Die Ausgabe ändert sich danach zum Beispiel von "377 days 19:03:00" oder "@ 377 days 19 hours 3 mins" in "377 19:03:00".

Als Contrib-Modul ist auto_explain hinzugekommen. Einmal geladen, schreibt es für jede Anfrage, die länger läuft als die in log_min_duration eingestellte Zeit, automatisch ein EXPLAIN in die Log-Datei. Das vereinfacht das Identifizieren und Analysieren langsamer Abfragen erheblich.

Ende der crypt-Verschlüsselung

Das Ende von crypt

PostgreSQL unterstützt nicht länger die crypt()-Verschlüsselung. Diese Methode sollte nur noch mit ganz alten Datenbanken (7.3 und früher) zum Einsatz kommen.

psql erlaubte bisher, dass einem einbuchstabigen Parameter die Argumente direkt und ohne Leerzeichen folgten. Diese Möglichkeit erwähnte die Dokumentation schon seit mehreren Versionen nicht, und der entsprechende Code ist jetzt komplett entfernt. Deshalb ältere Skripte überprüfen, ob sie noch dieses Format verwenden.

Nun können Anwender und Entwickler also etliche Verbesserungen nutzen. Die inkompatiblen Änderungen gegenüber der Vorgängerversion dürften zu verschmerzen sein.

Andreas Scherbaum
beschäftigt sich seit 1999 mit PostgreSQL. Seit 2004 ist er mit seinem eigenen Unternehmen in diesem Bereich tätig.

Kleinere Änderungen

Kleinere Änderungen in PostgreSQL 8.4

pg_dump und pg_dumpall sind nicht mehr von einem konfigurierten Statement Timeout betroffen. Diese Einstellung konnte vorher lästige Probleme während eines Backups verursachen. Außerdem kennt pg_dump die neue Option lock-wait-timeout; dies bricht die Ausführung ab, wenn nach der eingestellten Zeit noch nicht alle notwendigen LOCKs vorliegen.

Mit pg_get_keywords() erhält man eine Liste aller reservierten Schlüsselwörter. Ursprünglich für das Verwaltungswerkzeug pgAdminIII geschrieben, können jetzt andere Anwendungen ebenfalls Funktionen wie Autovervollständigung oder Syntax-Hervorhebung anbieten.

Die Systemtabelle pg_settings enthält nicht mehr nur die aktuellen Einstellungen für einen Konfigurationsparameter, sondern eine weitere Spalte (reset_val) mit dem Default-Wert. Das hilft, auf die Schnelle Änderungen an der Konfiguration festzustellen. Außerdem sind die Spalten sourcefile und sourceline hinzugekommen. Sie geben Konfigurationsdatei und die Zeile in ihr an, in der der Wert gesetzt wird.

GIN-Indizes (Generalized Inverted Index) beherrschen jetzt partielle Suchen. Das ist für die Volltextsuche sinnvoll, da sie nun Wortteile zumindest am Wortanfang findet. Des Weiteren sind GIN-Indizes über mehrere Spalten hinzugekommen sowie ein Mechanismus zum Einfügen größerer Datenmengen.

Das Contrib-Modul citext enthält einen Datentyp, der Text unabhängig von Groß-/Kleinschreibung darstellen kann. Dies vermeidet umständliche Aufrufe von LOWER() für Textspalten, bei denen die Schreibweise keine Rolle spielt (zum Beispiel Benutzernamen oder E-Mail-Adressen). Timestamps sind jetzt lokalisiert möglich und der Prä-Compiler ECPG für eingebettete SQL-Statements spricht neuerdings mehrere Sprachen. (ane)