Clever Geek Handbook

PostgreSQL Antipatterns: Eindeutige IDs

Sehr oft muss ein Entwickler eindeutige Bezeichner für Datensätze in der PostgreSQL-Tabelle generieren - sowohl beim Einfügen als auch beim Lesen von Datensätzen.





Zählertabelle



Es scheint - was ist einfacher? Darin haben wir eine separate Platte aufgestellt - einen Eintrag mit einer Theke. Wir müssen einen neuen Bezeichner bekommen - von dort lesen, um einen neuen Wert zu schreiben - mach es UPDATE...



Tu das nicht ! Denn morgen müssen Sie Probleme lösen:





SEQUENCE-Objekt



Für solche Aufgaben stellt PostgreSQL eine separate Entität bereit - SEQUENCE. Es ist nicht transaktional, das heißt, es verursacht keine Sperren , aber zwei "parallele" Transaktionen erhalten mit Sicherheit unterschiedliche Werte .



Um die nächste ID aus einer Sequenz zu erhalten, verwenden Sie einfach die Funktion nextval: 



SELECT nextval('seq_name'::regclass);


Manchmal müssen Sie mehrere IDs gleichzeitig erhalten - zum Beispiel für das Streaming von Aufzeichnungen über COPY. Die Verwendung hierfür setval(currval() + N)ist grundsätzlich falsch ! Aus dem einfachen Grund, dass zwischen Aufrufen der Funktionen "inner" ( currval) und "Outer" ( setval) eine gleichzeitige Transaktion den aktuellen Wert der Sequenz ändern kann. Der richtige Weg ist, die nextvalerforderliche Anzahl von Malen aufzurufen : 



SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);


Serielles Pseudo



Es ist nicht sehr bequem, mit Sequenzen im "manuellen" Modus zu arbeiten. Unsere typische Aufgabe ist es jedoch, das Einfügen eines neuen Datensatzes mit einer neuen Sequenz-ID sicherzustellen! Speziell für diesen Zweck wurde PostgreSQL erfunden serial, das sich beim Generieren einer Tabelle zu so etwas "erweitert" . Es ist nicht erforderlich, sich den Namen der automatisch generierten Sequenz zu merken, die mit dem Feld verknüpft ist. Hierfür gibt es eine Funktion . Dieselbe Funktion kann in Ihren eigenen Ersetzungen verwendet werden - beispielsweise, wenn eine gemeinsame Sequenz für mehrere Tabellen gleichzeitig erstellt werden muss. Da die Arbeit mit der Sequenz jedoch nicht transaktional ist, ist die Sequenz von IDs in den gespeicherten Tabellendatensätzen "undicht" , wenn die Kennung von ihr durch eine Rollback-Transaktion empfangen wurde.id integer NOT NULL DEFAULT nextval('tbl_id_seq')



pg_get_serial_sequence(table_name, column_name)DEFAULT



...



GENERIERTE Spalten



Ab PostgreSQL 10 kann eine Identitätsspalte ( GENERATED AS IDENTITY) deklariert werden , die dem SQL: 2003-Standard entspricht. In der Variante ist das GENERATED BY DEFAULTVerhalten gleichwertig serial, aber mit GENERATED ALWAYSallem Interessanteren: 



CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);


INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.


Ja, um einen bestimmten Wert "über" eine solche Spalte einzufügen, müssen Sie zusätzliche Anstrengungen unternehmen mit OVERRIDING SYSTEM VALUE: 



INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .


Beachten Sie, dass wir jetzt zwei identische Werte in der Tabelle haben id = 1- das heißt, GENERATED legt keine zusätzlichen EINZIGARTIGEN Bedingungen und Indizes fest , sondern ist lediglich eine Deklaration serial.



In modernen PostgreSQL-Versionen ist die Verwendung von Seriell im Allgemeinen veraltet, wobei der bevorzugte Ersatz für GENERATED. Außer vielleicht der Situation der Unterstützung für versionierungsübergreifende Anwendungen, die mit PGs unter 10 arbeiten.



Generierte UUID



Alles ist in Ordnung, solange Sie innerhalb einer Datenbankinstanz arbeiten. Wenn es jedoch mehrere davon gibt, gibt es keine angemessene Möglichkeit, die Sequenzen zu synchronisieren (dies hindert Sie jedoch nicht daran, sie „unzureichend“ zu synchronisieren , wenn Sie dies wirklich möchten). Hier helfen Typ UUIDund Funktionen zur Generierung von Werten . Ich benutze es normalerweise uuid_generate_v4()als das "lässigste".



Versteckte Systemfelder



tableoid / ctid



Wenn Sie Datensätze aus einer Tabelle abrufen, müssen Sie manchmal einen bestimmten "physischen" Datensatz adressieren oder herausfinden, aus welchem ​​bestimmten Abschnitt ein bestimmter Datensatz abgerufen wurde, wenn Sie über die Vererbung auf die "übergeordnete" Tabelle zugreifen .



In diesem Fall helfen uns die versteckten Systemfelder in jedem Datensatz:



  • tableoidspeichert die oid-id der Tabelle - das heißt, tableoid::regclass::textgibt den Namen eines bestimmten Tabellenabschnitts an
  • ctid - "physische" Adresse des Datensatzes im Format (<>,<>)


Beispielsweise ctidkann es für Operationen mit einer Tabelle ohne Primärschlüssel verwendet werden , jedoch tableoidfür die Implementierung bestimmter Arten von Fremdschlüsseln.



oid



Bis zu 11 PostgreSQL konnten beim Erstellen der Attributtabelle deklariert werden WITH OIDS: 



CREATE TABLE tbl(id serial) WITH OIDS;


Jeder Eintrag in dieser Tabelle wird ein zusätzliches verstecktes Feld oidmit einem global eindeutigen Wert innerhalb der Datenbank - wie für organisierte Systemtabellen wie pg_class, pg_namespace...



Wenn Sie einen Datensatz in einer Tabelle generierten Wert einfügen wird sofort auf das Ergebnis der Abfrage zurückgegeben: 



INSERT INTO tbl(id) VALUES(DEFAULT);


  :   OID 16400   11 .


Ein solches Feld ist für eine "normale" Tabellenabfrage unsichtbar: 



SELECT * FROM tbl;


id
--
 1


Es muss wie andere Systemfelder explizit angefordert werden: 



SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;


tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1


Der Wert oidbeträgt zwar nur 32 Bit , daher ist es sehr einfach, einen Überlauf zu erhalten. Danach kann oidnicht einmal mehr eine Tabelle erstellt werden (es wird eine neue benötigt !). Daher wird es seit PostgreSQL 12 WITH OIDSnicht mehr unterstützt .



"Faire" Zeit clock_timestamp



Manchmal möchten Sie während einer langen Ausführung einer Abfrage oder Prozedur die "aktuelle" Zeit an den Datensatz binden. Ein Fehler wartet auf jeden, der versucht, die Funktion dazu zu verwenden. now()Er gibt während der gesamten Transaktion denselben Wert zurück .



Um die "jetzt" Zeit zu bekommen, gibt es eine Funktion clock_timestamp()(und eine andere Gruppe ihrer Brüder). Der Unterschied zwischen dem Verhalten dieser Funktionen ist am Beispiel einer einfachen Abfrage zu sehen: 



SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);


              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03


More articles:


All Articles