Последовательности

Евгения Кузина

Последовательность — это объект, относящийся к определенной схеме, который генерирует ряд уникальных целых чисел в порядке возрастания или убывания. Последовательность не связана ни с какой таблицей, но её можно использовать для заполнения данных в первичном ключе или уникальных столбцах таблицы. Типы PostgreSQL serial представляют собой последовательности.

Создание последовательности

Чтобы создать последовательность, выполните команду CREATE SEQUENCE, которая создает новый генератор последовательности.

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <имя>
    [ AS <тип_данных> ]
    [ INCREMENT [ BY ] <инкремент> ]
    [ MINVALUE <мин_значение> | NO MINVALUE ] [ MAXVALUE <макс_значение> | NO MAXVALUE ]
    [ START [ WITH ] <начало> ] [ CACHE <размер_кеша> ] [ [ NO ] CYCLE ]
    [ OWNED BY { <имя_таблицы.имя_столбца> | NONE } ]
Параметры команды CREATE SEQUENCE

TEMPORARY | TEMP

Если указано, объект последовательности создается только для текущей сессии. Временная последовательность удаляется в конце сессии. Существующие постоянные последовательности с тем же именем не отображаются в текущей сессии, пока существует временная последовательность. Чтобы обратиться к постоянной последовательности, необходимо дополнить имя указанием схемы

IF NOT EXISTS

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

<имя>

Имя новой последовательности. Может включать имя схемы (<имя_схемы>.<имя_последовательности>)

<тип_данных>

Необязательное выражение AS <тип_данных> определяет тип данных последовательности. Валидные значения: smallint, integer и bigint. Значение по умолчанию — bigint. Тип данных определяет минимальное и максимальное значения последовательности по умолчанию

<инкремент>

Необязательное выражение INCREMENT [BY] <инкремент> указывает число, которое добавляется к текущему значению последовательности для создания нового значения. Положительное значение определяет возрастающую последовательность, отрицательное — убывающую. Значение по умолчанию — 1

MINVALUE <мин_значение> | NO MINVALUE

Необязательное выражение MINVALUE <мин_значение> определяет минимальное значение, которое может сгенерировать последовательность. Если оно не установлено или указано NO MINVALUE, используются значения по умолчанию: для возрастающей последовательности — 1, для убывающей — минимальное значение типа данных

MAXVALUE <макс_значение> | NO MAXVALUE

Необязательное выражение MAXVALUE <макс_значение> определяет максимальное значение для последовательности. Если оно не установлено или указано NO MAXVALUE, используются значения по умолчанию: для возрастающей последовательности — максимальное значение типа данных, для убывающей — -1

<начало>

Необязательное выражение START WITH <начало> указывает значение, с которого начинается последовательность. Начальное значение по умолчанию для возрастающих последовательностей — мин_значение, для убывающих — макс_значение

<размер_кеша>

Необязательное выражение CACHE <размер_кеша> указывает, сколько значений последовательности должно быть получено и сохранено в памяти для более быстрого доступа. Минимальное значение — 1, оно означает, что может быть сгенерировано только одно значение (кеш не используется). 1 также является значением по умолчанию

[ NO ] CYCLE

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

Если указано необязательное ключевое слово NO CYCLE, любые вызовы nextval после того, как последовательность достигнет максимального значения, вернут ошибку. NO CYCLE используется по умолчанию

OWNED BY <имя_таблицы.имя_столбца> | NONE

Выражение OWNED BY связывает последовательность со столбцом таблицы. Если этот столбец или вся таблица удаляется, последовательность будет удалена автоматически. Таблица должна иметь того же владельца и находиться в той же схеме, что и последовательность. Выражение OWNED BY NONE указывает, что ассоциации со столбцом нет. OWNED BY NONE используется по умолчанию

В следующем примере создается последовательность, начинающаяся с 2.

CREATE SEQUENCE mysequence START 2;

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

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

