Neues in PostgreSQL 11

PostgreSQL 11 erschien vor vier Monaten – meine Begutachtung ist also längst überfällig.

Mit Blick auf den SQL-Standard ist bei PostgreSQL 11 die Over-Klausel das Hauptthema. Über fast acht Jahre, von 2009 bis 2017, war PostgreSQL die einzige gängige, quelloffene und kostenlose SQL-Datenbank, die Fensterfunktionen (window functions) unterstützt hat. Nur ein Jahr später, im September 2018, hatten alle quelloffenen Konkurrenten aufgeschlossen und sind teilweise an PostgreSQL vorbeigezogen. Die PostgreSQL-Gemeinde war allerdings darauf vorbereitet. PostgreSQL 11 erschien noch 2018 und hat die Führungsposition wiederhergestellt und sogar ausgebaut.0

Dieser Artikel erklärt dieses Rennen und geht auch auf weitere Neuerungen in PostgreSQL 11 ein.

Inhalt:

1. Vollständige SQL:2011 Over-Klausel
2. Create Procedure und Call
3. Parameter in Fetch First|Next
4. Relative XPath-Ausdrücke
5. Andere Neuerungen
6. Extra: Create Index … Include

Vollständige SQL:2011 Over-Klausel

Die Over-Klausel legt fest, welche Zeilen für eine Fensterfunktion sichtbar sind. Fensterfunktionen wurden mit SQL:2003 standardisiert und werden von PostgreSQL seit Version 8.4 (2009) unterstützt. In manchen Bereichen war PostgreSQL weniger vollständig als andere (Range-Rahmen, ignore nulls). Andere Bereiche unterstützte PostgreSQL als erste gängige Datenbank (die Window-Klausel). Kurz gesagt: PostgreSQL konnte mit den kommerziellen Mitbewerbern mithalten und war die einzige gängige und kostenlose SQL-Datenbank, die Fensterfunktionen überhaupt angeboten hat.

Bis vor Kurzem. MariaDB führte Fensterfunktionen „schon“ in 2017 ein. MySQL und SQLite folgten in 2018. Die MySQL-Umsetzung war zu diesem Zeitpunkt sogar vollständiger als die von PostgreSQL. Ein Rückstand den PostgreSQL 11 wieder aufgeholt hat. Darüber hinaus unterstützt PostgreSQL 11 weitere Funktionen der Over-Klausel: die Rahmeneinheit groups und Ausschlüsse aus dem Rahmen (frame exclusion). Diese werden bisher von keinem anderen gängigen Produkt unterstützt – weder von quelloffenen, noch von kommerziellen.

Damit ist die einzige Funktion der Over-Klausel, die PostgreSQL 11 nicht unterstützt, die Pattern-Klausel. Diese wurde erst mit SQL:2016 in den Standard aufgenommen und führt eine Rahmung mittels regulärer Ausdrücke durch. Bisher unterstützt keine gängige Datenbank diese Rahmung.1

Rahmeneinheiten

Bevor wir uns ansehen, was in PostgreSQL 11 neu ist, betrachten wir einen häufigen Anwendungsfall für Fensterfunktionen. Danach wenden wir uns der sogenannten Rahmung (framing) der Over-Klausel zu.

Das Beispiel berechnet die fortlaufende Summe über die Spalte Betrag. Das heißt, es wird die Summe von Anfang des Ergebnisses – entsprechend der Order By-Klausel – bis zur aktuellen Zeile gebildet.

SELECT SUM(Betrag)
       OVER(ORDER BY id
            ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW
           ) laufende_Summe
  FROM …

Die Aggregatfunktion sum wird hier mit der Over-Klausel anstatt einer Group By-Klausel verwendet – das macht sie zur Fensterfunktion. Der interessante Teil dieses Beispiels ist die Rahmung, die fett gedruckt ist.

Eine Rahmung ist eine zusätzliche Einschränkung eines sortierten Fensters, auf die Zeilen zwischen einer Start- und End-Zeile.

Die allgemeine Syntax ist:

<Einheit> BETWEEN <Rahmengrenze>
              AND <Rahmengrenze>
[<Ausschluss>]

Sehen wir uns dabei zuerst die Rahmengrenzen an – sie definieren die Start- und End-Zeile des Rahmens.

