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.

Denis de Bernardy · Accepted Answer · 2011-06-16T09:58:41.277

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.

[jordani's answer](http://stackoverflow.com/questions/6369152/postgresql-auto-increment-based-on-multi-column-unique-constraint/6372052#6372052) uses simpler code. Do you know the reason for the extra complexity in your code? — l0b0, Jun 17 '11 at 06:49
@i0b0: in his example, `emp_pk_next()` is not concurrency safe. — Denis de Bernardy, Jun 17 '11 at 11:19
@Jordani: yes, the advisory lock ensures it'll work in serializable isolation. — Denis de Bernardy, Jun 17 '11 at 13:42
Would a `WHEN` condition make sense like for the `AFTER INSERT` create trigger statement? — Dmitry Minkovsky, Apr 15 '22 at 14:21

score 3 · Answer 2 · answered Apr 01 '15 at 07:50

I think I found better solution. It doesn't depends on grp Type (it can be enum, integer and string) and can be used in a lot of cases.

myFunc() - function for a trigger. You can name it as you want. number - autoincrement column which grows up for each exists value of grp. grp - your column you want to count in number. myTrigger - trigger for your table. myTable - table where you want to make trigger. unique_grp_number_key - unique constraint key. We need make it for unique pair of values: grp and 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();

How does it work? When you insert something in myTable, trigger invokes and checks if number field is empty. If it is empty, myFunc() select MAX value of number where grp equals to new grp value which you want to insert. It returns max value + 1 like auto_increment and replaces null number field to new autoincrement value.

This solution is more unique than Denis de Bernardy cause it doesn't depend on grp Type, but thanks to him, his code helps me write my solution. Maybe it's too late to write answer, but i can't found unique solution for this problem in stackoverflow, so it can help someone. Enjoy and thanks for help!

jordani · Answer 3 · 2011-06-18T17:15:16.823

2

I think this will help: http://www.varlena.com/GeneralBits/130.php

Note that in MySQL it is for MyISAM tables only.

PP I have tested advisory locks and found them useless for more than 1 transaction in same time. I am using 2 windows of pgAdmin. First is as simple as possible:

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"

Second:

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

And database is locked and can not be unlocked - it is unknown what to unlock.

edited Jun 18 '11 at 17:15

answered Jun 16 '11 at 12:46

jordani

436
3
3

+1 nice reference, similar to but simpler than [Denis' answer](http://stackoverflow.com/questions/6369152/postgresql-auto-increment-based-on-multi-column-unique-constraint/6369984#6369984). – l0b0 Jun 17 '11 at 06:47
Actually, there's an issue: "I'd like to avoid creating a ... table for each `(report, year, month)` set." With this approach I'd need a table containing each `(report, year, month)` set, with a counter for the `number` :/ Is there a `SELECT MAX(...)` approach which is known to be concurrency-safe? – l0b0 Jun 17 '11 at 07:14
Unfortunately you have to create table. Or you can lock table "incidents" in MySql way but it will be very slow. I have to test if advisory loks can work, I'm not sure. – jordani Jun 17 '11 at 18:12
I'm specifically looking for a PostgreSQL solution – l0b0 Jun 18 '11 at 13:23

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

3 Answers3

Linked