Open Source im professionellen Einsatz
Linux-Magazin 12/2015
© Pavel Timofeev, 123RF

© Pavel Timofeev, 123RF

PostgreSQL 9.5: Was ist neu und was besser?

Blick für Details

Wie erwartet gibt es auch in diesem Herbst eine neue Release des Open-Source-Datenbanksystems PostgreSQL. Sie bietet eine ganze Reihe überzeugender neuer Features.

1301

Das erste Highlight in PostgreSQL 9.5 ist die Erweiterung des »INSERT« -Befehls um die Klausel »ON CONFLICT DO UPDATE« , mit der sich bestimmen lässt, dass statt der »INSERT« -Anweisung ein »UPDATE« auszuführen ist, wenn die einzufügende Zeile schon existiert. Dieses Verhalten ist auch als so genannte Upsert-Funktionalität bekannt.

<C>UPSERT<C>

In der Praxis kommt dieser Fall sehr oft vor, der Anwender musste ihn aber bisher in PostgreSQL entweder durch eine Stored Procedure oder mit Hilfe einer Logik in der Clientanwendung von Hand selber implementieren. Die Schwierigkeit war, dass die Vorgaben im SQL-Standard und in anderen Datenbanksystemen erhebliche technische Probleme hervorriefen, die für die PostgreSQL-Entwickler nicht akzeptabel waren [1]. Die Logik eines »UPSERT« ist zwar sehr einfach, aber kompliziert wird es, wenn diese Logik im nebenläufigen Betrieb performant funktionieren muss, ohne Deadlocks oder sporadische Unique-Constraint-Fehler zu erzeugen, was bei anderen Implementierungen durchaus vorkommt.

Aus diesen Gründen hat sich das PostgreSQL-Projekt dazu entschlossen, extra eine eigene Syntax mit einer eigenen Semantik zu entwickeln. Hier ist ein Beispiel: Eine Tabelle speichert Abonnements für einen Dienst mit Benutzer-ID, Art des Abonnements sowie Start- und Ablaufdatum:

CREATE TABLE subscriptions (
  user_id int NOT NULL,
  subscription_type text NOT NULL,
  started date NOT NULL,
  expires date NOT NULL,
  PRIMARY KEY (user_id, subscription_type)
);

Eine »UPSERT« -Operation soll entweder einen neuen Eintrag einfügen oder einen bestehenden Eintrag aktualisieren. Bisher mussten Entwickler dazu eine Schleife zu Hilfe nehmen, die »INSERT« - oder »UPDATE« -Operationen so lange durchführte, bis eine fehlerfrei durchlief. Das gelang zum Beispiel mit einer PL/pgSQL-Funktion der Art, wie sie Listing 1 präsentiert. Die Schleife ist in diesem Beispiel notwendig, weil zwischen dem »UPDATE« und dem »INSERT« eine andere Verbindung eine dazu in Konflikt stehende Zeile einfügen könnte. Die Logik von Listing 1 verkürzt sich mit »UPSERT« auf einen einzigen neuen Befehl, wie ihn Listing 2 zeigt.

Listing 1

UPSERT-Ersatz

01 CREATE FUNCTION set_subscription(in_user_id int, in_subscription_type text, in_started date, in_expires date) RETURNS void
02 LANGUAGE plpgsql
03 STRICT
04 AS $$
05 BEGIN
06 <<upsert>>
07 LOOP
08     UPDATE subscriptions
09         SET (started, expires) = (in_started, in_expires)
10         WHERE (user_id, subscription_type) = (in_user_id, in_subscription_type);
11     EXIT upsert WHEN FOUND;
12     BEGIN
13         INSERT INTO subscriptions (user_id, subscription_type, started, expires)
14             VALUES (in_user_id, in_subscription_type, in_started, in_expires);
15         EXIT upsert;
16     EXCEPTION WHEN unique_violation THEN
17     END
18 END LOOP;
19 $$;

Der Befehl besteht zunächst aus einem normalen »INSERT« gefolgt von einer »ON CONFLICT« -Klausel, die den Konflikt und die auszuführende Aktion beschreibt. In diesem (typischen) Fall besteht der Konflikt in der Verletzung der Eindeutigkeit des Primärschlüssels, sobald versucht wird, denselben User mit demselben Abo noch einmal einzufügen. Die Aktion ist ein »UPDATE« -Befehl.

Listing 2

Mit UPSERT-Befehl