Die Rahmengrenzen können entweder relativ zur aktuellen Zeile, oder als „unbegrenzt“ (unbounded) festgelegt werden. Unbegrenzt bedeutet dabei den Beginn oder das Ende des Ergebnisses oder der aktuellen Partition.

  CURRENT ROW
| <Distanz> (PRECEDING|FOLLOWING)
| UNBOUNDED (PRECEDING|FOLLOWING)

Das folgende Beispiel nutzt relative Rahmengrenzen:

<Einheit> BETWEEN 1 PRECEDING AND CURRENT ROW

Um relativen Rahmengrenzen wirklich zu verstehen, muss man auch wissen, wie die drei Rahmeneinheiten – rows, range und groups – die Bedeutung dieser Grenzen ändern.

Die Einheit rows bedeutet genau das, was man erwartet: Sie interpretiert current row als Referenz auf die aktuelle Zeile und die <Distanz> in preceding und following als Zeilenzahlen. Mit der Einheit rows definiert das Beispiel einen Rahmen mit bis zu zwei Zeilen: die aktuelle Zeile selbst, und noch eine davor, falls es eine gibt. Gibt es keine vorherige Zeile, enthält der Rahmen nur die aktuelle Zeile selbst.

Bei der nächsten Einheit, range, geht es nicht um Zeilenzahlen, sondern um die Differenz des Sortierwertes – d. h. des Ausdruckes in der Order By-Klausel. Alle Zeilen, bei denen die Differenz des Sortierwertes zur aktuellen Zeile kleiner oder gleich dem angegebenen Distanz-Wert sind, fallen in den Rahmen.

Beachte dabei, dass current row als Range-Bereichsgrenze alle Zeilen mit dem Wert der aktuellen Zeile bezeichnet. Das können auch sehr viele Zeilen sein. Current row entspricht letztendlich 0 preceding oder 0 following.2 Bei der Einheit range wären die Begriffe „gleich platzierte“ (current peers) oder „aktueller Wert“ (current value) wohl treffender.

Dieses Beispiel ist bereits jene Funktionalität, die zwar in MySQL 8.0, nicht aber in PostgreSQL vor Version 11 funktioniert. Obwohl PostgreSQL die Einheit range grundsätzlich schon zuvor unterstützte, konnte man sie nicht mit einer <Distanz> verwenden. Erlaubt waren lediglich die Grenzen unbounded und current row. Diese Einschränkung haben SQL Server und SQLite übrigens noch immer. PostgreSQL 11 unterstützt alle Rahmeneinheiten mit allen Grenztypen.

Sogar die letzte Rahmeneinheit, groups, wird von PostgreSQL 11 vollständig unterstützt. Diese Einheit weist jede Zeile des Ergebnisses oder der Partition einer Gruppe zu, so wie es auch die Group By-Klausel macht. Das heißt, dass Zeilen mit gleichen Werten derselben Gruppe zugeordnet werden. Die <Distanz> bezeichnet dann die Anzahl der Gruppen – d. h. die Anzahl der verschiedenen Sortierwerte – die in den Rahmen fallen.

Das folgende Beispiel zeigt, wie die Einheit groups einen anderen Wert vor dem aktuellen Wert (1 preceding) und alle Zeilen mit dem aktuellen Wert selbst (current row) umfasst. Dabei spielt weder die Differenz der Werte noch die Anzahl der Zeilen eine Rolle. Bei der Einheit groups geht es nur um die Anzahl der unterschiedlichen Werte.

PostgreSQL 11 die erste gängige SQL-Datenbank, die die Rahmeneinheit groups unterstützt.

Ausschluss aus dem Rahmen (frame exclusion)

Eine weitere Funktion, die bisher von keiner anderen gängigen SQL-Datenbank umgesetzt wird, ist der Ausschluss aus dem Rahmen (frame exclusion). Diese Funktion entfernt Zeilen aus dem Rahmen, die in Verbindung mit der aktuellen Zeile stehen.

Die Voreinstellung ist exclude no others – sie entfernt keine Zeilen aus dem Rahmen.

Die nächste Option schließt die aktuelle Zeile selbst aus dem Rahmen aus.

EXCLUDE CURRENT ROW

Beachte, dass die Bedeutung der Exclude-Klausel nicht von der gewählten Rahmeneinheit abhängig ist. Exclude current row entfernt tatsächlich nur die aktuelle Zeile, selbst wenn die Rahmeneinheit range oder groups verwendet wird und es gleich platzierte Zeilen gibt. Die Bedeutung current row hängt also davon ab, ob es als Rahmengrenze oder Ausschluss verwendet wird.

