4.1 psql

Der bereits kurz erwähnt interaktive Kommandointerpreter ist sicherlich das wichtigste Programm.

4.2 pgaccess

pgaccess ist eine graphisches Frontend, mit dem etliche Standardaufgaben erledigt werden können. Das Anlegen von Tabellen beispielsweise macht sich mit diesem Frontend wesentlich besser, also mit psql .

Über das Menü kann man zu einer Datenbank verbinden. Im folgenden Dialog können Server- und Datenbankname sowie ein Benutzerkonto angegeben werden.

Im Hauptfenster kann man rechts die anzuzeigende Objekte wählen. Hier kann man beispielsweise zwischen Tabellen, Views und Sequenzen auswählen. Im linken Teil werden dann die entsprechenden Objekte aufgelistet und können ausgewählt werden.

Nach einem Doppelklick auf eine Tabelle bekommt man ein Fenster, in dem der Inhalt dargestellt wird und geändert werden kann. Klickt man eine Tabelle nur einmal an, so kann man weitere Funktionen anwenden, beispielsweise Design. Hier öffnet sich ein Fenster, in dem man komfortabel Indizes hinzufügen kann oder neue Felder anhängen kann.

Seit Version 7.3 ist pgaccess nicht mehr Bestandteil der PostgreSQL- Distribution und muss separat von  http://www.pgaccess.org besorgt werden.

4.3 RedHat Database Admin

RedHat vertreibt eine eigene Version von PostgreSQL. Diese entspricht ungefähr der Version 7.2.3 und ist unter  http://www.redhat.com/software/database/ erhältlich. RedHat stellt alle Änderungen am DBMS und auch sein graphisches Administrationsfrontend unter die GPL. Dieses läuft auch mit einer konventionellen PostgreSQL Installation und ist unter  http://sources.redhat.com/rhdb/ zu finden. Es ist hübscher als pgaccess und bietet im Bereich der Verwaltung mehr Optionen als dieses, kann dafür aber nicht zur Definition von TCL-Formularen herangezogen werden.

Die neuen Funktionen der Version 7.3 (Schemata) werden allerdings noch nicht unterstützt.

4.4 phpPgAdmin

Dies ist ein Webfrontend und setzt einen Webbrowser voraus. Dieses Frontend verfügt über sehr viele nützliche Funktionen. Tabellendaten können als HTML Tabelle betrachtet und editiert werden, beliebige Abfragen können erstellt und ausgeführt werden.

Tabellen selbst können einfach und komfortabel bearbeitet werden, so können neue Felder hinzugefügt oder gelöscht werden. Weiterhin stehen Kopier- und Dumpfunktionen bereit. Auch Berechtigungen können komfortabel verwaltet werden. Die zur Verfügung stehenden Optionen sind sinnvoll in Auswahlfeldern aufgelistet. Bei Bedarf ist es auch möglich, eigene SQL Kommandos einzugeben und ausführen zu lassen.

Eine weitere schöne Funktion ist die Verlinkung zu jeweils passenden Seiten der PostgreSQL Dokumentation.

Wer Webfrontends mag, wird dieses Frontend wohl lieben. Es lohnt sich allemal, sich dieses zu installieren. Natürlich muss unbedingt darauf geachtet werden, den Zugang zu diesem Frontend zu schützen, da der Zugriff auf das Frontend Zugriff auf die Datenbank gestattet - und zwar als Superuser!

4.5 Transaktionen

Dieser Abschnitt geht kurz auf Transaktionen ein. Transaktionen sind notwendig, um Änderungen atomar, dass heißt, ganz oder gar nicht, durchführen zu können.

Im Folgenden wird oft der englische Ausdruck lock verwendet. Wörtlich übersetzt bedeutet er in etwa sperren. Hier ist gemeint, ein Objekt so zu benutzen, dass es niemand anders gleichzeitig benutzen kann. Lock wird später noch genauer erklärt.

4.6 Variablen und Zeitzonen

Es gibt einige Variablen, die das Verhalten des DBMS (für den entsprechenden Clienten) beeinflussen. Über Variablen wird beispielsweise gesteuert, wie Datumsangaben aussehen. Dies ist nicht standard konform (mit Ausnahme von TIME ZONE, hier wurde der Standard erweitert).

Variablen werden mit SET gesetzt und mit SHOW abgefragt. Mit SET wird eine Variable auf einen Wert gesetzt. Zwischen der Variable und dem Wert steht TO (oder ein Gleichheitszeichen).

Hier werden nur zwei wichtige Variablen erwähnt. Die Variable DATESTYLE setzt die Form der Datumsrepräsentation. Mögliche Werte sind German, ISO und andere.

Auch die Zeitzone kann man setzen. Hier verwendet man SET TIME ZONE. ANSI erlaubt als Parameter nur eine Zahl, beispielsweise SET TIME ZONE 2. Dies ist natürlich ungünstig, da die Sommer- und Winterzeit Unterscheidung von der Anwendung getroffen werden muss (Ist Berlin nun gerade -1 oder -2? Das hängt vom Datum ab!). PostgreSQL erlaubt jedoch auch SET TIME ZONE 'Europe/Berlin'.

An einem Beispiel wird gezeigt, wie man die aktuelle Uhrzeit mit Datum in Californien (Zeitzone PST8PDT) im ISO Format (amerikanische Notation) und in Berlin (Zone CET, Central European Time, deutsche Notation) ausgeben lassen kann.

4.7 Datentypen

PostgreSQL unterstützt unter anderem die SQL92 Datentypen. Ingesammt werden viele Typen unterstützt und eigene können definiert werden. Beispiele sind int (Ganzzahlen), double precision (8 Byte Fließkomma), serial (Autoinkrementeller int), varchar (variable lange Zeichenketten), bytea (Binäre Zeichenkette, wie ANSI BLOB), timestamp (Datum und Uhrzeit), boolean (Wahrheitswert) und viele andere.