01 INSERT INTO subscriptions (user_id, subscription_type, started, expires)
02     VALUES (in_user_id, in_subscription_type, in_started, in_expires)
03     ON CONFLICT (user_id, subscription_id)
04         DO UPDATE SET (started, expires) = (in_started, in_expires);

Der neue Befehl spart, wie zu sehen ist, eine Menge Platz und ist noch dazu performanter, weil er die Problematik der Von-Hand-Implementierungen vermeidet, die darauf vorbereitet sein müssen, dass der Befehl wegen Aktivität in anderen Verbindungen fehlschlägt,

Wer vermeiden möchte, im »UPDATE« -Befehl die Eingabewerte aus der »VALUES« -Klausel zu wiederholen, kann auch das Alias »EXCLUDED« verwenden, um auf den Wert der Zeile zu verweisen, deren Einfügen gescheitert ist. Listing 3 zeigt ein Beispiel.

Listing 3

Mit EXCLUDED

01 INSERT INTO subscriptions (user_id, subscription_type, started, expires)
02     VALUES (in_user_id, in_subscription_type, in_started, in_expires)
03     ON CONFLICT (user_id, subscription_id)
04         DO UPDATE SET (started, expires) = (EXCLUDED.started, EXCLUDED.expires);

Andererseits ist es auch möglich, im »UPDATE« -Teil ganz andere Werte zu schreiben als im »INSERT« -Teil, wenn der Anwender zum Beispiel irgendwie aufzeichnen möchte, dass es einen Konflikt gegeben hat.

Während »UPSERT« eher für Entwickler von Webanwendungen und anderen transaktionalen Anwendungen enorme Vereinfachungen bringt, gibt es in PostgreSQL 9.5 auch Neuerungen im Bereich Analytik.

In der Gruppe

PostgreSQL unterstützt jetzt die aus anderen SQL-Systemen bekannten Gruppieroptionen »CUBE« und »ROLLUP« . Diese erweitern die bekannte »GROUP BY« -Klausel und erlauben das Ausführen von mehreren Gruppiervarianten auf einmal. Beispieldaten und einige Anwendungen der Gruppierfunktion liefert Listing 4.

Listing 4

Neue Gruppieroptionen

01 CREATE TABLE personal (
02     land text,
03     stadt text,
04     abteilung text,
05     name text
06 );
07
08 INSERT INTO personal VALUES
09     ('DE', 'Berlin', 'Vertrieb', 'Christian'),
10     ('DE', 'Berlin', 'Marketing', 'Sandra'),
11     ('DE', 'Frankfurt', 'Vertrieb', 'Stefan'),
12     ('AT', 'Innsbruck', 'Personal', 'Katrin'),
13     ('CH', 'Zürich', 'Vertrieb', 'Alexander');
14
15 SELECT land, count(*) FROM personal GROUP BY land;
16 SELECT land, stadt, count(*) FROM personal GROUP BY land, stadt;
17 SELECT abteilung, count(*) FROM personal GROUP BY abteilung;
18 SELECT land, abteilung, count(*) FROM personal GROUP BY land, abteilung;

Mit »CUBE« oder »ROLLUP« lassen sich derartige Anfragen kombinieren. Listing 5 zeigt ein Beispiel mit »ROLLUP« . Die Option gruppiert nach den angegebenen Spalten – wie das ein normales »GROUP BY« machen würde – sowie nach allen Präfixen der Spaltenliste, einschließlich der leeren Liste. Das ist besonders nützlich bei hierarchischen Strukturen. Zu sehen sind im Beispiel die Zahlen auf lokaler Abteilungsebene und auf den übergeordneten Organisationsebenen sowie die Gesamtzahl in einer einzigen Anfrage. Ohne »ROLLUP« müsste man das in separaten Anfragen ausführen (oder eventuell durch »UNION« zusammenkoppeln), was mühsam und langsamer wäre.

Listing 5

Ein Beispiel mit ROLLUP

01 >SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY ROLLUP (land, stadt, abteilung) ORDER BY 1, 2, 3;
02 -------------------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 -------------------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           |           |     1 |
08 | CH   | Zürich    | Vertrieb  |     1 |
09 | CH   | Zürich    |           |     1 |
10 | CH   |           |           |     1 |
11 | DE   | Berlin    | Marketing |     1 |
12 | DE   | Berlin    | Vertrieb  |     1 |
13 | DE   | Berlin    |           |     2 |
14 | DE   | Frankfurt | Vertrieb  |     1 |
15 | DE   | Frankfurt |           |     1 |
16 | DE   |           |           |     3 |