Um die aktuelle Zeile selbst samt aller gleich platzierten auszuschließen, kann man exclude group verwenden.

EXCLUDE GROUP

Auch hierbei ist die Bedeutung unabhängig von der gewählten Rahmeneinheit. Selbst bei der Einheit rows werden alle Zeilen mit demselben Sortierwert ausgeschlossen.

Zu guter Letzt gibt es noch exclude ties. Damit werden alle gleich platzierten Zeilen, nicht aber die aktuelle Zeile selbst ausgeschlossen.

EXCLUDE TIES

Kompatibilität

1. Keine Verkettung von Fensterdefinitionen
2. Keine <Distanz> (nur unbounded und current row)
3. Keine temporalen Datums- und Zeit-Typen
4. Keine <Distanz> (nur unbounded und current row) und keine Datums- und Zeit-Typen

Create Procedure und Call

PostgreSQL unterstützt benutzerdefinierte Funktionen seit Langem. Create procedure wurde vor PostgreSQL 11 jedoch nicht angeboten. Stattdessen wurden oft Funktionen mit returns void verwendet.

Die neuen Prozeduren, die mit PostreSQL 11 eingeführt wurden, unterscheiden sich in einigen Aspekten von Funktionen:

• Sie haben keinen Rückgabewert

• Sie werden mittels call und nicht über einem SQL-Ausdruck aufgerufen

• Sie können Transaktionskontrollanweisungen beinhalten (insb. commit und rollback).3

Über Prozeduren habe ich an dieser Stelle nicht viel mehr zu sagen, als dass daran noch gearbeitet wird. Das gilt insbesondere für Treiber wie JDBC.

1. Nicht genau nach der Standard-Syntax
2. Proprietäre Syntax: <varname> in|out|in out <type name>
3. Proprietäre Syntax: <varname> <type name> (ohne Parametermodus)
4. Proprietäre Syntax: <varname> <type name> output (output verhält sich wie inout)
5. Wird vom aktuellen JDBC-Treiber nicht unterstützt (daher kann ich nicht testen)
6. Unterstützt auch := anstatt des Schlüsselwortes default
7. Verwende = anstatt des Schlüsselwortes default
8. Mein Testfall scheint am JDBC-Treiber zu scheitern
9. Unter Verwendung von exec … <param>=<value>
10. Die Syntax wird akzeptiert, die Semantik wurde nicht getestet

Trotz dieser langen Supportmatrix gibt es einige Bereiche, die ich nicht getestet habe:

• Sicherheit (T323, T324)

• Das Verhalten von drop restrict|cascade (F032)

• Zyklische Abhängigkeiten (T655)

• Array und Multisets als Parameter (S201, S202)

• Dynamisches SQL in Routinen (T652)

• Schemaänderungen in Routinen (T651)

• Überladen von Routinen (T341)

• Proprietäre Erweiterungen wie alter procedure, drop routine, Transaktionskontrollanweisungen in Routinen usw.

Parameter in Fetch First|Next

Fetch first|next ist die SQL-Standardsyntax für die weitverbreitete – aber proprietäre – Limit-Klausel. Diese Funktion wurde mit SQL:2008 eingeführt und bereits 2009 von PostgreSQL 8.4 unterstützt.

Es gab jedoch einen kleinen Haken: Wenn man die Anzahl der zu liefernden Zeilen nicht mit Ziffern, sondern mit einem Parameter angeben wollte, musste man den Parameter in Klammern setzen.

FETCH FIRST ($1) ROWS ONLY

Wenn man das weiß, ist es natürlich kein großes Problem. Wenn man das nicht weiß, kann einem die Fehlermeldung „syntax error at or near "$1"“ in den Wahnsinn treiben.

PostgreSQL 11 akzeptiert Parameter (und Ausdrücke) auch ohne Klammern.

1. Benötigt offset (z. B. offset 0 rows) • Benötigt order by
2. Verwende eine Verschachtelung: CREATE VIEW … AS SELECT … FROM (SELECT … FROM … FETCH FIRST …) t
3. Erlaubt keine Klammern: (?)
4. Verwende die proprietäre Syntax: select top … percent
5. Nur mit rows, nicht mit percent
6. Verwende die proprietäre Syntax: select top … with ties
7. Nicht für 0 (Null)

