4.1.1 Kommandozeilenoptionen

psql versteht etliche Optionen:

Nach den Optionen gibt man eine Datenbank an, sofern man nicht -d verwendet. Dahinter kann man noch einen Benutzernamen schreiben, sofern man nicht -U verwendet.

Um als Superuser postgres zur Datenbank test zu verbinden, schreibt man also beispielsweise:

Je nach Einstellung der Authentifizierung wird nun nach einen Passwort gefragt. Es erscheint das Datenbankprompt.

Hat man PostgreSQL mit der readline-Unterstützung übersetzt, kann man ebenso wie in der Bash die Tabulator-Taste drücken, um Befehle und Objekte zu erweitern

4.1.2 Interaktion

Am Prompt kann man SQL Befehle eingeben:

Man sieht, das SQL Kommandos mit Semikolon abgeschlossen werden und dann automatisch ausgeführt werden. Das Prompt zeigt an, ob man in einer Klammer ist, eine kleine Hilfe. Das Beispielkommando hat nun eine einfach Testtabelle erzeugt. Diese kann man nun mit Daten füllen:

Die Ausgabe enthält eine merkwürdige Nummer. Das ist der OID, der object identifier. Diese sollte man nicht weiter beachten (es handelt sich um eine Art automatisches Indexfeld, ist aber höchst unportabel, und wird nur intern benötigt).

Über psql kann man auch in Transaktionen arbeiten:

Die Tabelle enthält einen Datensatz:

Transkation beginnen:

Tabelle temp leermachen (alles löschen):

Die Tabelle ist jetzt auch Sicht der Transaktion leer:

Transaktion abbrechen:

Es ist nichts geändert worden:

Die Temporäre Tabelle verfällt automatisch, wenn man die Verbindung schließt.

4.1.3 Interne Kommandos

psql verfügt über eine Reihe sogenannter interner Kommandos. Diese beginnen mit einem \ (Backslash). Einige der wichtigesten internen Kommandos sind:

Es folgt ein Beispiel für das Ausgabeformat. Zunächst soll die Ausgabe der oben erwähnten Testtabelle nicht feld1 und feld2 beinhalten, sondern Nummer und Textfeld. Wenn man diese Bezeichner case-sensitiv haben möchte (Tabellen- und Feldnamen sind sonst case-insensitiv, das heißt, Groß-/Kleinschreibung wird nicht beachtet), muss man diese quoten:

Nach \x sieht die Ausgabe so aus:

Dies macht bei großen Tabellen Sinn, wenn nicht mehr alle Spalten nebeneinander auf den Bildschirm passen.

4.1.4 Verwendung

Neben der interaktiven Verwendung kann man psql dazu verwenden, SQL Skripte auszuführen, beispielsweise Skripte, die Datenbankschemata erzeugen. Man kann psql sogar dazu verwenden, Shell-Skripte mit rudimentärer Datenbankfunktionalität zu versehen; hier ist die Verwendung von Perl::DBI oder anderen Methoden jedoch oft einfacher und sauberer.

4.5.1 Einführung

Das klassische Beispiel für den Bedarf ist das Buchungsbeispiel. Angenommen, es existieren zwei Kontotabellen. Möchte man nun eine Buchung gegen diese beiden Tabellen machen, muss in jede Tabelle ein neuer Datensatz angelegt werden. Dazu muss man zwei INSERT INTO SQL Kommandos ausführen lassen.

Nun könnte es ja passieren, dass eines der beiden Kommandos klappt, das andere jedoch nicht. In diesem Fall würden die Konten nicht mehr stimmen, da die Summen nicht mehr passen. Man hätte inkonsistente Daten und ein Problem.

Daher fasst man beide Kommandos zu einer Transaktion zusammen. Eine Transaktion klappt entweder ganz, oder gar nicht. Geht also eines der SQL Kommandos schief, so hat auch das andere automatisch keinen Effekt (es wird gegebenenfalls rückgängig gemacht).

