Ctid postgresql что это

PostgreSQL Antipatterns: уникальные идентификаторы

Достаточно часто у разработчика возникает потребность формировать для записей таблицы PostgreSQL некие уникальные идентификаторы — как при вставке записей, так и при их чтении.

Таблица счетчиков

Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE …

Так делать не надо! Потому что завтра же вам придется решать проблемы:

• постоянных пересекающихся блокировок при UPDATE
  см. PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»
• постепенной деградации скорости доступа к данным таблицы счетчиков
  см. PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой
• … и необходимости ее зачистки при активных транзакциях, которые будут вам мешать
  см. DBA: когда пасует VACUUM — чистим таблицу вручную

Объект SEQUENCE

Для таких задач в PostgreSQL предусмотрена отдельная сущность — SEQUENCE . Она нетранзакционна, то есть не вызывает блокировок, но две «параллельные» транзакции заведомо получат разные значения.

Чтобы получить следующий ID из последовательности, достаточно воспользоваться функцией nextval :

SELECT nextval('seq_name'::regclass);

Иногда необходимо получить сразу несколько ID — для потоковой записи через COPY, например. Использовать для этого setval(currval() + N) — в корне неправильно! По той простой причине, что между вызовами «внутренней» ( currval ) и «внешней» ( setval ) функций конкурирующая транзакция могла изменить текущее значение последовательности. Корректный способ — вызвать nextval нужное количество раз:

SELECT nextval('seq_name'::regclass) FROM generate_series(1, N);

Псевдотип serial

В «ручном» режиме с последовательностями работать не очень удобно. Но ведь типовая задача у нас — обеспечить вставку новой записи с новым sequence-ID! Специально для этой цели в PostgreSQL придуман псевдотип serial , который при генерации таблицы «разворачивается» во что-то типа id integer NOT NULL DEFAULT nextval(‘tbl_id_seq’) .

Запоминать имя автоматически сгенерированной и привязанной к полю последовательности — не надо, для этого есть функция pg_get_serial_sequence(table_name, column_name) . Эту же функцию можно использовать в собственных DEFAULT -подстановках — например, если есть необходимость сделать общую последовательность на несколько таблиц сразу.

Однако, поскольку работа с последовательностью нетранзакционна, если идентификатор из нее получала rollback’нувшаяся транзакция, то в сохраненных записях таблицы последовательность ID окажется «дырявой».

GENERATED-столбцы

Начиная с PostgreSQL 10, появилась возможность объявления идентифицирующего столбца ( GENERATED AS IDENTITY ), соответствующего стандарту SQL:2003. В варианте GENERATED BY DEFAULT поведение эквивалентно serial , а вот с GENERATED ALWAYS все интереснее:

CREATE TABLE tbl( id integer GENERATED ALWAYS AS IDENTITY );

INSERT INTO tbl(id) VALUES(DEFAULT); -- Запрос успешно выполнен: одна строка изменена за 10 мс. INSERT INTO tbl(id) VALUES(1); -- ERROR: cannot insert into column "id" -- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. -- HINT: Use OVERRIDING SYSTEM VALUE to override.

Да, чтобы вставить конкретное значение «поперек» такого столбца, придется приложить дополнительные усилия с помощью OVERRIDING SYSTEM VALUE :

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1); -- Запрос успешно выполнен: одна строка изменена за 11 мс.

Заметьте, что теперь у нас в таблице два одинаковых значения id = 1 — то есть GENERATED не накладывает дополнительных UNIQUE-условий и индексов, а является исключительно декларацией, равно как и serial .

В общем случае, на современных версиях PostgreSQL использование serial не рекомендуется с предпочтительной заменой его на GENERATED . Кроме, разве что, ситуации поддержки кросс-версионных приложений, работающих с PG ниже 10.

Генерируемый UUID

