Beispiel für Locking – Verbindung 1
Verbindung 1: CREATE TABLE locking_test ();
BEGIN;
LOCK TABLE locking_test IN ACCESS EXCLUSIVE MODE;
Beispiel für Locking – Verbindung 2
Verbindung 2: BEGIN;
LOCK TABLE locking_test IN ACCESS SHARE MODE;
Beispiel für Locking – Verbindung 1
Verbindung 1: ROLLBACK;
Beispiel für Locking – Verbindung 2
Verbindung 2: ROLLBACK;
Beispiel für NOWAIT Locking – Verbindung 1
Verbindung 1: BEGIN;
LOCK TABLE locking_test IN ACCESS EXCLUSIVE MODE;
Beispiel für NOWAIT Locking – Verbindung 2
Verbindung 2: BEGIN;
LOCK TABLE locking_test IN ACCESS SHARE MODE NOWAIT;
Beispiel für Advisory Locking – Verbindung 1
Verbindung 1: SELECT pg_advisory_lock(1);
Beispiel für Advisory Locking – Verbindung 2
Verbindung 2: SELECT pg_advisory_lock(1);
Beispiel für Advisory Locking – Verbindung 1
Verbindung 1: SELECT pg_advisory_unlock(1);
Advisory Locking mit zwei Schlüsseln
SELECT pg_advisory_lock(23, 1);
Shared Advisory Locking
SELECT pg_advisory_lock_shared(42);
Advisory Locking testweise anfordern
SELECT pg_try_advisory_lock(42);
Advisory Locking löschen
SELECT pg_advisory_unlock_all();
B-Tree-Index erstellen
CREATE INDEX index_name ON tabelle USING btree(spalte);
Hash-Index erstellen
CREATE INDEX index_name ON tabelle USING hash(spalte);
GIN-Index erstellen
CREATE INDEX index_name ON tabelle USING gin(spalte);
Index für Volltextsuche erstellen
CREATE TABLE textsuche ( volltext TSVECTOR );
CREATE INDEX vt_suche ON textsuche USING gist(volltext);
Unique-Index erstellen
CREATE TABLE mitarbeiter ( personalnummer INTEGER NOT NULL UNIQUE, ... );
Unique-Index hinzufügen
CREATE UNIQUE INDEX mitarbeiter_personalnummer ON mitarbeiter(personalnummer);
Index als Primärschlüssel anlegen
CREATE TABLE mitarbeiter ( personalnummer INTEGER PRIMARY KEY, ... );
Primärschlüssel hinzufügen
ALTER TABLE mitarbeiter ADD PRIMARY KEY (personalnummer);
Index im laufenden Betrieb erzeugen
CREATE TABLE daten ( id INTEGER NOT NULL, daten TEXT );
CREATE INDEX CONCURRENTLY daten_id ON daten(id);
Index konnte nicht erzeugt werden
DROP TABLE IF EXISTS daten;
CREATE TABLE daten ( id INTEGER NOT NULL, daten TEXT );
INSERT INTO daten (id, daten) VALUES (1, 'datum 1');
INSERT INTO daten (id, daten) VALUES (2, 'datum 2');
INSERT INTO daten (id, daten) VALUES (1, 'wieder datum 1');
CREATE UNIQUE INDEX CONCURRENTLY daten_id ON daten(id);
Fehlerhaften Index anzeigen
\d daten
Index über mehrere Spalten anlegen
CREATE TABLE nachrichten ( von TEXT NOT NULL, an TEXT NOT NULL, nachricht TEXT NOT NULL );
CREATE INDEX mehrere_spalten ON nachrichten(von, an);
Anfrageplan für Index über mehrere Spalten
EXPLAIN SELECT * FROM nachrichten WHERE von='absender' AND an='empfaenger';
Sortierreihenfolge für B-Tree-Index
ORDER BY spalte ASC NULLS LAST ORDER BY spalte DESC NULLS FIRST
Index mit umgekehrter Sortierreihenfolge anlegen
CREATE INDEX index_name ON tabelle(spalte NULLS FIRST);
CREATE INDEX index_name ON tabelle(spalte DESC NULLS LAST);
Sortierreihenfolge beim Index über mehrere Spalten
ORDER BY von ASC, an ASC ORDER BY von DESC, an DESC
Indexnutzung nicht möglich
ORDER BY von ASC, an DESC ORDER BY von DESC, an ASC
Index mit umgekehrter Sortierreihenfolge anlegen
CREATE INDEX nachrichten_sortieren ON nachrichten(von ASC, an DESC);
CREATE INDEX nachrichten_sortieren ON nachrichten(von DESC, an ASC);
Partiellen Index anlegen
CREATE INDEX log_fehler ON logdaten(fehler) WHERE fehler = TRUE;
Größe des Index ermitteln
SELECT pg_relation_size('log_fehler');
SELECT pg_size_pretty(pg_relation_size('log_fehler'));
Beispiel für einen funktionalen Index
CREATE TABLE adressen ( vorname TEXT NOT NULL, nachname TEXT NOT NULL );
INSERT INTO adressen (vorname, nachname) VALUES ('Andreas', 'Scherbaum');
SELECT vorname, nachname FROM adressen WHERE LOWER(nachname) = 'scherbaum';
Funktionalen Index erzeugen
CREATE INDEX adressen_nachname_klein ON adressen ((LOWER(nachname)));
Funktionalen Index beim Vergleich nutzen
SELECT vorname, nachname FROM adressen WHERE LOWER(nachname) = LOWER('SCHERBAUM');
Tabelle mit Texten und Prüfsummen erstellen
CREATE TABLE texte ( id INTEGER PRIMARY KEY, der_text TEXT NOT NULL );
CREATE INDEX texte_der_text_pruefsumme ON texte ((MD5(der_text)));
Prüfsumme statt Text vergleichen
SELECT id, der_text FROM texte WHERE MD5(der_text) = MD5('der neue Text');
Index auf anderen Tablespace erzeugen
CREATE INDEX daten_id ON daten(id) TABLESPACE ts_index;
Index auf anderen Tablespace verschieben
ALTER INDEX daten_id SET TABLESPACE ts_neu;
Füllfaktor für einen Index angeben
CREATE INDEX daten_id ON daten(id) WITH FILLFACTOR = 50;
Namen des Index ermitteln
\d daten
Index löschen
DROP INDEX daten_id;
Bedingtes Entfernen des Index
DROP INDEX IF EXISTS daten_id;
Index löschen
SELECT idx_stat.relname AS tabelle, indexrelname AS index_name, idx_stat.idx_scan AS index_nutzung FROM pg_stat_user_indexes AS idx_stat JOIN pg_indexes ON indexrelname = indexname JOIN pg_stat_user_tables AS tab_stat ON idx_stat.relname = tab_stat.relname WHERE idx_stat.idx_scan < 50 AND indexdef !~* 'unique' ORDER BY idx_stat.relname, indexrelname;
Danke an Josh Berkus für die ursprüngliche Idee.
Index anhand einer Spalte anordnen
CREATE TABLE cluster_test (id serial primary key, daten text);
CLUSTER cluster_test_pkey ON cluster_test;
ANALYZE cluster_test;
CLUSTER cluster_test;
Einen Tablespace anlegen
CREATE TABLESPACE ts_neu LOCATION '/mnt/db_neu';
Tablespace anlegen und einem Nutzer übergeben
CREATE TABLESPACE ts_neu OWNER anderer_user LOCATION '/mnt/db_neu';
Eigentümer eines Tablespace ändern
ALTER TABLESPACE ts_neu OWNER TO anderer_user;
Einen Tablespace umbenennen
ALTER TABLESPACE ts_neu RENAME TO ts_umbenannt;
Tablespace gezielt nutzen
CREATE TABLE test1 (id INTEGER) TABLESPACE ts_1;
CREATE TABLE test2 (id INTEGER) TABLESPACE ts_2;
Tablespace gezielt nutzen
CREATE INDEX test1_index ON test1(id) TABLESPACE ts_2;
Objekt auf anderen Tablespace umziehen
ALTER TABLE test1 SET TABLESPACE ts_2;
ALTER INDEX test1_index SET TABLESPACE ts_1;
Tablespaces anzeigen
\db
Datenbank in anderem Tablespace anlegen
CREATE DATABASE neue_db TABLESPACE = ts_2;
Default-Tablespace ändern
SET default_tablespace = ts_2;
Default-Tablespace angeben
SET default_tablespace = ts_2;
SET temp_tablespace = ts_2;
Datenbank verschieben
ALTER DATABASE eine_db SET TABLESPACE ts_2;
Tablespace löschen
DROP TABLESPACE ts_2;
Tablespace löschen
DROP TABLESPACE IF EXISTS ts_1;
Implizite Transaktion
BEGIN;
COMMIT
Keine weiteren Befehle bei einem Fehler
BEGIN;
SELECT fehler;
SELECT 1;
ROLLBACK;
Rollback nach einem Fehler
CREATE TABLE fehlertest ( id INTEGER, daten VARCHAR NOT NULL);
BEGIN;
Rollback nach einem Fehler
INSERT INTO fehlertest (id) VALUES (1);
SELECT 1;
ROLLBACK;
SQL-Fehler brechen ebenfalls die Transaktion ab
BEGIN;
SELECT fehler;
COMMIT;
Savepoints sind Rücksprungadressen
BEGIN;
SAVEPOINT punkt1;
SELECT fehler;
ROLLBACK TO SAVEPOINT punkt1;
SELECT 1+2;
ROLLBACK;
Zu einem Savepoint zurückrollen
ROLLBACK TO SAVEPOINT punkt1;
ROLLBACK WORK TO SAVEPOINT punkt1;
ROLLBACK TRANSACTION TO SAVEPOINT punkt1;
ROLLBACK TO punkt1;
Savepoint freigeben
RELEASE SAVEPOINT punkt1;
RELEASE punkt1;
Transaktionslevel setzen
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Transaktionslevel setzen
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Transaktionslevel als Vorgabe setzen
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Transaktion read-only setzen
CREATE TABLE schreib_test (daten VARCHAR);
BEGIN;
SET TRANSACTION READ ONLY;
INSERT INTO schreib_test (daten) VALUES ('abc');
Transaktion wieder read-write setzen
SET TRANSACTION READ WRITE;
Voreinstellung für Transaktionslevel setzen
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
BEGIN;
INSERT INTO schreib_test (daten) VALUES ('abc');
Beispieltabelle erstellen
CREATE TABLE t1 (id INTEGER);
INSERT INTO t1 SELECT generate_series(1, 1000000);
SELECT id FROM t1;
Daten aus Beispieltabelle löschen
DELETE FROM t1;
Daten aus Beispieltabelle löschen
INSERT INTO t1 SELECT generate_series(1, 1000000);
TRUNCATE t1;
Daten aus verknüpften Tabellen löschen
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INTEGER PRIMARY KEY);
CREATE TABLE t2 (id INTEGER PRIMARY KEY, fkey INTEGER NOT NULL REFERENCES t1(id));
INSERT INTO t1 (id) VALUES (1),(2),(3);
INSERT INTO t2 (id, fkey) VALUES (1, 1);
TRUNCATE t1;
TRUNCATE t1, t2;
Daten aus verknüpften Tabellen löschen
TRUNCATE t1 CASCADE;
Transaktionssicherheit und TRUNCATE
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INTEGER);
INSERT INTO t1 SELECT generate_series(1, 20);
SELECT id FROM t1;
Transaktionssicherheit und TRUNCATE
START TRANSACTION;
TRUNCATE t1;
SELECT id FROM t1;
Transaktionssicherheit und TRUNCATE
ROLLBACK;
SELECT id FROM t1;
Zurücksetzen von Sequenzen
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id SERIAL, daten INTEGER);
INSERT INTO t1 (daten) SELECT generate_series(1, 10);
INSERT INTO t1 (daten) VALUES (1);
SELECT id,daten FROM t1;
Zurücksetzen von Sequenzen
TRUNCATE t1;
INSERT INTO t1 (daten) VALUES (1);
SELECT id,daten FROM t1;
Zurücksetzen von Sequenzen
TRUNCATE t1 RESTART IDENTITY;
INSERT INTO t1 (daten) VALUES (1);
SELECT id,daten FROM t1;
Beispieltabelle erstellen
CREATE TABLE copy_from_test ( id INTEGER PRIMARY KEY CHECK (id > 0), daten TEXT NOT NULL );
Beispieldatei mit Daten
cat /tmp/copy_from_test_1.txt 1 Datensatz 1 2 Datensatz 2 5 Datensatz 5 4 Datensatz 4
Beispieldaten mit COPY schreiben
COPY copy_from_test FROM '/tmp/copy_from_test_1.txt';
SELECT id, daten FROM copy_from_test;
Beispieldaten mit COPY von STDIN schreiben
COPY copy_from_test FROM STDIN;
<codeSELECT id, daten FROM copy_from_test;</code>
Beispieldaten zum Schreiben mit psql
cat copy_from_test_2.txt 25 Datensatz 10 26 Datensatz 11
chmod go-rwx copy_from_test_2.txt
Beispieldaten mit COPY und psql schreiben
\copy copy_from_test FROM 'copy_from_test_2.txt'
SELECT id, daten FROM copy_from_test;
Einzelne Spalten mit COPY befüllen
CREATE TABLE copy_from_test_2 ( id SERIAL PRIMARY KEY, daten TEXT NOT NULL );
COPY copy_from_test_2 (daten) FROM STDIN;
SELECT id, daten FROM copy_from_test_2;
Export der Beispieldaten
COPY copy_from_test TO '/tmp/copy_to_test_1.txt';
Beispieldaten auf STDOUT ausgeben
COPY copy_from_test TO STDOUT;
Beispieldaten mit psql auslesen
\copy copy_from_test TO '/home/ads/copy_to_test_2.txt'
cat /home/ads/copy_to_test_2.txt 1 Datensatz 1 2 Datensatz 2 ... 25 Datensatz 10 26 Datensatz 11
Beispieldaten im Binärformat ausgeben
COPY copy_from_test TO STDOUT BINARY;
Beispieldaten im CSV-Format ausgeben
COPY copy_from_test TO STDOUT WITH CSV HEADER DELIMITER AS ';' QUOTE AS '"';
Daten einer SELECT-Anweisung ausgeben
COPY ( SELECT generate_series(1, 4) AS Zaehler, RANDOM() AS Zufall ) TO STDOUT;
Für ein bestimmtes Signal anmelden
LISTEN daten_aktualisieren;
Signal versenden
NOTIFY daten_aktualisieren;
Signal abbestellen
UNLISTEN daten_aktualisieren;
Signale festlegen
$db->do(qq{listen "daten_aktualisieren";});
Erhaltene Signale auswerten
my ($notifies); $notifies = $db->func('pg_notifies'); while ($notifies) { my ($n, $p) = @$notifies; # $n ist der Signalname, also hier "daten_aktualisieren" # $p ist die PID des Backends, das die Nachricht ausgelöst hat # ... weitere Bearbeitung des Signals }
Kommentare zu Objekten hinzufügen
COMMENT ON DATABASE finanzen IS 'Diese Datenbank enthält die Buchhaltung';
COMMENT ON SCHEMA daten IS 'Dieses Schema enthält Applikationsdaten';
COMMENT ON TABLE daten.konto IS 'Diese Tabelle enthält Kontodaten';
COMMENT ON INDEX daten.konto_name IS 'Dieser Index beschleunigt Suchen nach den Kontonamen';
Kommentare in psql anzeigen
\dd daten.konto_name
Kommentare löschen
COMMENT ON INDEX daten.konto_name IS NULL;
\dd daten.konto_name
Beispiel für die Volltextsuche
CREATE TABLE volltextsuche ( id SERIAL NOT NULL PRIMARY KEY, text_deutsch TEXT NOT NULL, text_geparst TSVECTOR );
CREATE INDEX vt_suche ON volltextsuche USING gist(text_geparst);
INSERT INTO volltextsuche (text_deutsch, text_geparst) VALUES ('Ein Text ueber Katzen und Elefanten', to_tsvector('german', 'Ein Text ueber Katzen und Elefanten'));
Alternatives Beispiel für die Volltextsuche
CREATE TABLE volltextsuche ( id SERIAL NOT NULL PRIMARY KEY, text_deutsch TEXT NOT NULL );
CREATE INDEX vt_suche ON volltextsuche USING gist(to_tsvector('german', text_deutsch));
INSERT INTO volltextsuche (text_deutsch) VALUES ('Ein Text ueber Katzen und Elefanten');
Inhalt der Beispieltabelle
SELECT text_deutsch FROM volltextsuche;
SELECT text_geparst FROM volltextsuche;
Position der Stoppwortdateien
pg_config --sharedir
ls /usr/share/postgresql/8.3/tsearch_data/
Volltextsuche
SELECT id, text_deutsch FROM volltextsuche WHERE text_geparst @@ to_tsquery('german', 'Elefant');
UND-Verknüpfung
SELECT id, text_deutsch FROM volltextsuche WHERE text_geparst @@ to_tsquery('german', 'Elefant & Katze');
UND-Verknüpfung
SELECT id, text_deutsch FROM volltextsuche WHERE text_geparst @@ to_tsquery('german', 'Elefant & Maus');
ODER-Verknüpfung
SELECT id, text_deutsch FROM volltextsuche WHERE text_geparst @@ to_tsquery('german', 'Elefant | Maus');
Negierte Suche
SELECT id, text_deutsch FROM volltextsuche WHERE text_geparst @@ to_tsquery('german', '!Elefant');
Beispieltabelle auffüllen
INSERT INTO volltextsuche (text_deutsch, text_geparst) VALUES ('Der kleine Elefant und der grosse Elefant', to_tsvector('german', 'Der kleine Elefant und der grosse Elefant'));
INSERT INTO volltextsuche (text_deutsch, text_geparst) VALUES ('Baby Elefant, kleiner Elefant, Mama Elefant', to_tsvector('german', 'Baby Elefant, kleiner Elefant, Mama Elefant'));
SELECT text_deutsch FROM volltextsuche;
Ein Ranking erstellen
SELECT text_deutsch, ts_rank_cd(text_geparst, query) AS rank FROM volltextsuche, to_tsquery('german', 'Elefant') query WHERE text_geparst @@ query ORDER BY rank DESC;
Basistabelle erstellen
CREATE TABLE adressen ( id SERIAL, name TEXT, adresse TEXT;
Vererbte Tabellen anlegen
CREATE TABLE mitarbeiter ( personalnummer INTEGER, krankenkasse TEXT ) INHERITS (adressen);
CREATE TABLE mitarbeiter_aussendienst ( bezirk TEXT ) INHERITS (mitarbeiter);
CREATE TABLE mitarbeiter_innendienst ( arbeitszeit TEXT ) INHERITS (mitarbeiter);
CREATE TABLE lieferanten ( kundennummer TEXT ) INHERITS (adressen);
CREATE TABLE kunden ( kundennummer TEXT ) INHERITS (adressen);
Daten einfügen
INSERT INTO kunden (name, adresse, kundennummer) VALUES ('Reifen-Wagner', 'Bahnhofsplatz 2', '10045'), ('Auspuff-Fritze', 'Parkallee 5', '10037'), ('Edelautos GmbH', 'Am Hafen 17', '10175');
INSERT INTO lieferanten (name, adresse, kundennummer) VALUES ('Fleischerei an der Ecke', 'Um die Ecke 1', '70029'), ('Party-Service Nr. 1', 'Am Wegekreuz 2', '70036');
Vererbte Daten auslesen
SELECT id, name, adresse, kundennummer FROM kunden;
Alle Daten auslesen
SELECT id, name, adresse FROM adressen;
Basistabelle erstellen
CREATE TABLE log_daten ( id SERIAL NOT NULL, ts TIMESTAMP NOT NULL, data VARCHAR(100) NOT NULL);
CREATE INDEX log_daten_ts ON log_daten(ts);
Tabellen für jeden Monat erstellen
CREATE TABLE log_daten_200902 ( CHECK (ts >= '2009-02-01' AND ts < '2009-03-01') ) INHERITS (log_daten);
CREATE INDEX log_daten_200902_ts ON log_daten_200902(ts);
CREATE TABLE log_daten_200903 ( CHECK (ts >= '2009-03-01' AND ts < '2009-04-01') ) INHERITS (log_daten);
CREATE INDEX log_daten_200903_ts ON log_daten_200903(ts);
Logdaten einfügen
INSERT INTO log_daten_200902 (ts, data) VALUES ('2009-02-05', 'Benutzer eingeloggt'), ('2009-02-06', 'E-Mail versandt'), ('2009-02-06', 'Neues Passwort erstellt'), ('2009-02-27', 'Account deaktiviert');
INSERT INTO log_daten_200903 (ts, data) VALUES ('2009-03-02', 'Neuer Account erstellt'), ('2009-03-08', 'Benutzer eingeloggt'), ('2009-03-13', 'Erinnerung versandt');
Logdaten auslesen
SELECT id, ts, data FROM log_daten ORDER BY ts;
constraint_exclusion deaktiviert
EXPLAIN SELECT id, ts, data FROM log_daten WHERE ts = '2009-03-13' ORDER BY ts;
constraint_exclusion aktivieren
-- PostgreSQL Version bis 8.3 SET constraint_exclusion TO on;
-- PostgreSQL Version ab 8.4 SET constraint_exclusion TO partitioned;
constraint_exclusion aktiviert
EXPLAIN SELECT id, ts, data FROM log_daten WHERE ts = '2009-03-13' ORDER BY ts;
Alte Logdaten löschen
DROP TABLE log_daten_200902;
RULE erstellen
CREATE RULE log_daten_200903_insert AS ON INSERT TO log_daten WHERE ts >= '2009-03-01' AND ts < '2009-04-01' DO INSTEAD INSERT INTO log_daten_200903 (id, ts, data) VALUES (new.id, new.ts, new.data);
Daten in Basistabelle schreiben
INSERT INTO log_daten (ts, data) VALUES ('2009-03-15', 'Benutzer ausgeloggt');
Logdaten aus abgeleiteter Tabelle auslesen
SELECT id, ts, data FROM log_daten_200903 ORDER BY ts;
Weitere Rules
CREATE RULE log_daten_200903_update AS ON UPDATE TO log_daten WHERE ts >= '2009-03-01' AND ts < '2009-04-01' DO INSTEAD UPDATE log_daten_200903 t SET id = new.id, ts = new.ts, data = new.data WHERE t.id = new.id;
CREATE RULE log_daten_200903_delete AS ON DELETE TO log_daten WHERE ts >= '2009-03-01' AND ts < '2009-04-01' DO INSTEAD DELETE FROM log_daten_200903 t WHERE t.id = old.id;
Trigger-Funktion erstellen
CREATE OR REPLACE FUNCTION log_daten_trigger() RETURNS TRIGGER AS $$ BEGIN IF NEW.ts >= '2009-02-01' AND NEW.ts < '2009-03-01' THEN INSERT INTO log_daten_200902 VALUES (NEW.*); ELSIF NEW.ts >= '2009-03-01' AND NEW.ts < '2009-04-01' THEN INSERT INTO log_daten_200903 VALUES (NEW.*); END IF; -- verhindere Schreiben in Basistabelle RETURN NULL; END; $$ LANGUAGE 'plpgsql' STRICT;
Rules entfernen, Trigger erstellen
DROP RULE log_daten_200903_insert ON log_daten;
CREATE TRIGGER log_daten_tg BEFORE INSERT ON log_daten FOR EACH ROW EXECUTE PROCEDURE log_daten_trigger();
Daten in Basistabelle schreiben
INSERT INTO log_daten (ts, data) VALUES ('2009-03-18', 'Benutzer eingeloggt');
Logdaten aus abgeleiteter Tabelle auslesen
SELECT id, ts, data FROM log_daten_200903 ORDER BY ts;