Relative XPath-Ausdrücke

Ein weiteres Ärgernis, mit dem man gut leben konnte, wenn man davon wusste, ist, dass PostgreSQL XPath-Ausdrücke früher relativ zum Wurzelknoten des XML-Dokumentes interpretiert hat. Klingt doch richtig, oder? Nicht wirklich, es sollte relativ zum Dokumentenknoten sein.

Betrachte das folgende Beispiel, das die Funktion Xmltable verwendet – eingeführt mit PostgreSQL 10 –, um ein XML-Dokument in Zeilen und Spalten umzuwandeln.

SELECT c
  FROM (VALUES ('<root>
                  <c>c1</c>
                  <c>c2</c>
                </root>'::xml
               )
       ) t(x)
     , XMLTABLE ('root/c' -- XPath-Ausdruck
                 PASSING x
                 COLUMNS c TEXT PATH '.'
                )

Wenn man den XPath-Ausdruck 'root/c' relativ zum Dokumentenknoten interpretiert, wie es der Standard vorsieht, trifft dieser Ausdruck beide <c>-Elemente in <root>.

Bis zu Version 10 hat PostgreSQL solche Ausdrücke relativ zum Wurzelknoten <root> interpretiert, sodass dieser Ausdruck ins Leere geht. Bei betroffenen Versionen musste man für dasselbe Ergebnis entweder den relativen XPath-Ausdruck 'c', oder besser, den absoluten Ausdruck '/root/c' verwenden.

Andere Neuerungen

Die oben erwähnten Neuerungen, die alle Standard-SQL-Funktionen betreffen, sind natürlich nur ein kleiner Teil Neuerungen in PostgreSQL 11. Für die vollständige Liste möchte ich auf die Releasenotes verweisen.

Eine kleine Vorschau kann ich mir aber nicht verkneifen.

Partitionierung

Die Möglichkeiten der Partitionierung sind nun nicht mehr deprimierend unvollständig. Neu in PostgreSQL 11:

• Primärschlüssel und Unique-Constraints über Partitionsgrenzen hinweg.

• Fremdschlüssel werden zumindest in eine Richtung unterstützt (von einer partitionierten auf eine nicht partitionierte Tabelle).

• Update-Anweisungen können Zeilen in andere Partitionen verschieben

• Default-Partitionen

• Hash-Partitionierung

Parallele Verarbeitung

Bestehende parallele Ausführungsschritte wurden optimiert (Hash Join, Seq Scan).

Einige weitere Anweisungen können parallel ausgeführt werden: Anlegen von B-Tree-Indizes, create table … as select, create materialized view.

Just In Time (JIT)

Ausdrücke in Abfragen können in nativen Code übersetzt werden, anstatt über den abstrakten Syntaxbaum interpretiert zu werden. Test haben gezeigt, dass Abfragen, die viele Ausdrücke verwenden, fast 30 % schneller laufen können.

Schnelles add column

Einer bestehenden Tabelle eine neue Spalte hinzuzufügen ist eine relativ häufige Aufgabe. Wenn die neue Spalte den Null-Wert als Default verwendet, konnte diese Operation schon in der Vergangenheit nur durch Änderung der Metadaten durchgeführt werden. PostgreSQL 11 erweitert diese Fähigkeit auf alle Spalten mit konstantem Default-Wert.

Quit und Exit in psql

PostgreSQL hat seine Lektion von „vi“ gelernt. Um aus der Pressemitteilung zu PostgreSQL 11 zu zitieren: “The inclusion of the keywords "quit" and "exit" in the PostgreSQL command-line interface to help make it easier to leave the command-line tool.”

Extra: Create Index … Include

Eine Neuerung in PostgreSQL 11 gibt es noch – diese hat sogar einen eigenen Artikel verdient: Create index … include. Dieser Artikel wurde auf Use The Index, Luke! veröffentlicht: Ein genauer Blick auf die Index Include-Klausel.

Wenn Du mehr über modernes SQL lernen willst, solltest du dir meine Schulung im Mai ansehen. Neben den oben erwähnten Fensterfunktionen behandle ich dort auch Rekursionen, Indizierung und verfestige die Grundlagen. Schulungsgrundlage ist der aktuelle Entwurf meines nächsten Buches. Schulung jetzt ansehen!