Все хорошо, пока вы работаете в рамках одного экземпляра БД. Но когда их несколько, адекватного способа синхронизации последовательностей не существует (впрочем, это не мешает «неадекватно» их синхронизировать, если очень хочется). Тут на помощь приходит тип UUID и функции генерации значений для него. Я обычно использую uuid_generate_v4() как наиболее «случайную».

Скрытые системные поля

tableoid/ctid

Иногда при выборке записей из таблицы требуется как-то адресоваться к конкретной «физической» записи, или узнать, из какой конкретной секции была получена та или иная запись при обращении к «родительской» таблице при использовании наследования.

В этом случае нам помогут скрытые системные поля, присутствующие в каждой записи:

• tableoid хранит oid -идентификатор таблицы — то есть tableoid::regclass::text дает имя конкретной таблицы-секции
• ctid — «физический» адрес записи в формате (,)

oid

Вплоть до PostgreSQL 11 существовала возможность объявить при создании таблицы атрибут WITH OIDS :

CREATE TABLE tbl(id serial) WITH OIDS;

Каждая запись этой таблицы получала дополнительное скрытое поле oid с глобально-уникальным значением в рамках БД — как это организовано для системных таблиц вроде pg_class , pg_namespace ,…

При вставке записи в такую таблицу генерируемое значение возвращается сразу с результатом запроса:

INSERT INTO tbl(id) VALUES(DEFAULT);

Запрос успешно выполнен: строка с OID 16400 добавлена за 11 мс. 

Такое поле невидимо при «обычном» запросе таблицы:

SELECT * FROM tbl;

id -- 1

Его, как и остальные системные поля надо запрашивать в явном виде:

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid | xmin | xmax | cmin | cmax | oid | id --------------------------------------------------------- 16596 | (0,1) | 572 | 0 | 0 | 0 | 16400 | 1

Правда, значение oid имеет всего 32 бита, поэтому весьма несложно получить переполнение, после которого даже создать никакую таблицу (ей нужен новый oid !) не удастся. Поэтому, начиная с PostgreSQL 12, WITH OIDS более не поддерживается.

«Честное» время clock_timestamp

Иногда при длительном выполнении запроса или процедуры хочется привязать к записи «текущее» время. Неудача ждет того, кто попытается для этого использовать функцию now() — она возвратит одно и то же значение в рамках всей транзакции.

Чтобы получить «вот прямо текущее» время, существует функция clock_timestamp() (и еще пучок ее собратьев). Чем отличается поведение этих функций можно увидеть на примере простого запроса:

SELECT now() , clock_timestamp() FROM generate_series(1, 4);

 now | clock_timestamp -------------------------------+------------------------------- 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03 

• Блог компании Тензор
• PostgreSQL
• Программирование
• SQL
• Администрирование баз данных

Особенности при миграции из Oracle в PostgreSQL (Life Hacks)

Хотя Oracle позволяет пользователям отключать и включать ограничения сколь угодно часто, это обычно не рекомендуется для любой СУБД, поскольку при неправильном выполнении это может привести к повреждению данных. В PostgreSQL ограничения вместо этого создаются как отложенные, и для их отсрочки можно использовать команду SET CONSTRAINTS. Параметр отложенного выполнения указывает время по умолчанию для активации ограничения. Если ограничение в Oracle нельзя отложить, его нужно будет отбросить и воссоздать как откладываемое, хотя иногда можно изменить ограничение, не отбрасывая его.

2 — Delete

При удалении данных в PostgreSQL для указания таблицы необходимо использовать синтаксис FROM, в Oracle же не требуется.

3 — Удаление объектов базы данных

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

4 — Dual Table

Поскольку предложение FROM является обязательным в Oracle для каждого оператора SELECT, FROM DUAL используется для операторов SELECT, где имя таблицы не требуется. PostgreSQL не требует предложения FROM, поэтому FROM DUAL не требуется и обычно его можно опустить. Если в PostgreSQL требуется dual table, ее можно сгенерировать как представление.

5 — Пустые строки и NULL