Typ-Umwandlungen werden durchgeführt, in dem man den Zieltyp durch zwei Doppelpunkte :: getrennt an den Typ anfügt: '123'::int.
Dies konvertiert die Zeichenkette 123 in einen Ganzzahltyp mit dem Wert einhundertdreiundzwanzig.

4.8 Operatoren

Neben den normalen Operatoren (OR, AND, +, -, *, || usw.) gibt viele weitere, beispielsweise Quadratwurzel (|/), LIKE und ILIKE (Patternmatching wie bei LIKE, aber case-insensitiv) auch reguläres Patternmatching (~, ~* und andere). Die Operatoren verhalten sich je nach Datentyp korrekt. Addiert man mit dem Operator + beispielsweise ein timestamp und ein intervall (also now() + intervall '2 hours'), kommt das erwartete Ergebnis heraus.

4.9 Vordefinierte FunktioneN

PostgreSQL stellt viele Funktionen bereit. Viele mathematische Funktionen sind verfügbar (sin(), cos(), abs(), random() usw). Daneben gibt es viele Zeichenkettenfunktionen (lower(), substring(), initcap(), translate(), encode(), um nur einige zu nennen). Auch die Zeit- und Datumsfunktionen sind sehr interessant und leistungsfähig. Beispielsweise gibt es current_timestamp (oder auch kurz now, eine klassische PostgreSQL-Erweiterung), extract (liefert Datumsteile, SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); führt also zu 38) und age (berechnet die Differenz zwischen zwei Zeitstempeln).

4.10 Datenbanken

Das Erzeugen und Planen von Datenbanken findet sich im Abschnitt  Administration.

4.11 Tabellen

Wie in jedem anderen RDBMS werden natürlich auch Tabellen unterstützt. Diese werden mit CREATE TABLE erzeugt. Dieses Kommando ist gut ANSI konform. Es gibt temporäre Tabellen, die automatisch gelöscht werden. Tabellen und Spalten können Bedingungen besitzen, das sind beispielsweise Funktionen, die es verhindern können, sinnlose Daten einzutragen (2 stellige Postleitzahlen beispielsweise). Wie bereits in  Arbeiten mit Bedingungen genannt, können die Prüfungen gegebenenfalls auf das Transaktionsende verschoben werden.

Fremdschlüssel sind Sonderformen von Bedingungen und werden auch unterstützt. Hiermit kann man gewährleisten, dass in eine Tabellenspalte nur solche Werte eingetragen werden können, die bereits in der Spalte einer anderen Tabelle definiert sind. Hat man z.B. eine Tabelle mit Herstellern und eine mit Teilen, in welcher der Hersteller vermerkt wird, kann sichergestellt werden, dass kein ungültiger Hersteller in letzterer eingetragen wird). Bei Fremdschlüsseln kann beispielsweise eine Aktion angegeben werden, die ausgeführt werden soll, falls der Fremdschlüssel gelöscht wird: NO ACTION, RESTRICT (dann ist das ein Fehler), CASCADE (die den Schlüssel referenzierenden Datensätze auch automatisch löschen, Vorsicht, dass können dann evtl. eine ganze Menge sein!), SET NULL (Wert auf NULL setzen), SET DEFAULT (auf Voreinstellung setzen).

-- Eine Tabelle mit Primärschlüssel und einfachem Aufbau
CREATE TABLE films (
    code             CHARACTER(5) CONSTRAINT films_pkey PRIMARY KEY,
    title            CHARACTER VARYING(40) NOT NULL,
    distributors_id  DECIMAL(3) NOT NULL,
    date_prod        DATE,
    kind             CHAR(10),
    len              INTERVAL HOUR TO MINUTE
);
-- Beispieldatensatz
INSERT INTO films (code, title, distributors_id) VALUES ('FilmA', 'Der Film A', 123);

-- Eine Tabelle mit einem Autoinkrement und einer einfachen Bedingung
CREATE TABLE distributors (
     id     DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'),
     name   VARCHAR(40) NOT NULL CHECK (name <> '')
);


