我的一个表具有以下定义：
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表一样增加“on a secondary column in a multiple-column index”,那么这是很好的,但是在manual中我没有提到这样的功能.
一个明显的解决方案是选择表1中的当前值,但这显然对于并发会话是不安全的.也许预插入触发器可以工作,但是它们是否保证是非并发的？
另请注意,我单独插入事件,所以我不能使用generate_series建议elsewhere.
It would be nice if Postgresql supported incrementing “on a secondary column in a multiple-column index” like MysqL’s MyISAM tables
是的,但请注意,在这样做时,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,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'),'whale'),'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()调用.该交易自动持续到交易结束时发布.
在一个单独的笔记,我会坚持使用一个好的旧序列.这将使事情更快 – 即使在查看数据时它不是很漂亮.
最后,通过添加一个额外的表,其主键是一个序列号,其(年,月)值对其具有唯一约束,也可以获得每(年,月)组合的唯一序列.