В Oracle пустые строки имеют значения NULL, но они не считаются NULL в PostgreSQL. В Oracle можно проверить, пуста ли строка или нет, с помощью оператора IS NULL, но в PostgreSQL он вернет FALSE для пустой строки (и TRUE для NULL).

6 — Federation to Foreign Data Wrappers

Функция Oracle Federation позволяет пользователям обрабатывать таблицы из других баз данных, как локальные данные. Оболочки сторонних данных PostgreSQL более универсальны и позволяют подключаться к более широкому диапазону данных.

7 — GRANT

Команда GRANT действует аналогично в Oracle и PostgreSQL. Есть два основных варианта — они могут использоваться для предоставления привилегий для объекта базы данных и для предоставления членства роли.

При этом, не все привилегии Oracle доступны в PostgreSQL. Например, предоставление привилегии триггера для таблицы позволяет пользователям создавать триггеры, но в отличие от Oracle только владелец таблицы может удалять триггеры.

8 — Иерархические запросы

PostgreSQL не поддерживает START WITH. . .CONNECT BY, который Oracle использует для иерархических запросов. Вместо этого PostgreSQL использует WITH RECURSIVE.

9 — Joins with (+)

В Oracle есть специальный оператор (+) для выполнения левого и правого outer joins. В PostgreSQL эта функция отсутствует, поэтому необходимо указывать команду JOIN.

10 — Проверка NOT NULL

Для определения какие столбцы в таблице Oracle НЕ являются NULL, необходимо использовать команду CHECK ( IS NOT NULL). В PostgreSQL же вместо этого имеется столбец «attnotnull» в pg_attribute, в котором хранится информация о столбцах таблицы, в том числе – о столбце с Constraint NOT NULL.

11 — Преобразование PL / SQL в PL / pgSQL

Процедурный язык PostgreSQL PL/pgSQL во многих отношениях похож на Oracle PL/SQL. Оба являются императивными языками с блочной структурой и похожими форматами для присваиваний, циклов и условных выражений.

12 — Remote objects

Для доступа к remote objects можно использовать оболочку внешних данных (Oracle_fdw) для доступа к любой другой базе данных. Столбцы ROWID, CTID и IdentityPostgreSQL не имеет точного эквивалента псевдостолбцу ROWID в Oracle, который предоставляет адрес строки в таблице. CTID в PostgreSQL аналогичен, за исключением того, что его значение изменяется каждый раз при выполнении VACUUM. Вместо этого используются столбцы идентификаторов, значения которых создаются автоматически при создании строки и никогда не изменяется. Значение можно указать, чтобы оно создавалось ВСЕГДА или ПО УМОЛЧАНИЮ. GENERATED BY DEFAULT позволяет пользователю вставить или обновить значение, а не использовать значение, сгенерированное системой.

13 — Sequences

Sequences имеют другой синтаксис в Oracle и PostgreSQL, и их необходимо обновлять вручную или с помощью скрипт.

14 — SUBSTR

Функция SUBSTR по-разному работает в Oracle и PostgreSQL. В Oracle оператор SELECT SUBSTR (‘ABC’, — 1) FROM DUAL; возвращает «C», а эквивалентный SELECT SUBSTR (‘ABC’, — 1); в PostgreSQL вернет ABC.

15 — MERGE INTO

В PostgreSQL отсутствует оператор MERGE INTO, в отличие от Oracle.

16 — Синонимы

PostgreSQL не поддерживает синонимы. Вместо CREATE SYNONYM Oracle для доступа к удаленным объектам в PostgreSQL можно использовать SET search_path для включения удаленного определения.

17 — SYSDATE

Функция Oracle SYSDATE возвращает дату и время (в часовом поясе сервера). PostgreSQL не имеет соответствующей функции, но существует ряд методов для получения даты и времени для различных целей: statement_timestamp () дает текущую дату и время с начала текущего оператора; now () и transaction_timestamp () дают дату и время с начала текущей транзакции, а clock_timestamp () дает текущую дату и время с момента выполнения функции.

18 — TO_DATE

