PostgreSQL: Sequences – co to jest i jak ich używać?

16-Mar-2022

Jest wiele sytuacji, w których w bazie danych trzeba zapisać unikalne informacje, oznaczone unikalnym identyfikatorem. Tak jest zresztą nie tylko w bazach danych. Idziesz do urzędu, a na wejściu musisz pobrać numerek, dzięki czemu od razu jesteś zakolejkowany 🙂

Obiektem, który w bazie danych generuje kolejne wartości jest tzw. SEQUENCE. Bardzo podstawowa definicja sequence może wyglądać tak:

Ilekroć będziesz odwoływać się do ticket_id, z wykorzystaniem funkcji nextvalue, będziesz otrzymywać kolejne numerki począwszy od 101. Sqeunce ma mnóstwo parametrów dotyczących tego, jak ma wyglądać generowanie kolejnych wartości i znajdziesz je w helpie.

Załóżmy, że została utworzona taka oto tabela:

Typowe wykorzystanie sequence wygląda tak:

W efekcie, w tabeli znajdują się teraz następujące rekordy:

Pięknie… ale…. dla danego sequence nie wiadomo, gdzie on jest wykorzystywany. Można by było oczywiście dochodzić do tego po nazwie. Skoro ktoś nazwał sequence ticket_id, to może jest jakaś tabela z ticket-ami?

Tabele można też zdefiniować tak:

Zasada pracy tej tabeli jest podobna, jak w przpadku sequence, a gdyby słowo DEFAULT zamienić na ALWAYS, to wartości dla color_id nie tylko, że nie trzeba by podawać – nie można jej podać, bo będzie ona generowana przez PostgreSQL samodzielnie.

Jest jednak pewna różnica. Jeśli zajrzymy do widoku  information_schema.sequences, to znajdziemy tam tylko informacje o pierwszym sequence (tym stworzonym jawnie i osobno). Jeśli jednak zajrzymy do tabeli systemowej  pg_sequences, to będą tam już obie!

No ale skoro utworzenie sequence nastąpiło automatycznie podczas tworzenia tabeli – to nie ma siły. Gdzieś musiał zostać ślad po tym, z jakim sequence jest powiązana kolumna color_id. Rzeczywiście:

W efekcie dostaniemy:

A jak zidentyfikować, te ręcznie tworzone sequence? Niestety – chyba odkryliśmy regułę, jak nie należy korzystać z sequence. Jeśli sequence jest obiektem „na boku” i nie podlega przypisaniu do tabeli, jest to tylko maszyna wydająca numerki, stojąca gdzieś w  portierni naszej bazy danych, to po prostu nie jest przypisana do żadnej tabeli/kolumny w tabeli. Logikę generowania wartości id w tabeli tickets, zna w tym przypadku tylko programista aplikacji.

Co ciekawe, jeśli tabela została utworzona poleceniem:

to poniższe zapytanie również wykryje relację między kolumną id, a automatycznie utworzonym sequence:

A tak BTW, skąd PostgreSQL wie, jak się ze sobą te obiekty wiążą? Jest jeszcze jedna tabela systemowa o nazwie pg_sequence, gdzie dla każdego sequence można odnaleźć jego identyfikator. Dzięki temu, działa następujące zapytanie, które – przyjmijmy – odpowiada na pytanie – gdzie jest używany obiekt sequence:

[ Referece: https://sadique.io/blog/2019/05/07/viewing-sequence-ownership-information-in-postgres/ ]

Dodaj komentarz:

Autor: Rafał Kraik