Die Option »CUBE« gruppiert nach allen möglichen Kombinationen der Gruppierspalten. Im Ergebnis zum passenden Beispiel in Listing 6 ist die weltweite Gesamtzahl in den verschiedenen Abteilungen zu sehen. Doch sind in diesem Beispiel nicht alle Zeilen des »CUBE« -Ergebnisses sinnvoll. Die Zeile »NULL, 'Berlin', NULL« etwa fasst alle Städte namens Berlin in allen Ländern zusammen. Das mag als Kuriosum interessant sein, aber in der Praxis wohl nicht.

Listing 6

Ein Beispiel mit CUBE

01 >SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY CUBE (land, stadt, abteilung) ORDER BY 1, 2, 3;
02 +------+-----------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 +------+-----------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           | Personal  |     1 |
08 | AT   |           |           |     1 |
09 | CH   | Zürich    | Vertrieb  |     1 |
10 | CH   | Zürich    |           |     1 |
11 | CH   |           | Vertrieb  |     1 |
12 | CH   |           |           |     1 |
13 | DE   | Berlin    | Marketing |     1 |
14 | DE   | Berlin    | Vertrieb  |     1 |
15 | DE   | Berlin    |           |     2 |
16 | DE   | Frankfurt | Vertrieb  |     1 |
17 | DE   | Frankfurt |           |     1 |
18 | DE   |           | Marketing |     1 |
19 | DE   |           | Vertrieb  |     2 |
20 | DE   |           |           |     3 |
21 |      | Berlin    | Marketing |     1 |
22 |      | Berlin    | Vertrieb  |     1 |
23 |      | Berlin    |           |     2 |
24 |      | Frankfurt | Vertrieb  |     1 |
25 |      | Frankfurt |           |     1 |
26 |      | Innsbruck | Personal  |     1 |
27 |      | Innsbruck |           |     1 |
28 |      | Zürich    | Vertrieb  |     1 |
29 |      | Zürich    |           |     1 |
30 |      |           | Marketing |     1 |
31 |      |           | Personal  |     1 |
32 |      |           | Vertrieb  |     3 |
33 |      |           |           |     5 |

Wer die Ausgabe noch genauer steuern möchte, kann die »GROUPING SETS« -Klausel verwenden, um die zu gruppierenden Kombinationen explizit anzugeben. »CUBE« und »ROLLUP« sind nämlich selbst einfach nur Kurzformen für bestimmte »GROUPING SETS« . Einen möglichen Einsatzfall dafür zeigt am Beispiel das Listing 7.

Listing 7

Beispiel mit GROUPING SETS

01 > SELECT land, stadt, abteilung, count(*) FROM personal GROUP BY GROUPING SETS((land, stadt, abteilung), (land, stadt), (land), (), (abteilung), (land, abteilung)) ORDER BY 1, 2, 3;
02 +------+-----------+-----------+-------+
03 | land |   stadt   | abteilung | count |
04 +------+-----------+-----------+-------+
05 | AT   | Innsbruck | Personal  |     1 |
06 | AT   | Innsbruck |           |     1 |
07 | AT   |           | Personal  |     1 |
08 | AT   |           |           |     1 |
09 | CH   | Zürich    | Vertrieb  |     1 |
10 | CH   | Zürich    |           |     1 |
11 | CH   |           | Vertrieb  |     1 |
12 | CH   |           |           |     1 |
13 | DE   | Berlin    | Marketing |     1 |
14 | DE   | Berlin    | Vertrieb  |     1 |
15 | DE   | Berlin    |           |     2 |
16 | DE   | Frankfurt | Vertrieb  |     1 |
17 | DE   | Frankfurt |           |     1 |
18 | DE   |           | Marketing |     1 |
19 | DE   |           | Vertrieb  |     2 |
20 | DE   |           |           |     3 |
21 |      |           | Marketing |     1 |
22 |      |           | Personal  |     1 |
23 |      |           | Vertrieb  |     3 |
24 |      |           |           |     5 |

Diesen Artikel als PDF kaufen

Express-Kauf als PDF

Umfang: 5 Heftseiten

Preis € 0,99
(inkl. 19% MwSt.)

Linux-Magazin kaufen

Einzelne Ausgabe
 
Abonnements
 
TABLET & SMARTPHONE APPS
Bald erhältlich
Get it on Google Play

Deutschland

Ähnliche Artikel

comments powered by Disqus

Stellenmarkt

Artikelserien und interessante Workshops aus dem Magazin können Sie hier als Bundle erwerben.