Sekvenser i PostgreSQL

MySQL-diskussionen i nyhetsforumet började bli lite för OT, så jag fortsätter sidospåret om sekvenser och ID-nummer i PostgreSQL här i stället.

Den kommer att sätta in 101 som nästa autoid, förutsatt att man inte ändrat sekvensens ökningsvärde till något annat än 1 (som är default). Sekvensen är inte associerad till någon specifik tabell/kolumn, men det går naturligtvis att göra det hela lite smartare:

SELECT setval('my_sequence', max(my_table.id));

Ovanstående sätter värdet på sekvensen till det högsta värdet i kolumnen "id" i tabellen "my_table".

Sekvenser är för övrigt otroligt kraftfulla att använda, just eftersom de fungerar oberoende av tabellerna. Du kan t.ex. använda samma sekvens för att dela ut id-värden i flera olika tabeller, eller i ett enda svep skapa en post i databasen tillsammans med en massa refererande poster, helt utan hjälp av middlewaren. Ett exempel på det sistnämnda följer här:

Låt oss säga att vi har en videobutik på nätet. I butikens databas finns en tabell där varje film listas, och en tabell för de dvd-skivor/kassetter som finns inne. Tabelldefinitionen ser ut så här:

CREATE SEQUENCE filmer_seq;

CREATE TABLE filmer (
    film_id INTEGER NOT NULL DEFAULT nextval('filmer_seq'),
    namn TEXT NOT NULL,
    PRIMARY KEY(film_id)
);

CREATE TABLE kassetter (
    film_id INTEGER REFERENCES filmer (film_id) ON DELETE CASCADE,
    typ TEXT NOT NULL
);

Det går också att definiera film_id som SERIAL, vilket motsvarar AUTO_INCREMENT i mysql. Skillnaden är då att id-sekvensen skapas automatiskt och får namnet "filmer_film_id_seq". Då behöver man alltså inte köra "CREATE SEQUENCE"-satsen.

Nu vill vi lägga till en film, samt lägga till två dvd-skivor och en vhs-kassett till saldot. Utan sekvenser hade vi varit tvungna att först skapa filmen, sedan köra en SELECT-sats från middlewaren för att ta reda på vilket ID den fick och först efter det skapa posterna. Nu kan vi göra alltihop i ett enda svep:

BEGIN TRANSACTION;
INSERT INTO filmer (namn) VALUES ('Mupparna i rymden');
INSERT INTO kassetter (film_id, typ) VALUES (currval('filmer_seq'), 'dvd');
INSERT INTO kassetter (film_id, typ) VALUES (currval('filmer_seq'), 'dvd');
INSERT INTO kassetter (film_id, typ) VALUES (currval('filmer_seq'), 'vhs');
COMMIT WORK;

Anledningen till att vi kör det hela i en transaktion är att vi då är 100% säkra på att ingen samtidig användare petar fram sekvensen under körning - sekvenshanteringen är helt transaktionssäker! Samtidigt behöver vi inte låsa några tabeller, utan alla andra kan använda databasen precis som vanligt. Inte illa va?

Edit: Det går också att ändra tabelldefinitionen för "kassetter" och definiera kolumnen "film_id" så att den har DEFAULT-värdet "currval('filmer_seq')". Då blir SQL-satsen lite enklare:

BEGIN TRANSACTION;
INSERT INTO filmer (namn) VALUES ('Mupparna i rymden');
INSERT INTO kassetter (typ) VALUES ('dvd');
INSERT INTO kassetter (typ) VALUES ('dvd');
INSERT INTO kassetter (typ) VALUES ('vhs');
COMMIT WORK;