Krok za krokiem — sekwencje w PostgreSQL

Sekwencja – uporządkowany ciąg znaków, następujący po sobie w ściśle określonej kolejności. W PostgreSQL (a szerzej w kontekście baz danych), specjalny obiekt bazodanowy – tak pożyteczny, a zarazem już tak w zasadzie oczywisty, że nie spodziewamy się zmian w ich funkcjonowaniu. Tymczasem zmiany takie zostały wprowadzone w PostgreSQL 10.

Zazwyczaj sekwencji używa się do automatycznego numerowania rekordów w tablicy, najczęściej w ten sposób tworzy się klucz główny. W wersjach PostgreSQL < 10 najłatwiej można to było zrobić poprzez:

postgres@sequence_in_pg10=# CREATE TABLE old_way(id serial primary key, dummy text);
CREATE TABLE

W wersji 10 mamy możliwość użycia innego rozwiązania:

postgres@sequence_in_pg10=# CREATE TABLE new_way_default(id int GENERATED BY
DEFAULT AS IDENTITY PRIMARY KEY, dummy text);
CREATE TABLE

Pozornie obie tabele wyglądają niemal identycznie:

Tabela "public.old_way"
 Kolumna |   Typ   | Porównanie | Nullowalne |              Domyślnie              
---------+---------+------------+------------+-------------------------------------
 id      | integer |            | not null   | nextval('old_way_id_seq'::regclass)
 dummy   | text    |            |            | 
Indeksy:
    "old_way_pkey" PRIMARY KEY, btree (id)

postgres@sequence_in_pg10=# \d new_way_default
                        Tabela "public.new_way_default"
 Kolumna |   Typ   | Porównanie | Nullowalne |            Domyślnie             
---------+---------+------------+------------+----------------------------------
 id      | integer |            | not null   | generated by default as identity
 dummy   | text    |            |            | 
Indeksy:
    "new_way_default_pkey" PRIMARY KEY, btree (id)

Większość czytelników zapewne zdaje sobie sprawę, że użycie pseudotypu serial powoduje niejawne utworzenie sekwencji, te jednak widzimy dla w obu tabel:

postgres@sequence_in_pg10=# \ds
                       Lista relacji
 Schemat |         Nazwa          |    Typ    | Właściciel 
---------+------------------------+-----------+------------
 public  | new_way_default_id_seq | sekwencja | postgres
 public  | old_way_id_seq         | sekwencja | postgres
(2 wiersze)

Tabele zachowują się również identycznie, na przykład:

postgres@sequence_in_pg10=# INSERT INTO old_way (dummy) VALUES ('a'), ('b') RETURNING *;
 id | dummy 
----+-------
  1 | a
  2 | b
INSERT 0 2

postgres@sequence_in_pg10=# INSERT INTO new_way_default (dummy) VALUES ('a'), ('b') RETURNING *;
 id | dummy 
----+-------
  1 | a
  2 | b

INSERT 0 2
postgres@sequence_in_pg10=# UPDATE old_way SET id = 3 WHERE id = 1 RETURNING *;
 id | dummy 
----+-------
  3 | a

UPDATE 1
postgres@sequence_in_pg10=# UPDATE new_way_default SET id = 3 WHERE id = 1 RETURNING *;
 id | dummy 
----+-------
  3 | a

UPDATE 1
postgres@sequence_in_pg10=# INSERT INTO old_way (dummy) VALUES ('d'), ('e') RETURNING *;
ERROR:  duplicate key value violates unique constraint "old_way_pkey"
SZCZEGÓŁY:  Key (id)=(3) already exists.
postgres@sequence_in_pg10=# INSERT INTO new_way_default (dummy) VALUES ('d'), ('e') RETURNING *;
ERROR:  duplicate key value violates unique constraint "new_way_default_pkey"
SZCZEGÓŁY:  Key (id)=(3) already exists.

O co zatem tyle hałasu?

Choć w istocie tabele wyglądają i zachowują się bardzo podobnie, różni je rola, jaką przy nich odgrywa sekwencja. Jak zostało wspomniane, w przypadku tabeli old_way niejawnie wykonało się kilka operacji, m.in.:

ALTER SEQUENCE old_way_id_seq OWNED BY old_way.id;

W ten sposób powiązano sekwencję i kolumnę, do której została przypisana, jednak oba obiekty egzystują nieco osobno. W wypadku tabeli new_way_default powiązanie to jest znacznie mocniejsze, sekwencja jest całkowicie zależna od tabeli.

Uprawnienia i zarządzanie

W wypadku sekwencji utworzonej w wyniku polecenia GENERATED [...] AS IDENTITY „punktem zarządzania” jest kolumna lub tabela. W kwestii uprawnień wystarczy zatem nadać je na tabelę i są one propagowane dalej:

postgres@sequence_in_pg10=# CREATE USER tester;
CREATE ROLE
postgres@sequence_in_pg10=# GRANT INSERT ON old_way TO tester;
GRANT
postgres@sequence_in_pg10=# GRANT INSERT ON new_way_default TO tester;
GRANT
postgres@sequence_in_pg10=# SET SESSION AUTHORIZATION tester;
SET
tester@sequence_in_pg10=> INSERT INTO old_way (dummy) VALUES ('d');
ERROR:  permission denied for sequence old_way_id_seq
tester@sequence_in_pg10=> INSERT INTO new_way_default (dummy) VALUES ('d');
INSERT 0 1

Również modyfikacja sekwencji może za pomocą poleceń odnoszących się kolumny (tu korzyścią jest to, że nie musimy pamiętać nazwy samej sekwencji):

postgres@sequence_in_pg10=# ALTER TABLE new_way_default ALTER COLUMN id RESTART WITH 4;
ALTER TABLE

W przypadku sekwencji utworzonej za pomocą pseudotypu serial musimy odwołać się do niej bezpośrednio:

postgres@sequence_in_pg10=# ALTER SEQUENCE old_way_id_seq RESTART WITH 4;
ALTER SEQUENCE

Sekwencji new_way_default_id_seq nie możemy usunąć:

postgres@sequence_in_pg10=# DROP SEQUENCE new_way_default_id_seq CASCADE;
ERROR:  cannot drop sequence new_way_default_id_seq because table new_way_default column id requires it
PODPOWIEDŹ:  You can drop table new_way_default column id instead.

Jednak zniknie ona wraz ze zniesieniem zależności:

postgres@sequence_in_pg10=# ALTER TABLE new_way_default ALTER COLUMN id DROP IDENTITY;
ALTER TABLE
postgres@sequence_in_pg10=# DROP SEQUENCE new_way_default_id_seq;
ERROR:  sequence "new_way_default_id_seq" does not exist

W wypadku drugiej sekwencji można usunąć ją (a zarazem powiązanie z tabelą) z użyciem klauzuli CASCADE:

postgres@sequence_in_pg10=# DROP SEQUENCE old_way_id_seq ;
ERROR:  cannot drop sequence old_way_id_seq because other objects depend on it
SZCZEGÓŁY:  default for table old_way column id depends on sequence old_way_id_seq
PODPOWIEDŹ:  Use DROP ... CASCADE to drop the dependent objects too.
postgres@sequence_in_pg10=# DROP SEQUENCE old_way_id_seq CASCADE;
NOTICE:  drop cascades to default for table old_way column id
DROP SEQUENCE

GENARATED ALWAYS AS IDENTITY

Podczas tworzenia tabeli new_way_default, kolumnę id utworzyliśmy z opcją GENERATED DEFAULT AS IDENTITY, stąd właśnie możliwość modyfikowania jej wartości za pomocą zapytań INSERT czy UPDATE. Jednak jeśli użyjemy konstrukcji ALWAYS, sytuacja ulegnie zmianie:

postgres@sequence_in_pg10=# CREATE TABLE new_way_always(id int GENERATED ALWAYS  AS IDENTITY PRIMARY KEY, dummy text);
CREATE TABLE
postgres@sequence_in_pg10=# INSERT INTO new_way_always (dummy) VALUES ('a'), ('b') RETURNING *;
 id | dummy 
----+-------
  1 | a
  2 | b
(2 wiersze)

INSERT 0 2

postgres@sequence_in_pg10=# INSERT INTO new_way_always VALUES (5, 'a') RETURNING *;
ERROR:  cannot insert into column "id"
SZCZEGÓŁY:  Column "id" is an identity column defined as GENERATED ALWAYS.
PODPOWIEDŹ:  Use OVERRIDING SYSTEM VALUE to override.
postgres@sequence_in_pg10=# UPDATE new_way_always SET id = 3 WHERE id = 1 RETURNING *;
ERROR:  column "id" can only be updated to DEFAULT
SZCZEGÓŁY:  Column "id" is an identity column defined as GENERATED ALWAYS.

Ograniczenie to jest znoszone podczas używania polecenia COPY:

postgres@sequence_in_pg10=# COPY new_way_always FROM STDIN WITH ( DELIMITER ',' );
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 55,s
>> 66,df
>> \.
COPY 2

Składnia

Ostatnim, ale na pewno nie nieistotnym aspektem są zmiany syntaktyczne, bo powodują one kompatybilność ze standardem SQL:2003. I choć trudno sobie wyobrazić potrzebę migracji z PostgreSQL do… w sumie jakiegokolwiek innego silnika, niewątpliwie taka standaryzacja ułatwia życie młodym adeptom administracji bazami danych ;-)

Źródła:
https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL
https://blog.2ndquadrant.com/postgresql-10-identity-columns/
https://www.depesz.com/2017/04/10/waiting-for-postgresql-10-identity-columns/