PostgreSQL：基于多列唯一约束的自增

Question

我的一个表的定义如下：

CREATE TABLE incidents
(
  id serial NOT NULL,
  report integer NOT NULL,
  year integer NOT NULL,
  month integer NOT NULL,
  number integer NOT NULL, -- Report serial number for this period
  ...
  CONSTRAINT PRIMARY KEY (id),
  CONSTRAINT UNIQUE (report, year, month, number)
);

你如何逐个独立地为每个报告，年份和月份增加数字列？我想避免为每个 (报告，年份，月份) 集创建序列或表。

如果PostgreSQL支持类似MySQL的MyISAM表在多列索引中自动递增“次要列”，那就太好了，但是在手册中没有提到这样的功能。

一个显而易见的解决方案是选择表中当前值 + 1 ，但是这显然对于并发会话不安全。也许预插入触发器可以工作，但它们保证是非并发的吗？

还要注意，我是逐个插入事件的，因此无法像其他地方建议的使用generate_series。

- l0b0

Answer 1

如果PostgreSQL支持像MySQL的MyISAM表那样在多列索引中对"第二列进行递增"将是很好的。

是的，但请注意这样做会锁定你的整个表。这使得安全地查找最大值+1而不必担心并发事务成为可能。

在Postgres中，您也可以这样做，而且不需要锁定整个表。建议使用一个advisory lock和一个触发器就足够了：

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_xact_lock()替换pg_advisory_lock()调用。这个锁会自动保持直到事务结束并释放。

---

另外，我建议使用传统的序列(sequence)，这样做会更快一些，虽然数据看起来不太漂亮。

最后，你可以通过添加一个额外的表，其主键为serial类型，并对(year, month)值设置唯一约束条件，来获得每个(年份，月份)组合的唯一序列。

- Denis de Bernardy

---

这个在可序列化隔离中测试过吗？ - jordani

jordani的回答使用了更简单的代码。您知道为什么您的代码要增加额外的复杂性吗？ - l0b0

@i0b0：在他的例子中，emp_pk_next()不是并发安全的。 - Denis de Bernardy

@Jordani：是的，咨询锁确保它在可串行化隔离中工作。 - Denis de Bernardy

在“AFTER INSERT”创建触发器语句中，是否使用“WHEN”条件会有意义？ - Dmitry Minkovsky

Answer 2

我认为我找到了更好的解决方案。它不依赖于grp类型（可以是枚举，整数和字符串），并且可以在许多情况下使用。

myFunc() - 触发器函数。您可以按自己的喜好命名。 number - 自增列，每个grp存在值时都会增加。 grp - 您想在number中计算的列。 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()将选择grp等于要插入的新grp值的数字的最大值。 它返回max value + 1，就像自动递增一样，并将null number字段替换为新的自动递增值。此解决方案比Denis de Bernardy的解决方案更加独特，因为它不依赖于grp类型，但由于他的代码帮助我编写了我的解决方案。也许现在回答有点晚了，但我在stackoverflow中找不到独特的解决方案，所以它可以帮助某些人。享受并感谢您的帮助！

- haveacigar

Answer 3

我认为这会有所帮助：http://www.varlena.com/GeneralBits/130.php 请注意，这仅适用于MySQL的MyISAM表。

PS：我已经测试了咨询锁，并发现它们对于同时进行多个事务是无用的。我正在使用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

数据库被锁定无法解锁 - 不知道该解锁什么。

- jordani

---

+1 不错的参考，类似于但比 Denis' answer 更简单。 - l0b0

实际上，有一个问题：“我想避免为每个(报告、年份、月份)集创建一个表。”采用这种方法，我需要一个包含每个(报告、年份、月份)集的表，其中包含number的计数器 :/ 是否有已知是并发安全的SELECT MAX(...)方法？ - l0b0

很遗憾，您必须创建表。或者您可以按照 MySql 的方式锁定“incidents”表，但这将非常缓慢。我必须测试一下咨询锁是否可行，我不确定。 - jordani

我特别寻找一个 PostgreSQL 的解决方案。 - l0b0