CREATE SEQUENCE или SERIAL - вот в чем вопрос...

В руководстве для получения последовательных уникальных значений, постоянно применяющихся для создания всевозможных ID и прочего (о давно и нежно любимый AUTO_INCREMENT :))))))), указаны два способа:

  • создание генератора последовательности через CREATE SEQUENCE и установка значения по умолчанию через функцию NEXTVAL(...);
  • использование псевдотипа SERIAL.

По своей сути второй способ представляет собой ссылку на первый с заранее заданными параметрами - первое значение равно единице, шаг последовательности - тоже единице и т.д. Если присмотреться внимательно, то при каждом создании SERIAL-поля автоматически создается соответствующий генератор последовательности с параметрами по умолчанию. Такой способ хорош, если задача поставлена так, что необходимо просто энное количество последовательных счетчиков, тогда через SERIAL-поля это делается все просто и быстро.
Однако в менее тривиальных случаях следует задуматься о ручном создании генератора. В частности, в нашем случае с разными видами DVD желательно иметь сквозную нумерацию поля id: поле представляет собой уникальный артикул каждой из моделей, а совпадение кодов у разных позиций, пускай из разных категорий товара, явно нежелательно. Проще создать один генератор, тогда уникальные артикулы будут создаваться автоматически, а повторение будет невозможно в принципе!

Комментарии

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

У SERIAL есть есть

У SERIAL есть есть ещё несколько недостатков.
1. При удалении записей в нумерации возникают разрывы. Всё бы ничего, но читайте 2.
2. Счётчик будет сбрасываться при достижении максимально разрешённого для данного поля значения. Т.е. на таблицах с очень большим количеством записей или где часто используется вставка-удаление записей возможна ситуация, когда значение счётчика будет сброшено в 1 по достижении максимального значения для данного типа. К чему это может привести сказать трудно, но в большинстве случаев ни к чему хорошему :)

Когда я только начинал работать с SQL и изучать SQL СУБД один хороший человек (Денис Галицкий) подсказал мне очень интересное и хитрое решение для обеспечения беспрерывной нумерации.
1. Создаётся таблица где для нумерации используется скажем INTEGER поле ID
3. На таблицу навешиваются два триггера - на удаление и на вставку
4. Создаётся вспомогательная таблица для хранения ID
5. При удалении триггер запускает функцию, которая берёт ID из удаляемой записи и сохраняет его значение во вспомогательной таблице
6. При вставке новой записи триггер запускает функцию, которая сперва просматривает ID во вспомогательной таблице с наименьшим номером и если он найден - возвращает его. Если вспомогательная таблица пуста, то значение ID берётся как максимальное значение ID из основной таблицы + 1
7. Чтобы всё время не дёргать max для вычисления максимальных значний - можно сохранять это значение при вставке в ещё одну вспомогательную таблицу.

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

BIGSERIAL спасет?

Хм, только что специально глянул в руководство. Действительно, при использовании SERIAL вполне реально, что значения счетчика закончатся. Однако можно использовать псевдотип BIGSERIAL, там возможны значения счетчика до 2^64-1 (http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html).
Кстати, при использовании CREATE SEQUENCE по умолчанию используется именно BIGSERIAL.

Да: 2^64 = 18 446 744 073

Да: 2^64 = 18 446 744 073 709 551 616
Это много. Но от дырок в нумерации не спасает.
Не спасает и от таких вещей как ошибочная вставка записи с ещё не использованным номером в SEQUENCE.
Например:

template1# CREATE TABLE tmp_tbl (
template1(# id SERIAL PRIMARY KEY,
template1(# name VARCHAR(20));
NOTICE:  CREATE TABLE создаст подразумеваемую последовательность "tmp_tbl_id_seq" для serial-колонки "tmp_tbl.id"
NOTICE:  CREATE TABLE / PRIMARY KEY создаст подразумеваемый индекс "tmp_tbl_pkey" для таблицы "tmp_tbl"
CREATE TABLE
template1=# INSERT INTO tmp_tbl (name) VALUES('Иван');
INSERT 0 1
template1=# INSERT INTO tmp_tbl (id, name) VALUES(3, 'Пётр');
INSERT 0 1
template1=# select * from tmp_tbl;
 id | name
----+------
  1 | Иван
  3 | Пётр
(2 rows)
 
template1=# INSERT INTO tmp_tbl (name) VALUES('Леонид');
INSERT 0 1
template1=# INSERT INTO tmp_tbl (name) VALUES('Фёдор');
ERROR:  повторный ключ нарушает констрейнт UNIQUE "tmp_tbl_pkey"

А что такого

А что такого страшного в дырках в нумерации? =) Если брать случай данных с небольшим периодом актуальности, то удалить какие-то ставшие ненужными записи совсем некритично. А в случае данных, которые могут понадобиться и через энный период времени - так, по-моему, гораздо проще не удалять их полностью из базы, а ввести какой-то параметр, допустим, бит, определяющий, участвует ли запись в запросах или нет.
А насчет нарушения уникальности - согласитесь, что в приведенном примере проблема возникает не из-за того, что SEQUENCE/SERIAL чем-то плох, а из-за того, что пользователь/приложение передает в БД значения поля, модификация которого вообще-то находится целиком и полностью в сфере ответственности СУБД, Если пользователь/приложение не будет ничего знать о поле id, кроме того, что при необходимости значение этого поля можно получить из БД, то и проблемы не будет...

Да я и не

Да я и не пытаюсь сказать, что SERIAL плох - ни в коем случае.
Я хочу сказать, что у него своя сфера применения, а также показать проблемные места и то, что пользоваться этим типом надо аккуратно. :)

