PostgreSQL: auto-incrément basé sur une contrainte unique à plusieurs colonnes

Ce serait bien si PostgreSQL est pris en charge incrémentation "sur une seconde colonne dans un index multi-colonnes" comme MySQL tables MyISAM

Oui, mais notez qu'en faisant cela, MyISAM verrouille votre table entière. Ce qui permet de trouver le plus gros +1 en toute sécurité sans se soucier des transactions simultanées.

à Postgres, vous pouvez le faire aussi, et sans verrouiller toute la table. Un verrou d'avertissement et un déclencheur seront bons. assez:

CREATE TYPE animal_grp AS ENUM ('fish','mammal','bird');

CREATE TABLE animals (
    grp animal_grp NOT NULL,
    id INT NOT NULL DEFAULT 0,
    name varchar NOT NULL,
    PRIMARY KEY (grp,id)
);

CREATE OR REPLACE FUNCTION animals_id_auto()
    RETURNS trigger AS $$
DECLARE
    _rel_id constant int := 'animals'::regclass::int;
    _grp_id int;
BEGIN
    _grp_id = array_length(enum_range(NULL, NEW.grp), 1);

    -- Obtain an advisory lock on this table/group.
    PERFORM pg_advisory_lock(_rel_id, _grp_id);

    SELECT  COALESCE(MAX(id) + 1, 1)
    INTO    NEW.id
    FROM    animals
    WHERE   grp = NEW.grp;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE TRIGGER animals_id_auto
    BEFORE INSERT ON animals
    FOR EACH ROW WHEN (NEW.id = 0)
    EXECUTE PROCEDURE animals_id_auto();

CREATE OR REPLACE FUNCTION animals_id_auto_unlock()
    RETURNS trigger AS $$
DECLARE
    _rel_id constant int := 'animals'::regclass::int;
    _grp_id int;
BEGIN
    _grp_id = array_length(enum_range(NULL, NEW.grp), 1);

    -- Release the lock.
    PERFORM pg_advisory_unlock(_rel_id, _grp_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE TRIGGER animals_id_auto_unlock
    AFTER INSERT ON animals
    FOR EACH ROW
    EXECUTE PROCEDURE animals_id_auto_unlock();

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Cela donne:

  grp   | id |  name   
--------+----+---------
 fish   |  1 | lax
 mammal |  1 | dog
 mammal |  2 | cat
 mammal |  3 | whale
 bird   |  1 | penguin
 bird   |  2 | ostrich
(6 rows)

il y a une mise en garde. Les serrures consultatives sont maintenues jusqu'à libération ou jusqu'à l'expiration de la session. Si une erreur se produit au cours de la transaction, la serrure est conservée et vous devez la Débloquer manuellement.

SELECT pg_advisory_unlock('animals'::regclass::int, i)
FROM generate_series(1, array_length(enum_range(NULL::animal_grp),1)) i;

dans Postgres 9.1, vous pouvez rejeter le déclencheur de déverrouillage, et remplacer l'appel pg_advisory_lock() par pg_advisory_xact_lock(). Que l'on est automatiquement maintenu jusqu'à ce que et libéré à la fin de la transaction.

sur une note séparée, Je m'en tiendrais à utiliser une bonne vieille séquence. Cela accélérera les choses, même si ce n'est pas aussi joli quand on regarde les données.

enfin, une séquence unique par (Année, Mois) combo pourrait aussi être obtenue en ajoutant une table supplémentaire, dont la clé primaire est une série, et dont la valeur (Année, Mois) a une contrainte unique sur elle.