Transaktionen sind für andere erst sichtbar, wenn sie abgeschlossen wurden. Das bedeutet im Beispiel, dass nach dem Ausführen der ersten Kommandos ein anderer Client diese Änderung überhaupt nicht sieht. Erst wenn das andere Kommando erfolgreich war und die Transaktion beendet wurde, werden die Änderungen sichtbar. Somit stimmen die Summen zu jedem Zeitpunkt.

Wenn innerhalb einer Transaktion Daten gelesen werden, und von einer anderen Transaktion in dieser Zeit geändert werden, so wird die Transaktion automatisch abgebrochen. Auch hier kann es nicht passieren, dass Daten versehentlich zurückgeschrieben werden, die inzwischen an anderer Stelle geändert wurden.

4.5.2 Multiversion Concurrency Control

Implementiert wird ein sogenanntes Multiversion Concurrency Control (MVCC). Das bedeutet, das Abfragen einer Transaktion die Daten so sehen, wie sie zu einem bestimmten Zeitpunkt waren, unabhängig davon, ob sie inzwischen von einer anderen Transaktion geändert wurden. Dies verhindert, dass eine Transaktion einen Teil Daten vor und einen anderen nach einer nebenläufig abgeschlossenen Transaktion lesen kann und verhindert so inkonsistentes Lesen: die Transaktionen werden von einander isoliert. Der Hauptunterschied zu Lock Verfahren ist, dass MVCC Locks für das Lesen nicht mit Locks für das Schreiben in Konflikt stehen. Somit blockiert das Schreiben nie das Lesen und das Lesen nie das Schreiben.

Eine wichtige Einschränkung gibt es: Transaktionen können in PostgreSQL nicht geschachtelt werden (es gibt also keine Untertransaktionen).

4.5.3 Transaktionslevel

PostgreSQL unterstützt zwei Transaktionslevel: read committed und serializable. Verwendet eine Transaktion read committed, so kann es vorkommen, dass sie Daten erneut liest, aber andere Daten erhält als beim ersten Lesen (nicht-wiederholbares Lesen, non-repeatble reads). Auch sogenanntes Phantom-Lesen (phantom reads) kann vorkommen. Vom Phantom-Lesen spricht man, wenn sich in einer Transaktion die Ergebnissätze von Suchbedingungen ändern können. Sogenanntes schmutziges Lesen (dirty reads), also das Lesen von Änderungen aus anderen, nicht abgeschlossenen Transaktionen kann jedoch nicht auftreten. Dieser Transaktionslevel ist die Voreinstellung. Er ist einfach anzuwenden, schnell und für die meisten Anwendungen ausreichend.

Verwendet eine Transaktion serializable, können diese beiden unerwünschten Effekte nicht auftreten. Man benutzt diesen Level für Anwendungen, die komplexe Abfragen und Änderungen durchführen.

4.5.4 Anwendung

Transaktionen werden durch das SQL Kommando BEGIN eingeleitet. Dies ist nicht standardkonform; ANSI fordert, das immer implizit eine Transaktion begonnen wird. PostgreSQL bietet jedoch wie viele andere DBMS auch eine sogenanntes auto commit Funktion an, dies ist auch das Standardverhalten. Jedes SQL Kommando wird dann so aufgefasst, als wäre es eine einzelne Transaktion (es wird also sozusagen ein implizites COMMIT nach jedem SQL Kommando ausgeführt). Möchte man nun eine aus mehreren Anweisungen bestehende Transaktion beginnen, schreibt man einfach BEGIN als erstes Kommando. Dies passt auch gut zum eingebettetem SQL, da die SQL Kommandos dadurch in einen schicken BEGIN - END Block eingeschlossen sind.

