Как эффективно проверить последовательность для используемых и неиспользуемых значений в PostgreSQL

Question

В PostgreSQL (9.3) у меня есть таблица, определенная как:

CREATE TABLE charts
( recid serial NOT NULL,
  groupid text NOT NULL,
  chart_number integer NOT NULL,
  "timestamp" timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  donotsee boolean,
  CONSTRAINT pk_charts PRIMARY KEY (recid),
  CONSTRAINT chart_groupid UNIQUE (groupid),
  CONSTRAINT charts_ichart_key UNIQUE (chart_number)
);

CREATE TRIGGER update_modified
  BEFORE UPDATE ON charts
  FOR EACH ROW EXECUTE PROCEDURE update_modified();

Я хотел бы заменить chart_number на последовательность вроде:

CREATE SEQUENCE charts_chartnumber_seq START 16047;

Таким образом, при использовании триггера или функции добавление новой записи диаграммы автоматически генерирует новый номер диаграммы в порядке возрастания. Однако ни у одной существующей записи диаграммы не может быть изменен номер диаграммы, и за прошедшие годы в назначенных номерах диаграмм были пропуски. Следовательно, прежде чем присваивать новый номер диаграммы новой записи диаграммы, я должен быть уверен, что "новый" номер диаграммы еще не использовался, и любой записи диаграммы с номером диаграммы не присвоен другой номер.

Как это может быть сделано?

Answer 1

Не делай этого. Сначала прочтите эти связанные ответы:

• Последовательность без пропусков, в которой участвуют несколько транзакций с несколькими таблицами
• Сжатие последовательности в PostgreSQL

Если вы все еще настаиваете на заполнении пробелов, вот довольно эффективное решение:

1. Чтобы не искать большие части таблицы на предмет отсутствия chart_number Создайте вспомогательную таблицу со всеми текущими пропусками один раз:

CREATE TABLE chart_gap AS
SELECT chart_number
FROM   generate_series(1, (SELECT max(chart_number) - 1  -- max is no gap
                           FROM charts)) chart_number
LEFT   JOIN charts c USING (chart_number)
WHERE  c.chart_number IS NULL;

2. Установить charts_chartnumber_seq до текущего максимума и конвертировать chart_number к фактическому serial колонка:

SELECT setval('charts_chartnumber_seq', max(chart_number)) FROM charts;

ALTER TABLE charts
   ALTER COLUMN chart_number SET NOT NULL
 , ALTER COLUMN chart_number SET DEFAULT nextval('charts_chartnumber_seq');

ALTER SEQUENCE charts_chartnumber_seq OWNED BY charts.chart_number; 

Подробности:

• Как сбросить последовательность первичных ключей postgres, если они не синхронизированы?
• Безопасно и чисто переименовывать таблицы, использующие последовательные первичные ключи столбцов в Postgres?

3. Пока chart_gap не пусто получить следующий chart_number оттуда. Чтобы разрешить возможные условия гонки с параллельными транзакциями, не заставляя транзакции ждать, используйте консультативные блокировки:

WITH sel AS (
   SELECT chart_number, ...  -- other input values
   FROM   chart_gap
   WHERE  pg_try_advisory_xact_lock(chart_number)
   LIMIT  1
   FOR    UPDATE
   )
, ins AS (
   INSERT INTO charts (chart_number, ...) -- other target columns
   TABLE sel 
   RETURNING chart_number
   )
DELETE FROM chart_gap c
USING  ins i
WHERE  i.chart_number = c.chart_number;

Кроме того, Postgres 9.5 или более поздняя версия имеет удобный FOR UPDATE SKIP LOCKED чтобы сделать это проще и быстрее:

...
   SELECT chart_number, ...  -- other input values
   FROM   chart_gap
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
...

Детальное объяснение:

• Postgres ОБНОВЛЕНИЕ... LIMIT 1

Проверьте результат. После заполнения всех строк возвращается 0 затронутых строк. (вы можете проверить в plpgsql с IF NOT FOUND THEN ...). Затем переключитесь на простой INSERT:

   INSERT INTO charts (...)  -- don't list chart_number
   VALUES (...);  --  don't provide chart_number

Answer 2

В PostgreSQL SEQUENCE обеспечивает два упомянутых вами требования, а именно:

1. Нет повторов
2. Изменения не назначены

Но из-за того, как работает ПОСЛЕДОВАТЕЛЬНОСТЬ (см. Руководство), она не может гарантировать отсутствие пропусков. Среди прочих, первые две причины, которые приходят на ум:

1. Как SEQUENCE обрабатывает параллельные блоки с помощью INSERTS (вы также можете добавить, что концепция Cache также делает это невозможным)
2. Кроме того, DELETE, инициируемые пользователем, являются неуправляемым аспектом, который SEQUENCE не может обработать сам по себе.

