Scripts SQL idempotents

Una operació és idempotent si executar-la una vegada o deu vegades deixa la base de dades exactament en el mateix estat. Un script idempotent es pot rellançar sense por: si ja s'ha aplicat, no falla i no duplica res.

Aquesta pàgina és material de repàs complementari a Repàs de SQL avançat (l'UPSERT que hi veuràs és una de les peces clau). No té sessió pròpia.

Per què importa

En un entorn real els scripts s'executen moltes més vegades de les que voldríem:

Reintents automàtics: un pipeline ETL falla a mig camí (xarxa, timeout, un node que cau) i l'orquestrador (Airflow, cron, CI/CD) el torna a executar.
Desplegaments repetits: el mateix script de migració s'aplica a desenvolupament, preproduction i producció, amb estats de partida diferents.
Recuperació d'errors: després d'un error a la línia 40 d'un script de 100 línies, volem poder tornar-lo a llançar sencer sense netejar a mà el que ja s'havia fet.
Infraestructura com a codi: Docker Compose, Ansible o Terraform tornen a aplicar l'estat desitjat cada vegada que s'executen.

Un script no idempotent produeix errors (relation already exists), o pitjor, dades duplicades en silenci: una càrrega rellançada que duplica un mes de vendes falseja tots els informes posteriors i és molt difícil de detectar.

El pitjor error és el que no falla

Un CREATE TABLE repetit falla de manera visible i és fàcil de corregir. Un INSERT repetit sense clau única s'executa "correctament" i deixa les dades corruptes. Cal dissenyar pensant en el segon cas.

DDL idempotent

Per a l'esquema, la majoria de motors ofereixen clàusules de guarda.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE TABLE IF NOT EXISTS clients (
    id      BIGINT PRIMARY KEY,
    nom     TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_clients_nom ON clients (nom);

ALTER TABLE clients ADD COLUMN IF NOT EXISTS email TEXT;

DROP TABLE IF EXISTS clients_temporal;

CREATE TABLE IF NOT EXISTS clients (
    id      BIGINT PRIMARY KEY,
    nom     VARCHAR(200) NOT NULL
);

-- MariaDB admet IF NOT EXISTS a CREATE INDEX i ADD COLUMN;
-- MySQL no: cal consultar information_schema o fer servir un procediment
CREATE INDEX IF NOT EXISTS idx_clients_nom ON clients (nom);  -- MariaDB

DROP TABLE IF EXISTS clients_temporal;

IF OBJECT_ID('dbo.clients', 'U') IS NULL
    CREATE TABLE dbo.clients (
        id  BIGINT PRIMARY KEY,
        nom NVARCHAR(200) NOT NULL
    );

IF COL_LENGTH('dbo.clients', 'email') IS NULL
    ALTER TABLE dbo.clients ADD email NVARCHAR(200);

DROP TABLE IF EXISTS dbo.clients_temporal;   -- des de SQL Server 2016

-- Oracle 23ai admet IF NOT EXISTS; a versions anteriors cal capturar l'error
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE clients (id NUMBER PRIMARY KEY, nom VARCHAR2(200))';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -955 THEN RAISE; END IF;   -- ORA-00955: el nom ja existeix
END;
/

CREATE OR REPLACE per a objectes sense dades

Vistes, funcions i procediments emmagatzemats es poden redefinir sense perdre res: CREATE OR REPLACE VIEW ... (PostgreSQL, MySQL, Oracle) o CREATE OR ALTER VIEW ... (SQL Server). Per a taules no serveix: substituir-les esborraria les dades.

DML idempotent

Per a les dades, la idempotència no ve d'una clàusula de guarda sinó del disseny: cal una manera determinista d'identificar "aquesta fila ja hi és".

Clau única + `ON CONFLICT DO NOTHING`

-- Sense clau natural, cada execució duplica les files
INSERT INTO vendes (comanda_id, producte_id, quantitat)
VALUES (5001, 101, 3);

-- Amb PRIMARY KEY o UNIQUE (comanda_id, producte_id), es pot rellançar sense risc
INSERT INTO vendes (comanda_id, producte_id, quantitat)
VALUES (5001, 101, 3)
ON CONFLICT (comanda_id, producte_id) DO NOTHING;

`UPSERT` amb valors absoluts

Compte: l'UPSERT de la pàgina de repàs (quantitat = estoc.quantitat + EXCLUDED.quantitat) és segur davant de concurrència però no idempotent: rellançar-lo suma dues vegades. Perquè ho sigui, cal escriure el valor final i no un increment:

INSERT INTO estoc (producte_id, quantitat, ultima_actualitzacio)
VALUES (101, 150, NOW())
ON CONFLICT (producte_id)
DO UPDATE SET
    quantitat = EXCLUDED.quantitat,          -- valor absolut: idempotent
    ultima_actualitzacio = EXCLUDED.ultima_actualitzacio;

Regla pràctica

SET x = 10 és idempotent. SET x = x + 10 no ho és. Sempre que puguis, calcula el valor final a l'origen i escriu-lo tal qual.

Càrregues per lots: esborrar i tornar a inserir dins d'una transacció

Quan una càrrega diària substitueix les dades d'un període, el patró més robust és eliminar el rang i reinserir-lo atòmicament:

BEGIN;

DELETE FROM vendes_diaries WHERE data = DATE '2026-10-08';

INSERT INTO vendes_diaries (data, botiga_id, import_total)
SELECT data, botiga_id, SUM(import)
FROM   vendes_brutes
WHERE  data = DATE '2026-10-08'
GROUP  BY data, botiga_id;

COMMIT;

Si el procés falla, la transacció es desfà; si es rellança, deixa el mateix resultat. Sobre taules particionades, aquest patró és encara més eficient: es pot fer TRUNCATE o DETACH/ATTACH de la partició del dia en lloc d'un DELETE massiu.

Patrons i antipatrons

Antipatró (no idempotent)	Alternativa idempotent
`INSERT` sense clau única	`INSERT ... ON CONFLICT DO NOTHING` / `MERGE`
`UPDATE t SET x = x + 1`	`UPDATE t SET x = <valor calculat>`
`CREATE TABLE t (...)`	`CREATE TABLE IF NOT EXISTS t (...)`
`DROP TABLE t`	`DROP TABLE IF EXISTS t`
Identificadors amb `NOW()` o `gen_random_uuid()` a la clau	Clau derivada de les dades d'origen (clau natural o hash determinista)
`DELETE` + `INSERT` sense transacció	Tot dins d'un `BEGIN ... COMMIT`
Migració sense control de versions	Taula de control (`schema_migrations`) que registra què s'ha aplicat

Control de migracions

Eines com Flyway, Liquibase o Alembic fan idempotent el desplegament de l'esquema: guarden en una taula quines migracions ja s'han executat i només apliquen les pendents. Un patró manual equivalent:

CREATE TABLE IF NOT EXISTS schema_migrations (
    versio      TEXT PRIMARY KEY,
    aplicada_a  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM schema_migrations WHERE versio = '2026_10_001') THEN
        ALTER TABLE vendes ADD COLUMN descompte NUMERIC(5,2) DEFAULT 0;
        INSERT INTO schema_migrations (versio) VALUES ('2026_10_001');
    END IF;
END $$;

Com comprovar la idempotència

La prova és senzilla i s'hauria d'automatitzar: executa l'script dues vegades seguides sobre una base de dades de prova i compara l'estat.

psql -d prova -f carrega.sql
psql -d prova -c "SELECT COUNT(*), SUM(import_total) FROM vendes_diaries" > primera.txt
psql -d prova -f carrega.sql
psql -d prova -c "SELECT COUNT(*), SUM(import_total) FROM vendes_diaries" > segona.txt
diff primera.txt segona.txt && echo "Idempotent"

Si el recompte o els totals canvien, l'script no és idempotent.

Exercici pràctic

Agafa el fitxer .sql amb les cinc consultes de l'exercici del repàs de SQL i:

Converteix l'esquema (CREATE TABLE, índexs) en DDL idempotent.
Reescriu l'UPSERT d'estoc perquè sigui idempotent (valor absolut).
Escriu una càrrega diària amb el patró DELETE + INSERT dins d'una transacció.
Executa l'script complet dues vegades i verifica que l'estat final és idèntic.

Bloc 2 | Mòdul M5074 Sistemes de Big Data | Institut Sa Palomera (Blanes) | Curs CEIABD 2026-2027