Последовательности основаны на арифметике bigint, поэтому диапазон не может превышать диапазон восьмибайтового целого числа (от -9223372036854775808 до 9223372036854775807).

Чтобы проверить текущее состояние последовательности, используйте SELECT.

SELECT * FROM mysequence;

Результат:

 last_value | log_cnt | is_called
------------+---------+-----------
          2 |       0 | f

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

PostgreSQL не логирует (не записывает в WAL) каждую полученную выборку из последовательности по соображениям производительности, он заранее записывает несколько выборок. В случае сбоя PostgreSQL теряет (пропускает) столько выборок, сколько было предварительно записано. log_cnt показывает, сколько выборок осталось до того, как необходимо будет создать новую запись WAL.

Если поле is_called имеет значение t — true, следующий вызов nextval продвинет последовательность перед возвратом значения. Если is_called имеет значение f — false, nextval вернет значение last_value.

После создания последовательности используйте функции манипулирования последовательностью для работы с ней. См. Работа с последовательностями.

Когда для объекта последовательности определен параметр размер_кеша больше 1, он используется одновременно несколькими сессиями. Каждая сессия выделяет и кеширует последовательные значения во время доступа к объекту последовательности и соответственно увеличивает последнее значение. Затем следующие обращения к последовательности в количестве размер_кеша-1 в этой сессии возвращают предварительно выделенные значения без обращения к объекту последовательности. Значения, выделенные, но не использованные в этой сессии, будут потеряны после её завершения. Это приводит к пропускам значений в последовательности.

Хотя для нескольких сессий гарантированно выделяются разные значения последовательности, при рассмотрении всех сессий значения могут генерироваться не последовательно. Например, при установке параметра размер_кеша равным 10 первая сессия может зарезервировать значения 1 — 10, а nextval вернет 1. Затем вторая сессия может зарезервировать значения 11 — 20, и nextval вернет 11 до того, как nextval первой сессии выдаст 2. Следовательно, при значении параметра размер_кеша равного 1, можно быть уверенным, что значения, возвращенные nextval, генерируются последовательно. При значении параметра размер_кеша больше единицы следует предполагать, что все значения nextval различны, но не генерируются последовательно. Кроме того, значение последовательности last_value отражает последнее значение, зарезервированное любой сессией, независимо от того, было ли оно уже возвращено функцией nextval.

CREATE SEQUENCE соответствует стандарту SQL со следующими исключениями:

  • Необходимо использовать функцию nextval вместо стандартного выражения NEXT VALUE FOR.

  • Выражение OWNED BY является расширением PostgreSQL.

Изменение последовательности

Используйте ALTER SEQUENCE, чтобы изменить определение генератора последовательности. Любые параметры, которые не заданы с помощью команды ALTER SEQUENCE, сохраняют свои предыдущие значения.

Вы должны быть владельцем последовательности, чтобы использовать ALTER SEQUENCE. У вас также должно быть право CREATE для новой схемы, чтобы изменить схему последовательности. Для изменения владельца вы должны быть прямым или косвенным членом новой роли владельца, и эта роль должна иметь привилегию CREATE в схеме последовательности.

