Введение

Я считаю, что наиболее важным моментом здесь является:

• не должно быть пробелов между номерами счетов

В этом случае вы не можете использовать поле squance и auto-increment (как другие предложения в комментариях). Последовательность использования поля Auto-increment под капотом и функция nextval(regclass) увеличивает счетчик последовательностей независимо от того, была ли транзакция успешной или неудачной (вы указываете это самостоятельно).

---

Обновить:

Я имею в виду, что вы не должны использовать последовательности вообще, особенно предлагаемое вами решение не исключает возможности разрыва. Ваш триггер получает новое значение последовательности, но INSERT все еще не удалось.

---

Последовательности работает таким образом, потому что они в основном предназначены для использования в PRIMARY KEYs и OIDs ценности поколения, где уникальность и неблокирующая механизм является конечной целью и пробелы между значениями не очень большая сделка.

Однако в вашем случае приоритеты могут быть разными, но есть несколько вещей, которые следует учитывать.

Простое решение

Первым возможным решением вашей проблемы может быть возвращение нового числа в качестве максимального значения существующих. Это можно сделать в триггере:

NEW.invoice_number =
        (SELECT foo.invoice_number
         FROM invoices foo
         WHERE foo._year = NEW._year
         ORDER BY foo.invoice_number DESC NULLS LAST LIMIT 1
        ); /*query 1*/

Этот запрос может использовать ваш составной UNIQUE INDEX если он был создан с "правильным" синтаксисом и порядком столбцов, который будет столбцом "год", в первую очередь, например:

CREATE UNIQUE INDEX invoice_number_unique
ON invoices (_year, invoice_number DESC NULLS LAST);

В PostgreSQL UNIQUE CONSTRAINTs реализуются просто как UNIQUE INDEXes поэтому в большинстве случаев нет никакой разницы, какую команду вы будете использовать. Однако, используя этот конкретный синтаксис, представленный выше, можно определить порядок по этому индексу. Это действительно хороший трюк, который делает /*query 1*/ быстрее, чем простой SELECT max(invoice_number) FROM invoices WHERE _year = NEW.year если таблица счетов становится больше.

Это простое решение, но имеет один большой недостаток. Существует вероятность состояния гонки, когда две транзакции пытаются вставить счет-фактуру одновременно. Оба могут получить такое же максимальное значение, и UNIQUE CONSTRAINT предотвратит совершение второго. Несмотря на это, этого может быть достаточно в некоторой небольшой системе со специальной политикой вставки.

Лучшее решение

Вы можете создать таблицу

CREATE TABLE invoice_numbers(
   _year INTEGER NOT NULL PRIMARY KEY,
   next_number_within_year INTEGER
);

для хранения следующего возможного номера на определенный год. Затем в триггере AFTER INSERT вы можете:

1. Блокировать invoice_numbers, что никакая другая транзакция не может даже читать номер LOCK TABLE invoice_numbers IN ACCESS EXCLUSIVE;
2. Получить новый номер счета new_invoice_number = (SELECT foo.next_number_within_year FROM invoice_numbers foo where foo._year = NEW.year);
3. Обновить значение числа новых добавленных строк счета
4. Increment UPDATE invoice_numbers SET next_number_within_year = next_number_within_year + 1 WHERE _year = NEW._year;

Поскольку блокировка таблицы удерживается транзакцией до ее фиксации, это, вероятно, должно быть последним триггером (подробнее о порядке выполнения триггера здесь)

---

Обновить:

Вместо блокировки всей таблицы с помощью команды LOCK check link, предоставленной Craig Ringer

---

Недостатком в этом случае является снижение производительности операции INSERT - только одна транзакция в то время может выполнять вставку.