PostgreSQL: Auto-increment based on multi-column unique constraint

Question

One of my tables has the following definition:

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)
);

How would you go about incrementing the number column for every report, year, and month independently? I'd like to avoid creating a sequence or table for each (report, year, month) set.

It would be nice if PostgreSQL supported incrementing "on a secondary column in a multiple-column index" like MySQL's MyISAM tables, but I couldn't find a mention of such a feature in the manual.

An obvious solution is to select the current value in the table + 1, but this obviously is not safe for concurrent sessions. Maybe a pre-insert trigger would work, but are they guaranteed to be non-concurrent?

Also note that I'm inserting incidents individually, so I can't use generate_series as suggested elsewhere.

Answer 1

It would be nice if PostgreSQL supported incrementing "on a secondary column in a multiple-column index" like MySQL's MyISAM tables

Yeah, but note that in doing so, MyISAM locks your entire table. Which then makes it safe to find the biggest +1 without worrying about concurrent transactions.

In Postgres, you can do this too, and without locking the whole table. An advisory lock and a trigger will be good enough:

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;

This yields:

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

There is one caveat. Advisory locks are held until released or until the session expires. If an error occurs during the transaction, the lock is kept around and you need to release it manually.

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

In Postgres 9.1, you can discard the unlock trigger, and replace the pg_advisory_lock() call with pg_advisory_xact_lock(). That one is automatically held until and released at the end of the transaction.

---

On a separate note, I'd stick to using a good old sequence. That will make things faster -- even if it's not as pretty-looking when you look at the data.

Lastly, a unique sequence per (year, month) combo could also be obtained by adding an extra table, whose primary key is a serial, and whose (year, month) value has a unique constraint on it.