ALTER SEQUENCE [ IF EXISTS ] <имя>
[ AS <тип_данных> ]
[ INCREMENT [ BY ] <инкремент> ]
[ MINVALUE <мин_значение> | NO MINVALUE ] [ MAXVALUE <макс_значение> | NO MAXVALUE ]
[ START [ WITH ] <начало> ]
[ RESTART [ [ WITH ] <перезапуск> ] ]
[ CACHE <размер_кеша> ] [ [ NO ] CYCLE ]
[ OWNED BY { <имя_таблицы.имя_столбца> | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] <имя> OWNER TO { <новый_владелец> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] <имя> RENAME TO <новое_имя>
ALTER SEQUENCE [ IF EXISTS ] <имя> SET SCHEMA <новая_схема>
Параметры команды ALTER SEQUENCE

<имя>

Имя последовательности, подлежащей изменению. Может включать имя схемы (<имя_схемы>.<имя_последовательности>)

IF EXISTS

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

<тип_данных>

Необязательное выражение AS <тип_данных> изменяет тип данных последовательности. Валидные значения: smallint, integer и bigint. Изменение типа данных изменяет минимальное и максимальное значения последовательности, если предыдущие минимальное и максимальное значения были установлены старым типом данных (последовательность была создана с использованием NO MINVALUE или NO MAXVALUE, неявно или явно). В противном случае минимальное и максимальное значения сохраняются, если только новые минимальные и максимальные значения не заданы как часть той же команды. Если минимальное и максимальное значения не соответствуют новому типу данных, генерируется ошибка

<инкремент>

Необязательное выражение INCREMENT [BY] <инкремент> изменяет число, которое добавляется к текущему значению последовательности для создания нового значения. Положительное значение определяет возрастающую последовательность, отрицательное — убывающую. Если не указано, сохраняется старое значение инкремента

MINVALUE <мин_значение> | NO MINVALUE

Необязательное выражение MINVALUE <мин_значение> изменяет минимальное значение, которое может сгенерировать последовательность. Если указано NO MINVALUE, будут использоваться для возрастающей последовательности — 1, для убывающей — минимальное значение типа данных. Если не указано, сохраняется старое минимальное значение

MAXVALUE <макс_значение> | NO MAXVALUE

Необязательное выражение MAXVALUE <макс_значение> изменяет максимальное значение для последовательности. Если указано NO MAXVALUE, будут использоваться для возрастающей последовательности — максимальное значение типа данных, для убывающей — -1. Если не указано, сохраняется старое максимальное значение

<начало>

Необязательное выражение START WITH <начало> меняет записанное начальное значение последовательности. При этом текущее значение последовательности не меняется, а только устанавливается значение, которое будет применяться командой ALTER SEQUENCE RESTART

<перезапуск>

Необязательное выражение RESTART [ WITH <перезапуск> ] меняет текущее значение последовательности. Работает подобно вызову функции setval с параметром is_called = false. Указанное значение перезапуска будет возвращено при следующем вызове функции nextval. Отсутствие в RESTART значения перезапуск равносильно передаче начального значения, записанного командой CREATE SEQUENCE или последнего установленного командой ALTER SEQUENCE START WITH

<размер_кеша>

Необязательное выражение CACHE <размер_кеша> устанавливает, сколько значений последовательности должно быть получено и сохранено в памяти для более быстрого доступа. Минимальное значение — 1 (одновременно может быть сгенерировано только одно значение, кеш не используется). Если не указано, сохраняется текущее значение размера кеша

[ NO ] CYCLE

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

Если указано необязательное ключевое слово NO CYCLE, любые вызовы nextval после того, как последовательность достигнет максимального значения, вернут ошибку. Если не указано ни CYCLE, ни NO CYCLE, сохраняется старое поведение

OWNED BY <имя_таблицы.имя_столбца> | NONE

Выражение OWNED BY связывает последовательность со столбцом таблицы. Если этот столбец или вся таблица удаляется, последовательность будет удалена автоматически. Таблица должна иметь того же владельца и находиться в той же схеме, что и последовательность. Выражение OWNED BY NONE удаляет любую существующую связь

<новый_владелец>

Имя пользователя, назначаемого новым владельцем последовательности

<новое_имя>

Новое имя последовательности

<новая_схема>

Новая схема последовательности

Следующий пример делает последовательность mysequence убывающей от 10 до 2 с использованием шага 2 и зацикливает последовательность:

ALTER SEQUENCE mysequence
    INCREMENT -2
    MINVALUE 2
    MAXVALUE 10
    RESTART 10
    CYCLE;

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

ALTER SEQUENCE не влияет на значение currval последовательности.

ALTER SEQUENCE блокирует одновременные вызовы nextval, currval, lastval и setval.

По историческим причинам ALTER TABLE тоже может работать с последовательностями, но допустимы только разновидности ALTER TABLE, равнозначные вышеперечисленным формам.

Удаление последовательности

Команда DROP SEQUENCE удаляет последовательность. Эту операцию может осуществить только владелец последовательности или суперпользователь.

DROP SEQUENCE [ IF EXISTS ] <имя> [, ...] [ CASCADE | RESTRICT ]
Параметры команды DROP SEQUENCE

IF EXISTS

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

<имя> [, …​]

Имя последовательности, подлежащей удалению. Может включать имя схемы (<имя_схемы>.<имя_последовательности>). Чтобы удалить несколько последовательностей, укажите их имена через запятую

CASCADE

Удалять объекты, зависящие от данной последовательности, и, в свою очередь, все объекты, зависящие от этих объектов. См. Dependency Tracking

RESTRICT

Отказаться от удаления последовательности, если от нее зависят какие-либо объекты. Это поведение по умолчанию

Пример:

DROP SEQUENCE mysequence;

Работа с последовательностями

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

nextval

Функция nextval продвигает объект последовательности к следующему значению и возвращает это значение.

nextval ( <regclass> ) -> bigint

Если несколько сессий одновременно выполняют nextval, каждая безопасно получает отдельное значение последовательности. Если объект последовательности был создан с параметрами по умолчанию, вызовы nextval будут возвращать последовательные значения, начиная с 1. Другие варианты поведения можно указать, используя соответствующие параметры в команде CREATE SEQUENCE.

Функция nextval требует привилегий USAGE или UPDATE для последовательности.

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

  1. Создайте таблицу.

    CREATE TABLE book_orders (
    order_id SERIAL,
    item_id INT NOT NULL,
    book_name VARCHAR NOT NULL,
    price NUMERIC NOT NULL,
    PRIMARY KEY(order_id, item_id)
);

  2. Создайте новую последовательность, связанную со столбцом item_id таблицы book_orders.

    CREATE SEQUENCE book_order_items START 1 OWNED BY book_orders.item_id;

  3. Добавьте строки в таблицу, используя функцию nextval для заполнения столбца item_id.

    INSERT INTO
    book_orders(order_id, item_id, book_name, price)
VALUES
    (1, nextval('book_order_items'),'Hyperion',21),
    (1, nextval('book_order_items'),'War and Peace',26),
    (2, nextval('book_order_items'),'1984',20),
    (2, nextval('book_order_items'),'The Time Machine',19);

  4. Проверьте данные таблицы.

    SELECT * FROM book_orders;

    Результат:

     order_id | item_id |    book_name     | price
----------+---------+------------------+-------
        1 |       1 | Hyperion         |    21
        1 |       2 | War and Peace    |    26
        2 |       3 | 1984             |    20
        2 |       4 | The Time Machine |    19

Если вы удалите таблицу book_orders, последовательность book_order_items также будет удалена.

setval

Функция setval устанавливает текущее значение объекта последовательности, а также флаг is_called. setval может вызываться с двумя или тремя параметрами.

setval ( <regclass>, <bigint> [, <boolean> ] ) -> bigint

Где:

  • <regclass> — имя последовательности;

  • <bigint> — значение last_value;

  • <boolean> — значение is_called.

Форма с двумя параметрами присваивает полю last_value указанное значение (<bigint>), а для поля is_called устанавливает значение true, что означает, что следующий вызов nextval продвигает последовательность перед возвращением значения. Функция currval также будет возвращать указанное значение. Если setval вызывается с тремя параметрами, для is_called может быть установлено значение true или false. true будет иметь тот же эффект, что и вызов функции с двумя параметрами. Если для is_called установлено значение false, следующий вызов nextval вернет указанное значение. Функция currval также будет возвращать это значение. В таблице ниже приведены примеры setval и их результаты.

Команда Значение, которое вернет nextval

SELECT setval('mysequence', 14);

15

SELECT setval('mysequence', 14, true);

15

SELECT setval('mysequence', 14, false);

14

Результат, возвращаемый setval, является значением второго параметра.

Функция setval требует привилегии UPDATE для последовательности.

currval

Функция currval возвращает последнее значение, полученное с помощью nextval, для указанной последовательности в текущей сессии.

currval ( <regclass> ) -> bigint

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

Функция currval требует привилегий USAGE или SELECT для последовательности.

Пример:

SELECT currval('mysequence');

Результат:

 currval
---------
      14
(1 row)

lastval

Функция возвращает последнее полученное значение с помощью nextval в текущей сессии для любой последовательности.

lastval () -> bigint

Эта функция идентична currval, но вместо того, чтобы принимать имя последовательности в качестве аргумента, она ссылается на последний вызов nextval, относящийся к любой последовательности в текущей сессии. Если nextval не вызывалась в текущей сессии и вы пытаетесь выполнить lastval, возникнет ошибка.

Функция lastval требует привилегий USAGE или SELECT для последовательности.

Пример:

SELECT * FROM lastval();

Результат:

 lastval
---------
      14
(1 row)

Получение информации о последовательностях

Вы можете получить информацию о последовательностях из представления sequences, хранящегося в схеме information_schema.

SELECT sequence_schema, sequence_name, data_type, minimum_value, maximum_value, increment, cycle_option
FROM information_schema.sequences;

Результат:

 sequence_schema |      sequence_name       | data_type | minimum_value |    maximum_value    | increment | cycle_option
-----------------+--------------------------+-----------+---------------+---------------------+-----------+--------------
 public          | mysequence               | bigint    | 2             | 10                  | -2        | YES
 public          | book_orders_order_id_seq | integer   | 1             | 2147483647          | 1         | NO
 public          | book_order_items         | bigint    | 1             | 9223372036854775807 | 1         | NO
(3 rows)

Результат включает автоматически сгенерированную последовательность book_orders_order_id_seq для столбца order_id типа serial.

Представление pg_sequences также обеспечивает доступ к полезной информации о каждой последовательности в базе данных. Например, вы можете получить информацию о владельцах последовательностей, которой нет в information_schema.sequences:

SELECT sequencename, sequenceowner FROM pg_sequences;

Результат:

       sequencename       | sequenceowner
--------------------------+---------------
 mysequence               | postgres
 book_orders_order_id_seq | postgres
 book_order_items         | postgres

Кроме того, вы также можете получить данные из системных каталогов pg_sequence и pg_class, используя следующий запрос:

SELECT pg_class.relname, pg_sequence.* FROM pg_sequence INNER JOIN pg_class ON pg_sequence.seqrelid = pg_class.oid;

Результат:

relname                 |seqrelid|seqtypid|seqstart|seqincrement|      seqmax       |seqmin|seqcache| seqcycle
------------------------+--------+--------+--------+------------+-------------------+------+--------+----------
mysequence              | 157170 |     20 |      2 |         -2 |                 10|    2 |      1 | t
book_orders_order_id_seq| 160111 |     23 |      1 |          1 |         2147483647|    1 |      1 | f
book_order_items        | 160126 |     20 |      1 |          1 |9223372036854775807|    1 |      1 | f
(3 rows)

Также можно использовать метакоманду psql \ds для вывода списка последовательностей, созданных в текущей базе данных.

\ds

Результат:

                    List of relations
 Schema |           Name           |   Type   |  Owner
--------+--------------------------+----------+----------
 public | book_order_items         | sequence | postgres
 public | book_orders_order_id_seq | sequence | postgres
 public | mysequence               | sequence | postgres
(3 rows)
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней