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:
CREATE SEQUENCE ticket_id INCREMENT 1 START 101;
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:
create table tickets(id integer, title text);
Typowe wykorzystanie sequence wygląda tak:
insert into tickets(id, title) values(nextval('ticket_id'),'issue1'), (nextval('ticket_id'),'issue2');
W efekcie, w tabeli znajdują się teraz następujące rekordy:
select * from tickets; id | title -----+-------- 101 | issue1 102 | issue2
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:
CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), color_name VARCHAR NOT NULL );
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.
insert into color (color_name) values ('pink'), ('green'), ('yellow');
INSERT 0 3
select * from color;
color_id | color_name
----------+------------
10 | pink
20 | green
30 | yellow
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:
select pg_get_serial_sequence('public.color', 'color_id');
W efekcie dostaniemy:
pg_get_serial_sequence --------------------------- public.color_color_id_seq
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:
CREATE TABLE Towns ( id SERIAL UNIQUE NOT NULL, code VARCHAR(10) NOT NULL, -- not unique article TEXT, name TEXT NOT NULL, -- not unique department VARCHAR(4) NOT NULL, UNIQUE (code, department) );
to poniższe zapytanie również wykryje relację między kolumną id, a automatycznie utworzonym sequence:
SELECT d.refobjid::regclass, a.attname FROM pg_sequences s JOIN pg_depend d ON d.objid = CONCAT(s.schemaname,'.', s.sequencename)::regclass JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid;
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:
SELECT seqclass.relname AS sequence_name, seqclass.relfilenode AS sequenceref, dep.refobjid AS depobjref, depclass.relname AS table_name FROM pg_class AS seqclass JOIN pg_sequence AS seq ON seq.seqrelid = seqclass.relfilenode JOIN pg_depend AS dep ON seq.seqrelid = dep.objid JOIN pg_class AS depclass ON dep.refobjid = depclass.relfilenode;
[ Referece: https://sadique.io/blog/2019/05/07/viewing-sequence-ownership-information-in-postgres/ ]