Grundsätzlich gibt es zwei Möglichkeiten, eine Transaktion zu beenden. Eine Anwendung kann eine Transaktion selbst abbrechen. Hierzu dient das Kommando ROLLBACK. Keine der Änderungen der Transaktion wird ausgeführt. Eine Anwendung kann die Transaktion auch positiv beenden. Dazu wird END oder COMMIT verwendet. Die Transaktion wird genau dann durchgeführt, wenn sie fehlerfrei war. In diesem Fall werden alle Änderungen (oder die eine komplexe Transaktionsänderung) übernommen (sichtbar). Trat in der Transaktion ein Fehler auf, so gibt es natürlich keine Möglichkeit, sie doch noch positiv zu beenden, da dies zu Inkonsistenzen führen würde. In solchen Fällen kann die Anwendung (je nach Art des Fehlers) die Transaktion wiederholen. Dies ist natürlich nicht sinnvoll, wenn beispielsweise ein Tabelle fehlt. Dann wird auch die Wiederholung fehlschlagen.

So ist also sichergestellt, dass Transaktionen nur vollständig (und vollständig erfolgreich), oder überhaupt nicht durchgeführt werden.

Hat man mit BEGIN eine Transaktion begonnen, so ist zunächst die Datenbankvoreinstellung des Transaktionslevel (read committed) aktiv. Solange die Transaktion noch nicht begonnen wurde, kann der Transaktionslevel noch geändert werden. Dazu wird das SQL Kommando SET TRANSACTION ISOLATION LEVEL verwendet. Als Parameter wird READ COMMITTED oder SERIALIZABLE angegeben. Damit ist der Transaktionslevel eingestellt. Ein Client kann auch einen eigene Voreinstellung setzen, wenn beispielsweise Transaktionen grundsätzlich serializable sein sollen. Das SQL Kommando lautet SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL und erwartet die gleichen Parameter wie das vorherige Kommando.

4.5.5 Mögliche Effekte

Verwendet man Transaktionen, so kann es natürlich vorkommen, dass eine Transaktion vom DBMS beendet wird, weil eine andere Transaktion Daten geändert hat; insbesondere, wenn serializable verwendet wird. In solchen Fällen wird die Transaktion in der Regel einfach von vorn beginnend vollständig wiederholt (die Anwendung führt diese also erneut aus).

Derartige Effekte minimiert man oft, in dem man Datensätze, von denen man schon weiß, dass man sie ändern muss, schon mal für selbiges vormerkt. Dies geschieht mit dem SQL Kommando SELECT FOR UPDATE. Nun weiß das DBMS, dass diese Datensätze der Transaktion gehören. Möchte eine andere Transaktion hier auch Daten ändern, so wartet diese automatisch, bis die erste Transaktion beendet wurde (also bestätigt oder abgebrochen). Dann erst wird die Aktion ausgeführt. Mit dem SQL Kommando LOCK TABLE können auch komplette Tabellen gesperrt werden. Verwendet man diese Mechanismen sorgfältig, vereinfacht sich die Handhabung; spätere Transaktionsabbrüche treten nicht auf, da die Daten ja bereits verwendet werden.

Es kann passieren, dass sich Transaktionen gegenseitig ausschließen. Würde beispielsweise Transaktion A die Tabelle A sperren und Transaktion B Tabelle B und anschließend Tabelle A sperrt, kommt es zu einer solchen Situation, wenn Transaktion A auch versucht, Tabelle B zu sperren. Transaktion B kann ja Tabelle A nicht sperren, weil diese schon von Transaktion A bereits gesperrt ist und blockiert, bis Transaktion A beendet wurde. Transaktion A wiederum wartet auf Transaktion B, um Tabelle B sperren zu können. Man spricht von einem Deadlock - beide Transaktionen haben sich gegenseitig blockiert.

PostgreSQL erkennt solche Fälle automatisch. Eine der beiden Transaktionen wird mit einem entsprechendem Deadlock-Fehler abgebrochen, woraufhin die andere durchgeführt werden kann. Auch hier wiederholt die Anwendung einfach die Transaktion. Da nun keine andere mehr läuft, wird es diesmal klappen.

Bei der Arbeit mit komplexen Transaktionen muss man damit rechnen, dass eine Transaktion durch solche oder ähnliche Gründe abgebrochen wird. In der Software ist also vorzusehen, Transaktionen wiederholen zu können. Da im Falle eines Transaktionsabbruches ja überhaupt keine Daten geändert werden, geht das unproblematisch. Man beginnt einfach von vorn.

