如果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);
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);
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)值设置唯一约束条件,来获得每个(年份,月份)组合的唯一序列。
emp_pk_next()
不是并发安全的。 - Denis de Bernardy