En bref : j’ai fait un kill -9 sur les processus postgres : une colonne identity PostgreSQL est passée de l’id 9 à 37, parce que les séquences PostgreSQL journalisent 32 valeurs d’avance dans le WAL. Rollbacks et insertions en échec brûlent aussi des valeurs, et une colonne BY DEFAULT ignore les id explicites. Préférer GENERATED ... AS IDENTITY à serial, et ne jamais compter sur des id continus.

PostgreSQL 18.6, cluster jetable sous Rocky Linux 10.2. Partie 1 sur 5 (PostgreSQL, MariaDB, Oracle, SQL Server, résumé). Toutes les sorties viennent de mes propres tests.

Séquences PostgreSQL après kill -9 : id 1 à 9, puis un trou de 27 valeurs, puis 37
Un kill -9 sur PostgreSQL 18.6 : 27 valeurs réservées et jamais distribuées.

Trois façons d’obtenir une valeur générée

CREATE TABLE t_ident  (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v text);
CREATE TABLE t_serial (id serial PRIMARY KEY, v text);
CREATE SEQUENCE s1;
CREATE TABLE t_seq    (id int PRIMARY KEY DEFAULT nextval('s1'), v text);

Chaque INSERT ... RETURNING id renvoie 1 sur une table neuve. Le catalogue montre la différence :

 column_name |            column_default            | is_identity | identity_generation
-------------+--------------------------------------+-------------+---------------------
 id          |                                      | YES         | ALWAYS
 id          | nextval('s1'::regclass)              | NO          |
 id          | nextval('t_serial_id_seq'::regclass) | NO          |

Une colonne identity n’a pas d’expression par défaut ; la séquence est attachée à la colonne (pg_get_serial_sequence('t_ident','id') renvoie public.t_ident_id_seq). serial est un raccourci : colonne entière plus défaut appelant nextval.

ALWAYS ou BY DEFAULT

Avec GENERATED BY DEFAULT, une valeur explicite est acceptée mais ne fait pas avancer le compteur :

INSERT INTO t_by(id, v) VALUES (2, 'explicit 2');         -- ok
INSERT INTO t_by(v) VALUES ('default 1') RETURNING id;    -- 1
INSERT INTO t_by(v) VALUES ('default 2') RETURNING id;    -- ERROR: duplicate key ... (id)=(2) already exists
INSERT INTO t_by(v) VALUES ('default 3') RETURNING id;    -- 3

L’insertion en échec a brûlé la valeur 2 du compteur. Avec GENERATED ALWAYS, une valeur explicite est refusée sauf demande expresse :

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Après OVERRIDING SYSTEM VALUE VALUES (50, ...), l’insertion par défaut suivante a renvoyé 1 : le compteur est indépendant des données.

Rollback et échecs consomment des valeurs

BEGIN; INSERT INTO t_ident(v) VALUES ('rolled back') RETURNING id;   -- 2
ROLLBACK;
INSERT INTO t_ident(v) VALUES ('after rollback') RETURNING id;       -- 3

Idem avec une violation d’unicité : id 1, erreur sur le doublon, puis 3. Un compteur sans trou demande une autre conception (table de compteur verrouillée), au prix de la sérialisation des écritures.

Concurrence : l’ordre des id n’est pas l’ordre des commits

La session A insère en premier et valide 3 secondes après B ; la session B insère en second et valide aussitôt :

A got id 1
B got id 2
B: committed 08:18:48.652
A: committed 08:18:51.154

La ligne de B est visible avant celle de A, bien que son id soit plus grand. Ne pas utiliser « max(id) déjà vu » comme curseur de réplication ou de polling.

CACHE est par session

CREATE SEQUENCE s_cache CACHE 10;
same session twice : 1|2
new session        : 11
another new session: 21

Avec les séquences PostgreSQL, chaque session réserve un bloc de 10. Les valeurs de sessions différentes s’entrelacent et des trous apparaissent dès qu’une session se termine.

Limites et CYCLE

ERROR:  nextval: reached maximum value of sequence "s_small" (3)

Après ALTER SEQUENCE s_small CYCLE, la valeur suivante est 1. Une identity sur smallint s’arrête à 32767 avec la même erreur sur t_small_id_seq. Les identity integer s’arrêtent à 2147483647 : prendre bigint pour les tables à longue vie.

Redémarrage et crash

Trois lignes insérées, la dernière supprimée, redémarrage propre :

sequence before the restart: 3|30|t     (last_value | log_cnt | is_called)
sequence after the restart:  3|30|t
new id = 4

Puis cinq lignes de plus (dernier id 9), kill -9 de tous les processus postgres, redémarrage :

sequence before the crash: 9|27|t
sequence after the crash:  36|0|t
new id = 37
ids now: 1,2,4,5,6,7,8,9,37

log_cnt est le nombre de valeurs encore couvertes par le dernier enregistrement WAL. PostgreSQL écrit un enregistrement WAL pour 32 valeurs ; après la récupération sur crash, il repart de la fin de la plage réservée (9 + 27 = 36), donc l’id suivant est 37. Un arrêt propre écrit l’état exact, sans trou.

Où lire l’état des séquences PostgreSQL

SELECT * FROM pg_sequences;                -- départ, incrément, max, cache_size, cycle, last_value
SELECT last_value, log_cnt, is_called FROM t_ident_id_seq;
SELECT attrelid::regclass, attname, attidentity FROM pg_attribute WHERE attidentity <> '';  -- 'a' ALWAYS, 'd' BY DEFAULT

Documentation officielle

PostgreSQL, site du projet

CREATE SEQUENCE, documentation PostgreSQL 18

Sequence manipulation functions, nextval, currval, setval

CREATE TABLE, GENERATED ... AS IDENTITY et serial

pg_sequences, la vue système utilisée plus haut

*Non testé ici : réplication logique des séquences, pg_upgrade, tables partitionnées, ALTER SEQUENCE ... RESTART en cours d’usage.*

Toutes les parties de la série

1/5 PostgreSQL

2/5 MariaDB (à paraître le 17/11)

3/5 Oracle (à paraître le 24/11)

4/5 SQL Server (à paraître le 1/12)

5/5 TL;DR : les quatre moteurs comparés (à paraître le 8/12)

Articles liés

Dans les fichiers d’une base de données (1/3) : InnoDB, de MySQL à MariaDB

Dans les fichiers d’une base de données (2/3) : PostgreSQL

Dans les fichiers d’une base de données (3/3) : Oracle, et les trois côte à côte