-- Ein Tabelle mit Bedingung (distributors_id muss größer als 100 sein, der Name 
-- darf nicht leer sein, sonst gibt es einen Fehler
-- Das Feld modtime wird automatisch auf "jetzt" gesetzt, wenn ein
-- Datensatz eingefügt wird.
CREATE OR REPLACE TABLE distributors (
    id               DECIMAL(3) UNIQUE,
    name             VARCHAR(40),
    modtime          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    CONSTRAINT cst_valid_distributors_id CHECK (id > 100 AND name <> '')
);
-- Datensatz einfügen:
INSERT INTO distributors (id, name) VALUES (123, 'Name');
-- Nochmal geht schief, weil id eindeutig sein muss

-- Das geht auch schief:
-- INSERT INTO distributors (id, name) VALUES (001, 'Name');
--   denn: "ExecAppend: rejected due to CHECK constraint cst_valid_distributors_id"
--   (muss ja > 100 sein)


-- Eine Tabelle mit Fremdschlüssel und benannten Bedingungen.
-- "varchar" heißt einfach: kann beliebig lang werden (also fast,
--   bei ca 1000 MB ist Ende)
CREATE TABLE lager (
    id               SERIAL PRIMARY KEY,
    films_code       CHARACTER(5),
    distributors_id  DECIMAL(3),
    info             VARCHAR DEFAULT NULL,
    CONSTRAINT fk_lager_distributors_id FOREIGN KEY (distributors_id) REFERENCES distributors(id) 
        ON DELETE RESTRICT,
    CONSTRAINT fk_lager_films_code FOREIGN KEY (films_code) REFERENCES films(code) 
        ON DELETE RESTRICT DEFERRABLE 
);
-- Datensatz einfügen
INSERT INTO lager (id, films_code, info) VALUES (123, 'FilmA', 'hallo');

-- Das geht schief:
-- INSERT INTO lager (id, films_code, info) VALUES (124, 'FilmA', 'hallo');
--   denn: "fk_lager_distributors_id referential integrity violation 
--         - key referenced from lager not found in distributors"

-- Das geht auch schief:
-- DELETE FROM distributors;
--   denn: "fk_lager_distributors_id referential integrity violation 
--         - key in distributors still referenced from lager"

Tabellen können mit dem Kommando ALTER TABLE geändert werden. Diese Kommando hat viele Formen.

Einige Beispiele:

-- Eine Spalte anfügen:
ALTER TABLE lager ADD COLUMN plz VARCHAR(8);

-- Eine Spalte ändern:
ALTER TABLE lager ALTER COLUMN plz SET DEFAULT 'unsortiert';

-- Eine Spalte umbennen:
ALTER TABLE lager RENAME COLUMN plz TO zipcode;

-- Bedingung hinzufügen (PLZ muss fünfstellig sein)
ALTER TABLE lager ADD CONSTRAINT cst_zipchk CHECK (char_length(zipcode) = 5);

-- Bedingung entfernen
ALTER TABLE lager DROP CONSTRAINT cst_zipchk RESTRICT;

-- Tabelle umbennnen
ALTER TABLE lager RENAME TO lagermitplz;
ALTER TABLE lagermitplz RENAME TO lager;

-- Eigentümer ändern
ALTER TABLE lager OWNER TO steffen;
	 

Ab Version 7.3 wird endlich auch SQL 92 ALTER TABLE DROP COLUMN unterstützt. Gibt es einen Index, eine Bedingung oder einen Fremdschlüssel der die zu löschende Spalte referenziert, muss die Option CASCADE mit angegeben werden.

Für ältere Versionen hat sich folgende Vorgehensweise bewährt: Man muss die Tabelle neu erzeugen. Diese Funktion wird übrigens von phpPgAdmin unterstützt (das heißt, es gibt einen DROP Knopf, der im Prinzip das tut). Im Folgenden wird ein Workaround gezeigt. Es werden hier gleich noch ein paar weitere Kommandos demonstriert.

-- Workaround für fehlendes:
-- ALTER TABLE lager DROP COLUMN zipcode;

-- Daten in Temp-Tabelle:

BEGIN;

-- Tabelle exklusiv schützen:
LOCK TABLE lager IN ACCESS EXCLUSIVE MODE;
-- LOCK TABLE lager; macht das gleiche (Voreinstellung ist ACCESS EXCLUSIVE)

CREATE TEMPORARY TABLE temp AS SELECT id, films_code, distributors_id, info FROM lager;

-- lager Tabelle neu erstellen
DROP TABLE lager;
CREATE TABLE lager (
    id               SERIAL PRIMARY KEY,
    films_code       CHARACTER(5),
    distributors_id  DECIMAL(3),
    info             VARCHAR DEFAULT NULL,
    CONSTRAINT fk_lager_distributors_id FOREIGN KEY (distributors_id) REFERENCES distributors(id) 
        ON DELETE RESTRICT,
    CONSTRAINT fk_lager_films_code FOREIGN KEY (films_code) REFERENCES films(code) 
        ON DELETE RESTRICT
        DEFERRABLE 
);
-- Achtung, die Berechtigungen und Bedingungen der Tabelle müssen 
--   noch gesetzt werden!

-- neue Tabelle füllen
INSERT INTO lager SELECT * FROM temp;

-- vielleicht noch prüfen
-- SELECT * FROM lager LIMIT 100;

DROP TABLE temp;
-- nicht unbedingt notwendig, passiert sonst bei Ende der 
--   Sitzung automatisch

-- Transaktion abschließen
END;
	 

Füllt man (beispielsweise neue) Tabellen mit sehr vielen Daten, so ist INSERT langsam. Die schnellste Möglichkeit ist das Füllen über COPY. Bei sehr vielen Datensätzen spart es auch Zeit, die Indizes zu löschen und anschließend neu zu erzeugen. Traut man den Daten, weil diese beispielsweise aus einem Backup kommen, so bringt es auch oft sehr viel Zeitersparnis, wenn man die Trigger und Bedingungen löscht und nach dem Füllen wieder neu anlegt.

Eine Erweiterung ist die Möglichkeit CREATE TABLE AS, die eine Tabelle aus einer SELECT-Abfrage erzeugt. Das ist äquivalent zu einer INSERT INTO Erweiterung, mit der auch Tabellen erzeugt werden können (beides ist nicht Standard-SQL). Um standardkonform zu sein, muss man zunächst ein CREATE TABLE machen und diese dann über INSERT ... SELECT füllen.

4.12 Views

Views sehen aus Sicht der Anwendung aus wie Tabellen. Manchmal werden sie sogar als virtuelle Tabellen bezeichnet. Es sind Sichten auf Tabellen. Eine View stellt eine Abfrage (ein SELECT Kommando) dar. Diese Abfrage kann beispielsweise nur einige der Spalten einer Tabelle enthalten. Die Abfrage kann auch über einen join mehrere Tabellen verbinden und so Werte aus verschiedenen Tabellen anzeigen.

Ein großer Vorteil von Views ergibt sich, wenn man sich an die Privilegien erinnert. Über Views kann man es erreichen, dass nur bestimmte Felder sichtbar sind. In diesem Fall definiert man einen View über die erlaubten Felder und gibt dem entsprechenden Benutzer Rechte auf den View - nicht aber auf die Tabelle.

Momentan können Views so erstmal nur zum Lesen von Daten, nicht jedoch zum Ändern benutzt werden. Möchte man Daten auch ändern können, so verwendet man eine PostgreSQL Erweiterung, eine Regel. Im später später folgenden Abschnitt zu Regeln wird dies exemplarisch erklärt.

4.13 Cursors

Das Cursorkonzept stammt aus eingebettetem SQL (ESQL). Eingebettet heißt, dass man SQL Anweisungen direkt in Programmquelltexte einbettet (diese Programmiertechnik wurde inzwischen durch Standards wie ODBC weitgehend abgelöst; ESQL wird jedoch auch heute noch verwendet und auch von PostgreSQL unterstützt). In PostgreSQL stehen Cursors unabhängig von der Verwendung von ESQL zur Verfügung. Man kann sie beispielsweise auch über psql interaktiv verwenden.

Einem aktiven Cursor ist eine Menge von Datensätzen assoziiert, die über eine Abfrage, also über ein SELECT Kommando, ausgewählt wurden. Man kann nun einzelne Datensätze oder Teilmengen der Datensatzmenge über den Cursor holen. Der Cursor merkt sich dabei die Position. Holt man beispielsweise dreimal einen Datensatz aus einem Cursor, so erhält man automatisch die ersten drei Datensätze. Der Cursor zählt sozusagen mit, was auch den Namen erklärt. Eine Besonderheit ist, dass man über Cursors (in PostgreSQL, das gilt nicht generell) auch rückwärts gehen kann, also dass man Datensätze mehrfach holen kann.

Cursors funktionieren in PostgreSQL nur in Transaktionen. Um einen Cursor zu verwenden, muss dieser zunächst deklariert werden. Man kann sich vorstellen, dass man einer Abfrage einen (temporären) Namen gibt. Dann kann man Datensätze (die Ergebnisse der Abfrage) holen. Man kann den Cursor auch verschieben, beispielsweise, um Datensätze auszulassen oder erneut zu verarbeiten. Wird der Cursor nicht mehr benötigt, so wird er mit CLOSE geschlossen.

Besonderheiten in PostgreSQL sind, dass aus einem Cursor nicht über absolute Positionen gelesen werden kann und das Cursordaten nicht geändert werden können (es gibt kein DECLARE FOR UPDATE). Ein Cursor ist also immer READ ONLY. Durch die Transaktionsforderung ist er auch immer INSENSITIVE, auch wenn dies nicht explizit mit angeben wurde. Auch SCROLL ist nicht notwendig, da ein Cursor immer SCROLL kann. Es muss auch kein OPEN auf einen Cursor gemacht werden.

Ein einfaches Beispiel folgt.

-- Die Tabelle sieht so aus:
test=> SELECT code, title FROM films WHERE distributors_id = 124;
 code  |      title
-------+------------------
 MM-dt | Mädchen, Mädchen
 IJ1   | Indiana Jones 1
 IJ2   | Indiana Jones 2
 IJ3   | Indiana Jones 3
(4 rows)

-- Transaktion starten
test=> BEGIN; 
BEGIN 

-- Einen Cursor für Indiana Jones deklarieren.
test=> DECLARE ijfilme INSENSITIVE CURSOR FOR 
test->   SELECT code, title FROM films 
test->   WHERE code LIKE 'IJ%'
test->   ORDER BY code
test-> FOR READ ONLY; 
DECLARE 

-- Ersten Datensatz holen
test=> FETCH NEXT FROM ijfilme;
 code  |      title
-------+-----------------
 IJ1   | Indiana Jones 1
(1 row)

-- Zweiten Datensatz holen (1 ist wie NEXT)
test=> FETCH 1 FROM ijfilme;
 code  |      title
-------+-----------------
 IJ2   | Indiana Jones 2
(1 row)
  
-- Einen Datensatz zurückgehen:
test=> FETCH -1 FROM ijfilme;
 code  |      title
-------+-----------------
 IJ1   | Indiana Jones 1
(1 row)


-- Die nächsten zwei Datensätze holen:
test=> FETCH 2 FROM ijfilme;
 code  |      title
-------+-----------------
 IJ2   | Indiana Jones 2
 IJ3   | Indiana Jones 3
(2 rows)

-- Hier ist Ende:
test=> FETCH 1 FROM ijfilme;
 code | title
------+-------
(0 rows)

-- weit Zurückspringen (an den Anfang)
test=> MOVE -100 FROM ijfilme;
MOVE 3

-- wieder am Anfang
test=> FETCH 1 FROM ijfilme;
 code  |      title
-------+-----------------
 IJ1   | Indiana Jones 1
(1 row)

-- Rest holen
test=> FETCH ALL FROM ijfilme;
 code  |      title
-------+-----------------
 IJ2   | Indiana Jones 2
 IJ3   | Indiana Jones 3
(2 rows)

-- Den letzen nochmal (wie -1)
test=> FETCH PRIOR FROM ijfilme;
 code  |      title
-------+-----------------
 IJ3   | Indiana Jones 3
(1 row)

-- Cursor schließen
test=> CLOSE ijfilme;
CLOSE

-- Transaktion abbrechen
test=> ROLLBACK;
ROLLBACK

4.14 Indizes

Ein Index dient dazu, Datensätze mit bestimmten Eigenschaften schnell zu finden. Hat man beispielsweise eine Tabelle films wie im Beispiel Tabellen und sucht den Film mit dem code FilmA, so müsste ja die gesamte Tabelle durchsucht werden (und dazu vor allem von Festplatte geladen werden), dann müsste jeder code geprüft werden, ob er denn dem gesuchten entspricht.

Hier verwendet man einen Index. Ein Index gilt für eine bestimmte Tabellenspalte, also beispielsweise für code. Er kann aber auch aus mehreren zusammengesetzten Spalten bestehen. Ein Index ist eine effiziente Speicherung aller code Werte und einem Verweis auf die Stelle, an der der zugehörige Datensatz gespeichert ist. Wie genau die Speicherung funktioniert, hängt vom Typ des Index ab. Es gibt beispielsweise HashIndizes und binäre Bäume.

Sucht man nun FilmA, so wird nur der Index geladen, der ja viel kleiner ist, als die ganze Tabelle. Es wird an der entsprechenden Stelle nachgesehen (bei einem Hash geht das bei einer Gleichoperation mit einem Zugriff), dann direkt die richtige Stelle (oder die richtigen Stellen) der Tabelle geladen. Das ist dann wesentlich schneller.

Indizes sind aber nicht immer günstig. Hat man beispielsweise viele Datensätze, beispielsweise alle, so muss eh sehr viel von der Tabelle geladen werden. Hier bremst es nur, zusätzlich den Index zu laden (der Abfrageplaner würde in solchen Fällen den Index aber automatisch nicht verwenden, weil er das auch weiß, mehr dazu später). Das gleiche Verhalten kann man auch bei kleinen Tabellen erwarten (wenn man beispielsweise 100 aus 1000 Datensätzen liest, ist ein Index oft nicht günstig und wird nicht verwendet). Ein Index verlangsamt auch Änderungen, da nicht nur die Tabelle, sondern auch der Index aktualisiert werden muss.

Ein Index kann auch Eindeutigkeit (UNIQUE) fordern. Genauer gesagt, wird Eindeutigkeit in Tabellen garantiert, in dem ein UNIQUE Index angelegt wird. Dies sollte man aber lieber durch ein sauberes ALTER TABLE ... ADD CONSTRAINT erledigen. Das dann ein Index verwendet wird, ist ein Implementierungsdetail von PostgreSQL.

Die bereits kurz erwähnten Speichertypen von Indizes sind: BTREE (Lehman-Yao B-Baum), RTREE (R-Baum mit Guttman's "quadratic split" Algorithmus), HASH (Litwin's lineares hashen) und GIST (Generalized Index Search Trees, verallgemeinerter Index Suchbaum).

BTREE kann bei den Operationen <, <=, =, >=, > verwendet werden. RTREE bei den Operationen <<, &<, &>, >>, @, ~=, && und ein HASH bei =.

Indexes kann man per Hand erzeugen. Dazu gibt es das nicht-standard SQL Kommando CREATE INDEX. Zum Löschen gibt es analog DROP INDEX. Ein Index auch hat immer einen Namen. Meistens setzt man diesen aus Tabellen- und Feldnamen zusammen. Ein Beispiel für einen Index test1_id_idx über die Spalte id der Tabelle test1:

CREATE UNIQUE INDEX test1_id_idx ON test1 USING BTREE (id);

Es ist sogar möglich, Indizes für Funktionsergebnisse zu definieren. Verwendet man beispielsweise oft:

SELECT * FROM test1 WHERE lower(col1) = 'value';

so hilft einem ein Index über col1 hier ja nichts. Man kann aber einen Index für lower(col1) erzeugen, der dann wieder verwendet wird:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

Indizes können auch nur über Teile gelegt werden, in dem man eine WHERE Bedingung hinzufügt. So kann man beispielsweise sehr häufige Werte ausklammern und von Geschwindigkeitsvorteilen bei seltenen Werten profitieren (bei häufigen Werten werden Indizes oft gar nicht verwendet, weil langsam). Eine genaue Diskussion würde diesen Rahmen hier jedoch sprengen.

Eine Erweiterung ist die Möglichkeit, Indizes neu zu erstellen. Oft kann man diese einfach löschen und neu anlegen, was den Vorteil hat, dass nur die zu lesenden Datensätze gelockt werden. Hat man jedoch kaputte Indizes, kann man diese mit REINDEX neu erstellen lassen. Dies wird nur durchgeführt, wenn der Index als kaputt bekannt ist, oder man FORCE mit angibt.

Es gibt drei Varianten des Kommandos: REINDEX INDEX (erzeugt den folgenden Index neu), REINDEX TABLE (erzeugt für die folgend genannte Tabelle alle Indizes neu) und REINDEX DATABASE (erzeugt für die folgend genannte Datenbank alle Indizes neu).

REINDEX DATABASE my_database FORCE;

Hilft bei Problemen also (was in der Praxis jedoch im Prinzip NIE benötigt wird; aber wenn, dann hilft das).

4.15 Funktionen

Man kann sich eigene Funktionen definieren. Hierzu stehen neben SQL noch weitere Sprachen bereit. SQL ist in manchen Punkten beschränkt oder umständlich. Hier hilft einem eine Sprache wie PL/pgSQL oder PL/Perl weiter.

So kann man sich Funktionen schreiben, die beispielsweise komplizierte Bedingungen prüfen können (vielleicht Quersumme der ID muss gerade sein). Funktionen kann man auch direkt aufrufen. In PostgreSQL ruft man selbst definierte Funktionen genauso auf, die eingebaute: einfach über SELECT. Eine Funktion hallo mit zwei Parametern könnte man beispielsweise aufrufen:

SELECT hallo(123, 'hallo parameter');

Eine Erweiterung von PostgreSQL ist die Möglichkeit, Aggregatfunktionen selbst zu definieren (CREATE AGGREGATE). Aggregatfunktionen sind Funktionen wie min oder max, die beispielsweise in Gruppierten SELECT Anweisungen verwendet werden.

4.16 Trigger

Trigger sind relativ SQL99 konform (es gibt einfach zu umgehende Ausnahmen). Trigger können nicht auf einzelne Spalten angewendet werden. Über einen Trigger kann man vor oder nach den Ereignissen INSERT, DELETE oder UPDATE auf eine Tabelle eine Funkion aufrufen (man sagt, der Trigger feuert bei einem Ereignis). Diese Funktion kann dann die Daten prüfen, ändern oder sonst was unternehmen. PostgreSQL bietet erweitert dazu auch Regeln (Rules).

Über Trigger kann man, wie auch mit Bedingungen, Konsistenzbedingungen realisieren. Werden beispielsweise Schlüssel geändert, so kann man über einen Trigger vielleicht abhängige Datensätze entsprechend anpassen.

4.17 Regeln (Rules)

Regeln sind eine PostgreSQL Erweiterung. Ähnlich wie Trigger reagieren sie auf ein Ereignis SELECT, INSERT, DELETE oder UPDATE auf eine Tabelle. Optional kann noch eine Bedingung angegeben werden, die ebenfalls erfüllt sein muss, damit die Regel greift. Die Regel definiert dann, ob gar nicht passieren soll (NOTHING), ob zusätzlich oder ob anstatt (INSTEAD) des eigentlichen Kommandos ein anderes ausgeführt werden soll.

Über Regeln kann man, wie auch mit Bedingungen, Konsistenzbedingungen realisieren.

Regeln werden bei PostgreSQL oft in Verbindung mit Views verwendet. Dr.Ruud (  rvtol@isolution.nl) postete eine beispielhafte Regelschablone:

CREATE VIEW <virtual-table> AS SELECT * FROM <actual-table>;

CREATE RULE <virtual-table>_ins AS ON INSERT TO <virtual-table>
DO INSTEAD 
INSERT INTO <actual-table> ( <field-1>, <field-2>, ... , <field-n> )
VALUES ( new.<field-1>, new.<field-2>, ... , new.<field-n> ); 

CREATE RULE <virtual-table>_upd AS ON UPDATE TO <virtual-table>
DO INSTEAD 
UPDATE <actual-table>
SET <field-1> = new.<field-1>, 
<field-2> = new.<field-2>, 
... 
<field-n> = new.<field-n> 
WHERE <primary-key> = old.<primary-key>; 

CREATE RULE <virtual-table>_del AS ON DELETE TO <virtual-table>
DO INSTEAD 
DELETE FROM <actual-table>
WHERE <primary-key> = old.<primary-key>;
	 

4.18 Sequenzen

Eine Sequenz ist eine Zählfunktion, die hauptsächlich bei Autoinkrementfeldern angewendet werden. Bei jedem Aufruf liefert eine Sequenz einen größeren Wert (dies funktioniert natürlich auch vollständig in Transaktionen). Man kann auch den letzten Wert abfragen, den man in der Transaktion erhalten hat und so herausfinden, welchen Wert der letzte Datensatz im Autoinkrementfeld erhalten hat.

Sequenzen werden beim Feldern vom Typ serial automatisch erzeugt. Der Name wird automatisch bestimmt. Es kommt zu einem Fehler beim Anlegen der Tabelle, wenn der Name bereits vergeben ist. Man kann ein Sequenz auch für mehrere verschiedene Felder verwenden, und so tabellenübergreifend eindeutige Werte erzeugen. Daher werden automatisch erzeugte Sequenzen nicht automatisch mit dem Löschen von Tabellen gelöscht.

Man kann Sequenzen auch explizit über CREATE SEQUENCE erzeugen. Die Funktionen nextval('seq') und currval('seq') liefern den nächsten bzw. aktuellen (zuletzt gelieferte nextval) zurück. Mit setval('seq', 1234) kann man den Wert eine Sequenz direkt setzen.

Dies braucht man beispielsweise, wenn man IDs hat, die von der Sequenz noch gar nicht erzeugt wurden, weil jemand einen Wert bei INSERT direkt angegeben hat. In solchen Fällen erreicht die Sequenz irgendwann diesen Wert (oder den ersten dieser Werte), daraufhin klappt das INSERT nicht, weil die ID sicherlich eindeutig sein muss, die Sequenz wird auch nicht erhöht (Transaktionsabbruch) und man kommt nicht weiter. Hier hilft es, die Sequenz auf den höchsten verwendeten Wert zu setzen. Hat man eine Tabelle lager mit einem Autoinkrementfeld id, so heißt die automatisch erzeugte Sequenz lager_id_seq. Um diese anzupassen, kann man einfach schreiben:

Danach funktioniert das Autoinkrementfeld wieder. Vor solchen Phänomenen kann man sich schützen, wenn man Regeln verwendet, die ein direktes Setzen solcher Felder verhindern.

4.19 Sprachen

Neben SQL unterstützt PostgreSQL weitere Datenbanksprachen. Arbeitet man mit SQL, so kann man bestimmte Dinge teils nur schwierig formulieren.

SQL ist eine sehr mächtige Sprache, wenn man sie beherrscht. Im Gegensatz zu prozeduralen Sprachen beschreibt man jedoch keine Algorithmen. Möchte man beispielsweise alle Werte des Feldes gehalt einer Tabelle mitarbeiter um 10 Prozent erhöhen, würde man prozedural formulieren: gehe jeden Datensatz durch, und für jeden Wert setze Wert gleich Wert mal 1.1. In SQL schreibt man das jedoch einfach so hin:

UPDATE mitarbeiter SET gehalt = gehalt * 1.1;

Man beschreibt also in etwa Änderungen. Zusätzlich kann man hier natürlich auch Bedingungen angeben (nur, wenn gehalt kleiner als 5000 ist beispielsweise). Diesen grundlegenden Unterschied muss man unbedingt verstehen, wenn man mit SQL arbeitet. In der Praxis sieht man manchmal Skripte, die die Datensätze einer Tabelle einzeln durchgehen, einen Test machen, und eine Änderung schreiben. So etwas macht man in der Regel einfach mit einem passendem SQL Kommando; das hat noch den angenehmen Nebeneffekt, viel schneller zu sein.

In PostgreSQL kann man auch Unterabfragen verwenden:

UPDATE mitarbeiter 
SET gehalt = gehalt + 
        (SELECT bonus FROM bonustabelle WHERE art = 'weihnachtsgeld');
	 

Mit derartigen Konstrukten kann man Operationen durchführen, die in prozeduralen Sprachen nur sehr umständlich gemacht werden können.

-- Eine Beispieltabelle für Angestellte
CREATE TABLE emp (
    empname text,               -- Name
    salary integer,             -- Gehalt
    last_date timestamp,        -- Letztes Datum
    last_user text              -- Letzter Benutzer
);

-- Der Trigger. 
-- Es ist eine Funktion, die einen Datensatz zurückliefert.
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
    -- Dieses Begin kommt von PL/pgSQL. Es startet keine
    --   neue Transaktion!
    BEGIN
        -- Prüfen, ob empname und salary (Gehalt) angegeben wurde
        IF NEW.empname ISNULL THEN
            -- RAISE erzeugt einen Fehler (EXCEPTION)
            -- Die Transaktion wird dadruch abgebrochen.
            RAISE EXCEPTION ''empname darf nicht NULL sein'';
        END IF;
        IF NEW.salary ISNULL THEN
            -- anstelle des % steht dann der Name
            RAISE EXCEPTION ''% ohne Gehalt?!'', NEW.empname;
        END IF;

        -- Wer arbeitet für uns und muss dafür bezahlen?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% mit negativen Gehalt?!'', NEW.empname;
        END IF;

        -- Es wird das letze Änderungsdatum und der Änderungsbenutzer gesetzt.
        -- Selbst wenn bei INSERT oder UPDATE last_user angegeben wird, so
        -- wird dennoch immer current_user verwendet. Es ist also
        -- nicht mehr möglich, einen falschen Eintrag zu erzeugen.
        NEW.last_user := current_user;
        -- now sollte hier besser als Funktion und besser gegen 
        --   den standardkonformen Namen current_timestamp ersetzt werden:
        --   NEW.last_date := current_timestamp;
        NEW.last_date := ''now'';

        -- Den (geänderten) Datensatz zurückliefern (wird dann eingetragen)
        RETURN NEW;
    END;
    -- Das END kommt - wie auch BEGIN - von PL/pgSQL und beeinflußt
    --   die aktive Transaktion nicht
' LANGUAGE 'plpgsql';

-- Diese Funktion als Trigger setzen. Danach wird sie bei INSERT
-- oder UPDATE automatisch gestartet.
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
	  

-- Eine Funktion, die den größeren Wert zurückliefert.
-- Ist eine der Werte NULL, so wird der andere zurückgeben.
-- Sind beide NULL, ergibt die Funktion auch NULL
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS '
    my ($a,$b) = @_;
    if (! defined $a) {
        if (! defined $b) { return undef; }
        return $b;
    }
    if (! defined $b) { return $a; }
    if ($a > $b) { return $a; }
    return $b;
' LANGUAGE plperl;
     

-- Ein Beispiel mit einen zusammengesetzten Datentyp (hier employee)
CREATE TABLE employee (
    name text,
    basesalary integer,
    bonus integer
);

-- Als Parameter kommt ein employee, also z.B. ein Datensatz aus
-- dieser Tabelle
CREATE FUNCTION empcomp(employee) RETURNS integer AS '
    my ($emp) = @_;
    return $emp->{''basesalary''} + $emp->{''bonus''};
' LANGUAGE plperl;
	  

4.20 Notifikationen (Benachrichtigungen)

Eine PostgreSQL Erweiterung erlaubt es, das mehrere Clienten sich synchronisieren. Dazu kann ein Client über LISTEN ein Objekt beobachten. Ruft ein anderer Client NOTIFY auf diesem Objekt auf, so wird ersterer (und alle anderen LISTENer) benachrichtigt. Hat er kein Interesse mehr an Notifikationen, ruft er ein UNLISTEN auf das Objekt auf. LISTEN ist nicht blockierend; die Notifikation erfolgt asynchron.

Dies wird wohl selten verwendet und ist nicht portabel. Oft kann man ähnliches Verhalten auch über Datensatz-Locks über Tabellen erreichen.

4.21 Statistiken für den Planer

Wie im Abschnitt  Optimierung mit EXPLAIN noch genauer erklärt wird, wird eine Abfrage vom Planer in Abfragepläne umgewandelt. Um sich für den richtigen (also den schnellsten) Abfrageplan entscheiden zu können, muss beispielsweise geschätzt werden, wie viele Daten von Festplatte gelesen werden müssen. Das hängt von der Tabellengröße ab.

Die Tabellengrößen werden von PostgreSQL in einer speziellen Tabelle pg_class gespeichert. Meistens werden jedoch nicht alle Datensätze benötigt, sondern nur ein Teil. Dieser wird oft über eine Bedingung definiert. Dadurch wird es schwierig zu schätzen, wie viele Daten geladen werden müssen, da man dazu ja wissen muss, wie oft die Bedingung erfüllt ist.

Um diese Abschätzung durchführen zu können, werden Statistiktabellen geführt, beispielsweise pg_stats. In diesen Tabellen werden statistische Informationen über Tabelleninhalte gespeichert, beispielsweise die häufigsten Werte, die Anzahl der NULL Werte, die Anzahl der verwendeten Werte (es kann ja Tabellen mit 1000 Einträgen geben, die nur 4 verschiedene Werte verwenden) und andere. Mit diesen Informationen errechnet der Planer seine Abschätzungen.

Die Statistiken werden natürlich nicht ständig aktualisiert, das wäre ja sehr bremsend (um statistische Korrelation zu berechnen, muss ja in jedem Fall jeder Datensatz gelesen werden). Statt dessen werden die Statistiken durch das SQL Kommando ANALYZE oder VACUUM ANALYZE aktualisiert, dass man demzufolge regelmäßig (zum Beispiels nachts und nach großen Änderungen) ausführen sollte.

4.22 Optimierung mit "EXPLAIN"

Natürlich ist es immer interessant, Abfragen auf Geschwindigkeit zu optimieren. Bei langsamen Abfragen ist es interessant, den Grund zu kennen. Vielleicht fehlt ja nur ein Index oder sitzt ungünstig?

Verarbeitet PostgreSQL eine Abfrage, so wird vom DBMS ein Abfrageplan erstellt. Dies wird durch den sogenannten Planer erledigt. Dieser legt fest, in welcher Reihenfolge die Daten organisiert werden und ob (und welche) Indizes verwendet werden. Dazu prüft er die verschiedenen Möglichkeiten auf Effizienz. Er erstellt also erstmal viel Pläne und wählt dann den Plan aus, der die geringsten Kosten hat, also am schnellsten geht.

Es gibt das EXPLAIN Kommando, das den Abfrageplan für die Abfrage anzeigt (eine PostgreSQL Erweiterung). Man erhält die geschätzten Kosten, bis mit der Ausgabe begonnen werden könnte, und die gesamten Kosten. Als Einheit wird in etwa Festplattenzugriffe verwendet. Die anderen beiden Zahlen sind die geschätzte Anzahl an Datensätzen (etwas richtiger ist hier der Begriff Tupel), die zurückgegeben werden, und die geschätzte Größe eines Datensatzes.

Eine Abfrage besteht aus mehreren Teilen. Die Kosten jedes Teiles schließen immer die aller nach unten folgenden Teile ein.

Ein paar Beispiele dazu.

Man sieht: Es ist ein vollständiges durchgehen der Tabellen tenk1 notwendig (Seq Scan heißt sequentiell). Mit der Ausgabe kann sofort begonnen werden, nach 333 Zugriffen ist sie nach 10000 Datensätzen beendet. Die 333 Zugriffe entstehen hier übrigens durch 233 Diskzugriffe und 10000 * cpu_tuple_cost (Voreinstellung ist 0.01), also 233 + 100 == 333.

Man sieht, das immer noch die gesamte Tabelle gelesen werden muss. Es werden weniger Datensätze erwartet (natürlich ist der Wert nur geschätzt und nicht wirklich aussagekräftig). Die Kosten sind durch die zusätzlich benötigte Vergleichszeit etwas gestiegen. Es wird immer noch kein Index verwendet, weil er sich nicht lohnt.

Oft liefert Abfragen jedoch nicht solche Mengen an Daten:

Hier ist die Bedingung so, dass nur noch 49 Datensätze erwartet werden. Daher entscheidet der Planer, den Index zu verwenden. Da hier nur 50 Datensätze erwartet werden, ist die Verwendung eines Index billiger, obwohl jeder einzelne Datensatz langsamer geladen wird (Festplatten lesen Folgedaten schneller).

In diesem etwas komplizierteren Beispiel wird zusätzlich eine zweite Tabelle benutzt, die über einen Join verbunden ist. Man sieht, das der Planer einen Indexscan ausgewählt hat. Durch den Join entsteht ein Loop mit zwei Teilen. Zunächst wird die Tabelle tenk1 über den Index durchgearbeitet. Die Kosten sind natürlich die gleichen im Beispiel davor (gleiche Bedingung: WHERE unique1 < 50). Mit dem Wert unique2, der aus tenk1 gelesen wurde (genauer gesagt, sind das ja insgesamt 49 Werte!), wird nun ein passender Eintrag in tenk2 gesucht. Der Planer erwartet genau einen Treffer und verwendet daher wieder einen Index (der glücklicherweise auch verfügbar ist). Diese Teilabfrage gilt für einen konstanten Wert unique2 (je einen der insgesamt 49). Damit ist der Zugriff vergleichsweise billig (3).

Die zweite Teilabfrage wird nun für jeden der 49 Werte durchgeführt. Die Kosten sind also 49 * 3 == 147. Dazu kommen die 181 des vorherigen Teils (der die 49 Werte überhaupt erstmal lieferte), macht zusammen 147 + 181 == 328. Dazu kommt noch etwas Rechenzeit für den Join (hier ca. 2). Macht dann zusammen 330.

330 sind auch die Kosten, die der Planer für den Loop ausgerechnet hat. Es 49 Datensätze (es wird ja erwartet, das jeweils ein Datensatz passt), nur das die etwas größer sind, also vorher (sind ja durch einen Join verbunden).

Der Planer hat sich entschieden, einen nested-loop join (etwa: geschachtelte Schleife) zu verwenden. Man kann über Variablen den Planer beeinflussen. In der Praxis bringt das so gut wie nie Vorteile. Beispielsweise kann man dem Planer sagen, dass er nested-loop join nicht verwenden soll:

Der Planer kommt nun mit einem anderen Plan. Zunächst werden wieder die 49 Datensätze aus tenk1 mit Indexunterstützung geladen. Die Werte werden nun aber erst alle geladen, und in einem Hash gespeichert. Man sieht das gut an den Anfangskosten für den Hash: sie entsprechen den Gesamtkosten für den Indexscan (da der Hash anschließend gebaut wird, und sehr schnell fertig ist).

Anschließend wird die Tabelle tenk2 sequentiell durchsucht, ob irgendwo der Wert unique2 aus der Tabelle zu einem der im Hash gespeicherten Werte passt. Dies muss ja nun für alle 10.000 Datensätze gemacht werden (nestloop ist ja verboten).

Sobald mit dem Scan über tenk2 begonnen wurde, sind die ersten Treffer zu erwarten. Die Anfangskosten des Joins entsprechen also den Kosten, die anfallen, bis mit tenk2 begonnen werden kann (vorher kommt ja keine Ausgabe), also den Gesamtkosten des ersten Indexscans. Dazu kommen die 333 für den sequentiellen Scan über tenk2, macht 514.09. Die restlichen 50 gehen für Rechenleistung drauf; schließlich muss mit jedem der 10000 Datensätze eine Test auf den vorher gespeicherten Hash gemacht werden. Die erwarten Kosten sind wesentlich höher als vorhin, daher hat der Planer vorhin auch einen nestloop verwendet.

Weitere Variablen, die bestimmte Pläne vermeiden, sind: ENABLE_HASHJOIN, ENABLE_INDEXSCAN, ENABLE_MERGEJOIN, ENABLE_SEQSCAN ENABLE_SORT und ENABLE_TIDSCAN. Auch diese können auf off gesetzt werden, um anzuzeigen, dass sie zu vermeiden sind. Wie bereits gesagt, lassen sich nur schwer Fälle konstruieren, wo das was bringt.

EXPLAIN kann auch um ANALYZE erweitert werden. Dann wird die Abfrage tatsächlich ausgeführt, und auch die wirklichen Werte werden ausgegeben.

Hier wird die tatsächlich benötigte Zeit in Millisekunden angezeigt. Man erkennt auch, dass der Planer sich gering verschätzt hat, anstatt 49 Datensätzen sind es 50. Es läßt sich abschätzen, das ein Festplattenzugriff (die Einheit von EXPLAIN) hier in etwa 10 Millisekunden dauert.

Dies mag als Einführung ausreichen. Das Verstehen dieser Ausgaben erfordert Übung. Man kann so erkennen, ob und wann Indexe verwendet werden, ob sie günstig sind, oder vielleicht gar nicht benötigt sind. Dann sollte man sie löschen, dass spart Zeit bei Aktualisierungen.