4.5.6 Sperren für Tabellen

Sperre oder Lock bedeutet, dass der Inhaber oder Eigentümer dessen davor geschützt ist, dass jemand anders eine Sperre erzeugt, der dieser widerspricht. Es gibt verschiedene Arten von Sperren. Lese-Locks beispielsweise schließen sich nicht gegenseitig aus (es können ja problemlos mehrere Transaktionen die gleichen Daten lesen), jedoch schließt ein Lese-Lock einen Schreib-Lock aus. Schreib-Locks schließen sich und Lese-Locks aus. Letztere nennt man daher auch exklusiv, keine anderere Sperre kann neben einem Schreib-Lock ausgeführt sein.

Die folgende Aufstellung ist unvollständig.

4.5.7 Sperren für Datensätze

Datensätze werden mit SELECT FOR UPDATE gesperrt. Dies schließt Änderungen an genau diesen Datensätzen aus. Wie bereits angedeutet, schließt dies kein Lesen aus (Schreiben blockiert kein Lesen).

4.5.8 Transaktionsbeispiel

Wie bereits gesagt, werden Transaktionen bei Fehlern automatisch abgebrochen. Alle Kommandos werden ignoriert:

Transaktion beginnen:

Es Kommando geht schief, zum Beispiel weil syntaktisch falsch:

Die Transaktion ist abgebrochen worden. Alle Kommandos werden ab jetzt ignoriert:

Selbst wenn man versucht, die Transaktion positiv zu beenden, wird nichts geändert (die Transaktion wird also trozdem abgebrochen):

Die Antwort COMMIT heißt nicht, dass wirklich etwas committed wurde. Hier wurde ja ein Rollback durchgeführt. Dieses Verhalten ist bei Skripts sehr nützlich. Die Kommandos schreibt man einfach ein einen BEGIN; - END; Block (End ist das gleiche wie Commit). Bei einem Fehler wird keine Änderung ausgeführt - die Datenbank sieht genauso aus, wie vorher. Man kann das Skript korrigieren und erneut ausführen.

An dieser Stelle sei noch einmal daran erinnert, dass Strukturkommandos (wie CREATE und DROP) nicht den den Transaktionsregeln unterliegen.

4.5.9 Arbeiten mit Bedingungen

Es ist möglich, Bedingungen (CONSTRAINTS) an Tabellen zu definieren. Beispielsweise könnte man fordern, dass die Summe über alle Felder einer Tabelle null sein muss. Möchte man nun zu einem Datensatz drei addieren, muss man also von einem anderen drei abziehen. Doch kurz dazwischen ist die Bedingung ja verletzt, denn die Summe ist ja dann nicht mehr null, sondern drei!

Bedingungen können daher in Transaktionen aufgeschoben werden (DEFERRED). Das bedeutet, sie werden erst am Ende der Transaktion geprüft. Eine Bedingung kann dies aber auch verhindern. Bedingungen können so definiert werden, dass sie immer sofort geprüft werden. Bedingungen können aber auch so definiert werden, dass die Prüfung per Voreinstellung aufgeschoben wird, oder das die Bedingung explizit aufgeschoben werden kann.

Um Bedingungen aufzuschieben, die sofort geprüft werden sollen, aber auch aufgeschoben werden dürfen, verwendet man das SQL Kommando SET CONSTRAINTS ALL DEFERRED. Anstatt ALL kann man auch den Namen der Bedingung angeben (das wird auch oft gemacht). Anstatt DEFERRED kann auch IMMEDIATE eingestellt werden. Damit hat das den Gegenteiligen Effekt. Bedingungen, die per Voreinstellung aufgeschoben werden, werden dennoch sofort ausgeführt.

Schiebt man also eine Prüfung auf, so wird diese am (bisher positiven) Ende der Transaktion durchgeführt. Stellt sich nun heraus, dass die Bedingung verletzt ist, wird die Transaktion abgebrochen (und die Bedingung bleibt dadurch erfüllt).

-- 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"

-- 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;
	 

-- 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;
	 

-- 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 letzten 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