Функция to_date () как в Oracle, так и в PostgreSQL возвращает тип данных даты. Однако тип данных даты PostgreSQL предоставляет дату (год, месяц, день), а значение типа данных даты Oracle предоставляет дату и время (год, месяц, день, час, минута, секунда). Чтобы избежать этой несовместимости, используйте to_timestamp () PostgreSQL. Решением этой несовместимости является преобразование TO_DATE () в TO_TIMESTAMP ().

19 — Транзакции

Oracle всегда использует транзакции, но в PostgreSQL их нужно активировать. В Oracle выполнение любого оператора запускает транзакцию и заканчивается оператором COMMIT. В PostgreSQL транзакция начинается с оператора BEGIN, а также заканчивается оператором COMMIT. Уровни изоляции транзакций одинаковы в PostgreSQL и Oracle, и Read Committed является уровнем изоляции по умолчанию для обоих.

20 — Обработка ошибок транзакции

PostgreSQL создан для облегчения управления транзакциями и обработки ошибок с полной поддержкой ACID и уровнями изоляции. Он также способен обрабатывать ошибки времени выполнения и предоставляет надежные коды ошибок и сообщения для PL/pgSQL или кода приложения. Однако они обрабатываются иначе, чем Oracle, поэтому изменения все же необходимо внести. Вот несколько советов по оптимизации обработки ошибок в PostgreSQL:

• Контроль транзакций внутри PL / pgSQL не разрешен: вы не можете зафиксировать или откатить транзакцию внутри хранимой процедуры.
• Когда во время транзакции возникает исключительная ситуация во время выполнения, транзакция должна быть отменена, перед выполнением другой операции, потому что транзакция прерывается, когда обнаруживает ошибку. Журнал приложения покажет следующее сообщение об ошибке:
• Используйте блок BEGIN… EXCEPTION… END для обработки исключений, чтобы код улавливал любые возникающие ошибки. Это автоматически устанавливает точку сохранения перед блоком и откатывается к ней при возникновении исключения. Имейте в виду, что, поскольку блоки исключений создают точку сохранения, они дороги, поэтому добавляйте их осторожно.
• Сопоставьте коды ошибок и типы исключений из Oracle в PostgreSQL. Хотя некоторые коды ошибок одинаковы в обоих случаях, другие различаются. Язык программирования также влияет на это — например, специфичные для Oracle исключения JDBC должны быть заменены либо общими исключениями между базами данных, либо специфичными для PostgreSQL.
• Обеспечение правильной обработки транзакций и ошибок в базе данных PostgreSQL является важной частью процесса миграции и обычно требует тщательного анализа базы данных и кода приложения.

PostgreSQL — Системные столбцы

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

• tableoid OID таблицы, содержащей эту строку. Этот столбец особенно удобен для запросов, которые выбирают из секционированных таблиц или иерархий наследования, поскольку без него трудно сказать, из какой отдельной таблицы взята строка. Их tableoid можно объединить со oid столбцом pg_class для получения имени таблицы.
• xmin Идентификатор (идентификатор транзакции) транзакции вставки для этой версии строки. (Версия строки — это отдельное состояние строки; каждое обновление строки создает новую версию строки для той же логической строки.)
• cmin Идентификатор команды (начиная с нуля) в транзакции вставки.
• xmax Идентификатор (идентификатор транзакции) удаляющей транзакции или ноль для восстановленной версии строки. Этот столбец может быть ненулевым в версии с видимой строкой. Обычно это указывает на то, что транзакция удаления еще не зафиксирована или попытка удаления была отменена.
• cmax Идентификатор команды в удаляющей транзакции или ноль.
• ctid Физическое расположение версии строки в ее таблице. Обратите внимание, что, хотя ctid можно использовать для очень быстрого поиска версии строки, строка ctid изменится, если она будет обновлена ​​или перемещена с помощью VACUUM FULL . Поэтому ctid бесполезен в качестве долгосрочного идентификатора строки. Для идентификации логических строк следует использовать первичный ключ.

