PostgreSQL: автоинкремент на основе многоколоночного уникального ограничения

Ответ 1

Было бы неплохо, если PostgreSQL поддерживал увеличение "на вторичном столбце в индексе с несколькими столбцами", например, таблицы MySQL MyISAM

Да, но обратите внимание, что при этом MyISAM блокирует всю вашу таблицу. Что затем позволяет безопасно находить самый большой +1, не беспокоясь о параллельных транзакциях.

В Postgres вы можете сделать это и без блокировки всей таблицы. Контрольная блокировка и триггер будут достаточно хороши:

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;

Это дает:

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

Существует одна оговорка. Контрольные блокировки сохраняются до отпускания или до истечения срока действия сеанса. Если во время транзакции произошла ошибка, блокировка сохраняется, и вам нужно ее вручную отпустить.

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

В Postgres 9.1 вы можете отменить триггер разблокировки и заменить pg_advisory_lock() на pg_advisory_xact_lock(). Он автоматически удерживается до и заканчивается в конце транзакции.

В отдельном примечании я придерживаюсь старой старой последовательности. Это ускорит работу, даже если это не так красиво, когда вы смотрите на данные.

Наконец, уникальная последовательность в год (year, month) combo также может быть получена путем добавления дополнительной таблицы, чей первичный ключ является серийным и чье (год, месяц) значение имеет уникальное ограничение на него.

Ответ 2

Я думаю, что это поможет: http://www.varlena.com/GeneralBits/130.php

Обратите внимание, что в MySQL это только для таблиц MyISAM.

PP Я проверил консультативные блокировки и нашел их бесполезными для более чем одной транзакции за это же время. Я использую 2 окна pgAdmin. Сначала максимально просто:

BEGIN;
INSERT INTO animals (grp,name) VALUES ('mammal','dog');
COMMIT;

BEGIN;
INSERT INTO animals (grp,name) VALUES ('mammal','cat');
COMMIT;

ERROR: duplicate key violates unique constraint "animals_pkey"

Во-вторых:

BEGIN;
INSERT INTO animals (grp,name) VALUES ('mammal','dog');
INSERT INTO animals (grp,name) VALUES ('mammal','cat');
COMMIT;

ERROR: deadlock detected
SQL state: 40P01
Detail: Process 3764 waits for ExclusiveLock on advisory lock [46462,46496,2,2]; blocked by process 2712.
Process 2712 waits for ShareLock on transaction 136759; blocked by process 3764.
Context: SQL statement "SELECT  pg_advisory_lock( $1 ,  $2 )"
PL/pgSQL function "animals_id_auto" line 15 at perform

И база данных заблокирована и не может быть разблокирована - неизвестно, что разблокировать.

Ответ 3

Думаю, я нашел лучшее решение. Это не зависит от типа grp (это может быть перечисление, целое число и строка) и может использоваться во многих случаях.

myFunc() - функция для триггера. Вы можете назвать его так, как хотите. number - автоинкрементный столбец, который растет для каждого существующего значения grp. grp - номер столбца, который вы хотите подсчитать. myTrigger - триггер для вашей таблицы. myTable - таблица, в которой вы хотите создать триггер. unique_grp_number_key - уникальный ключ ограничения. Нам нужно сделать это для уникальной пары значений: grp и number.

ALTER TABLE "myTable"
    ADD CONSTRAINT "unique_grp_number_key" UNIQUE(grp, number);

CREATE OR REPLACE FUNCTION myFunc() RETURNS trigger AS $body_start$
BEGIN
    SELECT COALESCE(MAX(number) + 1, 1)
        INTO NEW.number
        FROM "myTable"
        WHERE grp = NEW.grp;
    RETURN NEW;
END;
$body_start$ LANGUAGE plpgsql;

CREATE TRIGGER myTrigger BEFORE INSERT ON "myTable"
    FOR EACH ROW
    WHEN (NEW.number IS NULL) 
    EXECUTE PROCEDURE myFunc();

Как это работает? Когда вы вставляете что-то в myTable, запускает вызов и проверяет, пусто ли поле номера. Если он пуст, myFunc() выбирает значение MAX числа, где grp равно новому значению grp, которое вы хотите вставить. Он возвращает максимальное значение + 1, подобное auto_increment, и заменяет поле нулевого номера на новое значение автоинкремента.

Это решение более уникально, чем Denis de Bernardy, потому что он не зависит от типа grp, но благодаря ему его код помогает мне написать мое решение. Может быть, слишком поздно писать ответ, но я не могу найти уникальное решение этой проблемы в stackoverflow, так что это может помочь кому-то. Наслаждайтесь и благодарите за помощь!