Identity-Spalten, Sequences und weshalb generierte IDs Lücken haben

Worum es geht

id bigint GENERATED ALWAYS AS IDENTITY (oder BY DEFAULT) deklariert eine Identity-Spalte. Die Dokumentation beschreibt sie als durch eine implizite Sequence gestützt: Bei ALWAYS wird ein von der Anwendung angegebener Wert nur akzeptiert, wenn das INSERT OVERRIDING SYSTEM VALUE angibt; bei BY DEFAULT hat ein angegebener Wert Vorrang. nextval rückt die Sequence weiter, und die Dokumentation zu den Sequence-Funktionen hält fest, dass ein durch nextval erhaltener Wert nicht zurückgewonnen wird, falls die aufrufende Transaktion später abbricht, dass ein INSERT mit ON CONFLICT die Zeile einschliesslich der nextval-Aufrufe berechnet, bevor der Konflikt erkannt wird, und dass Sequences deshalb keine lückenlose Nummerierung liefern können. Die Seite zu CREATE SEQUENCE ergänzt, dass eine CACHE-Einstellung über eins Werte pro Session vorab zuteilt, sodass unbenutzte Werte beim Ende der Session verloren gehen und Werte über Sessions hinweg ausser der Reihe sein können.

Warum es wichtig ist

Entwickelnde und Prüfende bemerken Lücken und vermuten verlorene Zeilen. Code, der annimmt, max(id) + 1 sei der nächste Wert, oder dass count(*) gleich max(id) sei, ist falsch. Nummerierungsvorschriften, die fortlaufende Nummern verlangen, lassen sich mit dem Primärschlüssel nicht erfüllen.

So wird es angewendet

• bigint GENERATED ALWAYS AS IDENTITY vorziehen; eine integer-Spalte stösst bei 2^31 - 1 (2 147 483 647) an ihre Grenze, und der Typ lässt sich später nur durch ein Neuschreiben der Tabelle ändern.
• IDs als undurchsichtig behandeln; nie eine Bedeutung aus Lücken oder Grösse ableiten und niemals das Zeilenvolumen darüber offenlegen, falls das eine Rolle spielt, sondern stattdessen extern UUIDs oder zufällige Tokens verwenden.
• Wird eine fortlaufende Nummer benötigt (Dokumentennummerierung), sie in dem Moment vergeben, in dem das Dokument endgültig wird, aus einer Zählerzeile, die in derselben Transaktion aktualisiert wird (UPDATE counters SET last = last + 1 WHERE name = 'invoice' RETURNING last); die Dokumentation zu CREATE SEQUENCE beschreibt einen solchen gesperrten Zähler als deutlich teurer als eine Sequence, was der Preis für die Garantie ist.
• Nach Massenladevorgängen mit expliziten IDs in eine BY DEFAULT-Identity-Spalte die Sequence zurücksetzen (SELECT setval(pg_get_serial_sequence('t', 'id'), max(id)) FROM t), sonst kollidiert der nächste generierte Wert.
• CACHE beim Standardwert 1 belassen, ausser die Sequence ist ein gemessener Engpass.

Stolpersteine

Änderungen durch setval sind für andere Sessions sofort sichtbar und werden durch ein Rollback nicht rückgängig gemacht. Serial-Spalten (serial, bigserial) sind eine Sequence plus ein Spalten-Default statt einer Identity-Spalte, weshalb explizite Werte nie zurückgewiesen werden. Die Dokumentation zu CREATE SEQUENCE hält fest, dass NO CYCLE der Standard ist: Ist das Maximum erreicht, gibt nextval einen Fehler zurück, statt umzuschlagen.