В обоих случаях, если вы все еще не хотите пропустить (и если вы действительно знаете, что делаете), у вас должна быть отдельная структура, которая присваивает идентификаторы (вместо использования SEQUENCE). В основном это система, которая имеет список "назначаемых" идентификаторов, хранящихся в ТАБЛИЦЕ, и имеет функцию для выдачи идентификаторов способом FIFO. Это должно позволить вам контролировать УДАЛЕНИЯ и т. Д.

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

Answer 3

Порядковые номера обычно не имеют смысла, так зачем волноваться? Но если вы действительно этого хотите, то следуйте приведенной ниже громоздкой процедуре. Обратите внимание, что это не эффективно; Единственный эффективный вариант - забыть о дырах и использовать последовательность.

Во избежание необходимости сканировать charts таблица на каждой вставке, вы должны отсканировать таблицу один раз и сохранить неиспользованные chart_number Значения в отдельной таблице:

CREATE TABLE charts_unused_chart_number AS
  SELECT seq.unused
  FROM (SELECT max(chart_number) FROM charts) mx,
       generate_series(1, mx(max)) seq(unused)
  LEFT JOIN charts ON charts.chart_number = seq.unused
  WHERE charts.recid IS NULL;

Приведенный выше запрос генерирует непрерывную серию чисел от 1 до текущего максимума chart_number значение, то LEFT JOINс charts таблицы к нему и найти записи, где нет соответствующих charts данные, означающие, что значение ряда не используется как chart_number,

Затем вы создаете триггер, который срабатывает на INSERT на charts Таблица. В функции триггера выберите значение из таблицы, созданной на шаге выше:

CREATE FUNCTION pick_unused_chart_number() RETURNS trigger AS $$
BEGIN
  -- Get an unused chart number
  SELECT unused INTO NEW.chart_number FROM charts_unused_chart_number LIMIT 1;

  -- If the table is empty, get one from the sequence
  IF NOT FOUND THEN
    NEW.chart_number := next_val(charts_chartnumber_seq);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_charts_cn
BEFORE INSERT ON charts
FOR EACH ROW EXECUTE PROCEDURE pick_unused_chart_number();

Легко. Но INSERT может произойти сбой из-за какого-либо другого триггера, прерывающего процедуру, или по любой другой причине. Таким образом, вам нужно проверить, чтобы убедиться, что chart_number было действительно вставлено:

CREATE FUNCTION verify_chart_number() RETURNS trigger AS $$
BEGIN
  -- If you get here, the INSERT was successful, so delete the chart_number
  -- from the temporary table.
  DELETE FROM charts_unused_chart_number WHERE unused = NEW.chart_number;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_charts_verify
AFTER INSERT ON charts
FOR EACH ROW EXECUTE PROCEDURE verify_chart_number();

В определенный момент таблица с неиспользуемыми номерами диаграмм будет пустой, после чего вы сможете (1) ALTER TABLE charts использовать последовательность вместо integer за chart_number; (2) удалить два триггера; и (3) таблица с неиспользованными номерами диаграмм; все в одной транзакции.

Answer 4

Хотя то, что вы хотите, возможно, это не может быть сделано с помощью только SEQUENCE и для работы требуется эксклюзивная блокировка на столе или повторный цикл.

Вам нужно будет:

• LOCK thetable IN EXCLUSIVE MODE
• Найдите первый бесплатный идентификатор, запросив max тогда я делаю left join над generate_series найти первую бесплатную запись. Если есть один.
• Если есть бесплатная запись, вставьте ее.
• Если нет бесплатного входа, звоните nextval и вернуть результат.

Производительность будет абсолютно ужасной, а транзакции будут сериализованы. Там не будет параллелизма. Кроме того, если LOCK это первое, что вы запускаете, воздействуя на эту таблицу, вы будете сталкиваться с тупиками, которые вызывают прерывание транзакций.

Вы можете сделать это менее плохо, используя AFTER DELETE .. FOR EACH ROW триггер, который отслеживает записи, которые вы удаляете INSERTвставляя их в таблицу из одного столбца, которая отслеживает запасные идентификаторы. Вы можете тогда SELECT самый низкий идентификатор из таблицы в вашей функции присвоения идентификаторов на default для столбца, избегая необходимости явной блокировки таблицы, left join на generate_series и max вызов. Транзакции по-прежнему будут сериализованы в блокировку таблицы свободных идентификаторов. В PostgreSQL вы можете решить эту проблему, используя SELECT ... FOR UPDATE SKIP LOCKED, Так что, если вы на 9,5, вы можете сделать это не ужасно, хотя все равно будет медленно.

Я настоятельно советую вам просто использовать SEQUENCE напрямую, и не беспокоиться о повторном использовании значений.