Идентификаторы транзакций также являются 32-битными величинами. В долгоживущих базах данных идентификаторы транзакций могут повторяться. Это не является фатальной проблемой при соответствующих процедурах технического обслуживания. Однако неразумно полагаться на уникальность идентификаторов транзакций в долгосрочной перспективе (более одного миллиарда транзакций).

Идентификаторы команд также являются 32-битными величинами. Это создает жесткое ограничение в 2 32 (4 миллиарда) SQL -команд в рамках одной транзакции. На практике это ограничение не является проблемой — обратите внимание, что ограничение на количество команд SQL , а не на количество обрабатываемых строк. Кроме того, только команды, которые фактически изменяют содержимое базы данных, будут использовать идентификатор команды.

Ctid postgresql что это

В каждой таблице есть несколько системных столбцов, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских столбцов. (Заметьте, что это не зависит от того, является ли имя ключевым словом или нет; заключение имени в кавычки не поможет избежать этого ограничения.) Эти столбцы не должны вас беспокоить, вам лишь достаточно знать об их существовании.

Идентификатор объекта (object ID) для строки. Этот столбец присутствует, только если таблица была создана с указанием WITH OIDS , или если в момент её создания была установлена переменная конфигурации default_with_oids. Этот столбец имеет тип oid (с тем же именем, что и сам столбец); подробнее об этом типе см. Раздел 8.18. tableoid

Идентификатор объекта для таблицы, содержащей строку. Этот столбец особенно полезен для запросов, имеющих дело с иерархией наследования (см. Раздел 5.9), так как без него сложно определить, из какой таблицы выбрана строка. Связав tableoid со столбцом oid в таблице pg_class , можно будет получить имя таблицы. xmin

Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.) cmin

Номер команды (начиная с нуля) внутри транзакции, добавившей строку. xmax

Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено. cmax

Номер команды в удаляющей транзакции или ноль. ctid

Физическое расположение данной версии строки в таблице. Заметьте, что хотя по ctid можно очень быстро найти версию строки, значение ctid изменится при выполнении VACUUM FULL . Таким образом, ctid нельзя применять в качестве долгосрочного идентификатора строки. Для идентификации логических строк лучше использовать OID или даже дополнительный последовательный номер.

Коды OID представляют собой 32-битные значения и выбираются из единого для всей СУБД счётчика. В больших или долгоживущих базах данных этот счётчик может пойти по кругу. Таким образом, не рекомендуется рассчитывать на уникальность OID, если только вы не обеспечите её дополнительно. Если вам нужно идентифицировать строки таблицы, настоятельно рекомендуется использовать последовательности. Однако можно использовать и коды OID, при выполнении следующих условий:

Когда для идентификации строк таблиц применяется OID, в каждой такой таблице должно создаваться ограничение уникальности для столбца OID. Когда такое ограничение уникальности (или уникальный индекс) существует, система позаботится о том, чтобы OID новой строки не совпал с уже существующими. (Конечно, это возможно, только если в таблице меньше 2 32 (4 миллиардов) строк, а на практике таблицы должны быть гораздо меньше, иначе может пострадать производительность системы.)

Никогда не следует рассчитывать, что OID будут уникальны среди всех таблиц; в качестве глобального идентификатора в рамках базы данных используйте комбинацию tableoid и OID строки.

Идентификаторы транзакций также являются 32-битными. В долгоживущей базе данных они могут пойти по кругу. Это не критично при правильном обслуживании БД; подробнее об этом см. Главу 23. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.

Идентификаторы команд также 32-битные. Это создаёт жёсткий лимит на 2 32 (4 миллиарда) команд SQL в одной транзакции. На практике это не проблема — заметьте, что это лимит числа команд SQL , а не количества обрабатываемых строк. Кроме того, идентификатор получают только те команды, которые фактически изменяют содержимое базы данных.

Пред.	Наверх	След.
5.3. Ограничения	Начало	5.5. Изменение таблиц