У него сфера

У него сфера применения -- генерировать первичные ключи.

RE: обеспечения беспрерывной нумерации.

Беспрерывной нумерации это решение не гарантирует.
Если удаленых записей больше чем добавленных, то дырки неизбежны. Зачем нужен весь этот огород, если он не решает проблемы в принципе, а на производительности сказывается очень существенно.

6. При вставке


6. При вставке новой записи триггер запускает функцию, которая сперва просматривает ID во вспомогательной таблице с наименьшим номером и если он найден - возвращает его. Если вспомогательная таблица пуста, то значение ID берётся как максимальное значение ID из основной таблицы + 1
7. Чтобы всё время не дёргать max для вычисления максимальных значний - можно сохранять это значение при вставке в ещё одну вспомогательную таблицу.

6. При вставке новой записи триггер запускает функцию, которая сперва вёдёргивает ID во вспомогательной таблице с наименьшим номером, и это не единственная запись - возвращает его. Если найдено всего одно значение, то во вспомогательной таблице увеличивает его на единицу, а возвращает число до увеличения.
7. больше не нужен. Так имхо проще, без создания ещё одной вспомогательной таблицы и запросов к оной.

Ессно при создании вспомогательной таблицы, первый раз, при создании, туда надо положить 1 "вручную".

>> Когда я

>> Когда я только начинал работать с SQL и изучать SQL СУБД один хороший человек (Денис Галицкий) подсказал мне очень интересное и хитрое решение для обеспечения беспрерывной нумерации...

Я пока только изучаю postgresql (знаю oracle и interbase/firebird), может чего не понимаю...

Стартует транзакция 1. Тут же стартует транзакция 2.
Первая лезет в таблицу, находит подходящий ID 1000, делает все изменения.
Вторая лезет в таблицу. Находит подходящий ID 1000 (она ж не видит что сделала первая) и ... повисает на блокировке? Тут же ближе к oracle, а не interbase/firebird? А первая пошла далее колбасить данные на полчасика. Вторая всё висит?
Нафиг нафиг такой ценой от дырок избавляться.

Или в postgresql есть автономные транзакции, как в oracle? Хотя всё одно будут тормоза.

А переполнение последовательности- разве у нас нет INT64? Чтобы за 100 лет исчерпать её надо дёргать 4 000 000 000 раз в секунду (с учётом знаковости). Что-то мне кажется, что любой сервер помрёт от такой нагрузки (а это на одну таблицу, если экономим).

--

Алексей

Только что

Только что заметил интересную вещь, связанную с SERIAL и наследованием, которую стоит принять во внимание - так как она, возможно, будет иногда полезна.
Создадим простую таблицу такого вот вида:

CREATE TABLE test1 (
	id serial);

При этом будет автоматом создана последовательность test1_id_seq. Далее, унаследуем от этой таблицы вторую таблицу, даже не добавляя в неё других полей:

CREATE TABLE test2
	INHERITS (test1);

О удивление! Оказывается, это не совсем новый объект; обе таблицы - и родитель, и потомок - используют общий генератор последовательности. Более того, все таблицы, участвующие в такого рода наследовании, будут использовать общий объект SEQUENCE, созданный их общим прародителем.
Таким образом, в случае, если не требуется непосредственная настройка генератора последовательности для отдельных таблиц, то поле счетчика вполне достаточно создать один раз для его дальнейшего автоматического использования.

Ещё одна интересная вещь

Ещё одна интересная вещь - используя pgAdmin III, я обнаружил, что при задании поля с псевдотипом SERIAL автоматически создается последовательность с "большим" запасом, аналогичная псевдотипу BIGSERIAL.
Однако есть ли в этом смысл, если реальный тип, присваиваемый полю счетчика - integer и большие значения счетчика все равно не влезут в это поле? Осталось спросить об этом у разработчиков =)

Так и вышло

Так и вышло =))))))) В один прекрасный момент будет выдано "Integer out of range" и стоп.

"Integer out of range"

А если бы sequence была "впритирку", то получил бы ошибку об исчерпании последовательности. Какая разница, и то и другое ошибка в проектировании и СУБД тут ни при чем.

А в чем проблема-то?

Я не вижу ничего страшного, ни в появлении "дырок", ни в сбросе индекса в минимальное значение при cycle.
nextval работает вполне четко.
Если при увеличении счетчика на 1 окажется, что в таблице уже есть запись с таким номером (по крайней мере для первичного ключа, где автоматически требуется уникальность), счетчик будет увеличиваться, пока не найдется пустая запись. Если такая не найдется - будет сообщение об ошибке.

Если не верите, создайте короткую последовательность cycle с верхним значением, например, 5. И поэкспериментируйте с добавлением и удалением записей. Проблема возникает только, если в последовательности нет "дырок".

Создавать какие-то вспомогательные таблицы для хранения индексов удаленных полей - странно. Все отлично работает стандартными средствами, без всяких нарушений целостности.

с помощью последовательностей можно организовать любую нужную нумерацию записей. Используйте во всех категорях одну последовательность и совпадение индексов в принципе будет невозможным.

"Страшное" тут только в одном

"Страшное" тут только в одном случае - если значения в SERIAL не хватает для хранения нужного количества значений. Об этом собственно в документации предупреждается.

А "дырки" страшны только если какие-то особые требования предьявляет само приложения.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2010