Как создать несколько последовательностей в одной таблице?

У меня есть таблица "квитанции". У меня есть столбцы customer_id (у которого была квитанция) и receive_number. Квитанция чека должна начинаться с 1 для каждого клиента и быть последовательностью. Это означает, что customer_id и receive_number будут уникальными. Как я могу элегантно сделать это. Могу ли я использовать встроенную функцию защиты от прикосновения с CREATE SEQUENCE или аналогичной? Кажется, мне нужно было бы создать последовательность для каждого клиента, что, конечно, не является элегантным решением.

РЕДАКТИРОВАТЬ: должен быть потокобезопасный и идиот-безопасный способ сделать это. Это должно быть довольно простой / общей потребностью.

Источник

4 ответа

Решение

ПОСЛЕДОВАТЕЛЬНОСТЬ не гарантирует отсутствие пробелов. Например, одна транзакция может сгенерировать новый номер и затем прерваться (из-за ошибки, сбоя питания или чего-то еще...). Следующая транзакция будет слепо получать следующий номер, а не тот, который был "потерян".

Было бы лучше, если бы ваше клиентское приложение не зависело от предположения об отсутствии пробелов. Вы можете, однако, минимизировать разрывы, как это:

  1. SELECT MAX(receipt_number) FROM receipts WHERE customer_id = :ci
  2. INSERT INTO receipts(customer_id, receipt_number) VALUES (:ci, aboveresult+1)или просто вставьте 1, если шаг 1 вернул NULL.
  3. Если шаг 2 вернул нарушение PK*, повторите попытку с самого начала.

* Поскольку параллельная транзакция прошла через тот же процесс и зафиксирована.

Пока строки просто добавляются, а не удаляются, это должно предотвратить любые пробелы, даже в параллельной среде.

Кстати, вы можете "сжать" шаги 1 и 2 следующим образом:

INSERT INTO receipts (customer_id, receipt_number)
SELECT :ci, COALESCE(MAX(receipt_number), 0) + 1
FROM receipts
WHERE customer_id = :ci;

[SQL Fiddle]

Индекс под PK {customer_id, receive_number} должен обеспечивать эффективное выполнение части SELECT этого запроса.

Источник

Вы можете использовать триггер как этот, чтобы обновить ваш столбец:

Определение таблицы с уникальным ограничением на customer_id, receive_number:

CREATE TABLE receipts (id serial primary key, customer_id bigint, receipt_number bigint default 1);
CREATE UNIQUE INDEX receipts_idx ON receipts(customer_id, receipt_number);

Функция для проверки максимального количества чеков для клиента или 1, если нет предыдущих чеков

CREATE OR REPLACE FUNCTION get_receipt_number()  RETURNS TRIGGER AS $receipts$
  BEGIN
    -- This lock will block other transactions from doing anything to table until
    -- committed. This may not offer the best performance, but is threadsafe.
    LOCK TABLE receipts IN ACCESS EXCLUSIVE MODE;
    NEW.receipt_number = (SELECT CASE WHEN max(receipt_number) IS NULL THEN 1 ELSE max(receipt_number) + 1 END FROM receipts WHERE customer_id = new.customer_id);
    RETURN NEW;
  END;
$receipts$ LANGUAGE 'plpgsql';

Триггер для запуска функции на каждой строке вставки:

CREATE TRIGGER rcpt_trigger 
   BEFORE INSERT ON receipts 
   FOR EACH ROW 
   EXECUTE PROCEDURE get_receipt_number();

Затем, выполнив следующее:

db=> insert into receipts (customer_id) VALUES (1);
INSERT 0 1
db=> insert into receipts (customer_id) VALUES (1);
INSERT 0 1
db=> insert into receipts (customer_id) VALUES (2);
INSERT 0 1
db=> insert into receipts (customer_id) VALUES (2);
INSERT 0 1
db=> insert into receipts (customer_id) VALUES (2);

должен дать:

  id | customer_id | receipt_number 
 ----+-------------+----------------  
  14 |           1 |              1  
  15 |           1 |              2  
  16 |           2 |              1 
  17 |           2 |              2  
  18 |           2 |              3
Источник

Почему номера квитанций начинаются с 1 для каждого клиента? Это часть определенных требований?

Простейший способ сделать это состоит в том, чтобы программа, генерирующая новые квитанции, запрашивала у базы данных max(ReceiptNumber), где CustomerId = CurrentCustomerId, а затем добавляла 1.

currentCustomerId - это программная переменная, а не значение базы данных.

Это немного не элегантно, поскольку требует дополнительного поиска в таблице. Вам нужно будет тщательно создать свои индексы, чтобы один из индексов мог ответить на вопрос без полного сканирования таблицы.

Альтернативой, которая немного быстрее во время вставки, является создание дополнительного столбца с именем MaxReeceiptNumber в таблице клиентов. Увеличивайте это всякий раз, когда вы хотите вставить новую квитанцию.

Источник 

-- next CustomerReceiptNo
select coalesce(max(CustomerReceiptNo), 0) + 1
from  Receipt
where CustomerId = specific_customer_id;

Это не потокобезопасно, поэтому убедитесь, что реализовали обработку ошибок, если два отдельных потока пытаются создать новую квитанцию ​​для данного клиента одновременно.

РЕДАКТИРОВАТЬ

Безопасность потоков - это больше, чем просто отказ от условий гонки. Предположим, есть два отдельных потока, создающих новую квитанцию ​​для одного и того же клиента одновременно. Должно ли это случиться? Это нормально, ошибка или нарушение безопасности? Предположим, что банк, в котором два кассира создают новую запись для одного и того же клиента одновременно - что-то очень неправильно. Если это должно произойти, вы можете использовать блокировки; если нет, то какая-то ошибка в порядке.

Источник

Я хотел бы предложить свое решение этой проблемы - использовать столбец +1 в таблице клиентов для хранения latest_receipt_id и использовать инкрементную функцию next_receipt_id( customer_id):

ALTER TABLE customers ADD COLUMN latest_receipt_id integer DEFAULT 1;

-- ensure customer_id, receipt_number pair uniqueness
CREATE UNIQUE INDEX customer_receipt_ids_pair_uniq_index ON receipts USING btree (customer_id, receipt_number);

-- sequence-like function for the next receipt id, 
-- will increment it on every execution
CREATE FUNCTION next_receipt_id( for_customer_id integer ) RETURNS integer
LANGUAGE plpgsql AS 
$$
DECLARE 
  result integer;
BEGIN  
  UPDATE customers SET latest_receipt_id = latest_receipt_id + 1 WHERE id = for_customer_id RETURNING latest_receipt_id INTO result;
  RETURN result;
END;
$$;

Затем вы можете использовать его в триггере получения INSERT:

-- somewhere inside trigger function, triggered on receipt INSERT 
NEW.receipt_number := next_receipt_id( NEW.customer_id );

ИЛИ внутри вашего ORM (псевдокода):

# it does not matter when you assign the receipt_number, 
# it could be even in standalone update execution, just do it only once! 
receipt.update( 'receipt_number = next_receipt_id(customer_id)' )

Не обращая внимания на параллелизм при вставках, вы всегда будете иметь последовательные идентификаторы.

Ура!

Источник
Другие вопросы по тегам