Что такое oid столбец
PostgreSQL Antipatterns: уникальные идентификаторы
Достаточно часто у разработчика возникает потребность формировать для записей таблицы PostgreSQL некие уникальные идентификаторы — как при вставке записей, так и при их чтении.
Таблица счетчиков
Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE …
Так делать не надо! Потому что завтра же вам придется решать проблемы:
Объект SEQUENCE
Чтобы получить следующий ID из последовательности, достаточно воспользоваться функцией nextval :
Иногда необходимо получить сразу несколько ID — для потоковой записи через COPY, например. Использовать для этого setval(currval() + N) — в корне неправильно! По той простой причине, что между вызовами «внутренней» ( currval ) и «внешней» ( setval ) функций конкурирующая транзакция могла изменить текущее значение последовательности. Корректный способ — вызвать nextval нужное количество раз:
Псевдотип serial
Однако, поскольку работа с последовательностью нетранзакционна, если идентификатор из нее получала rollback’нувшаяся транзакция, то в сохраненных записях таблицы последовательность ID окажется «дырявой».
GENERATED-столбцы
Да, чтобы вставить конкретное значение «поперек» такого столбца, придется приложить дополнительные усилия с помощью OVERRIDING SYSTEM VALUE :
Генерируемый UUID
Все хорошо, пока вы работаете в рамках одного экземпляра БД. Но когда их несколько, адекватного способа синхронизации последовательностей не существует (впрочем, это не мешает «неадекватно» их синхронизировать, если очень хочется). Тут на помощь приходит тип UUID и функции генерации значений для него. Я обычно использую uuid_generate_v4() как наиболее «случайную».
Скрытые системные поля
tableoid/ctid
Иногда при выборке записей из таблицы требуется как-то адресоваться к конкретной «физической» записи, или узнать, из какой конкретной секции была получена та или иная запись при обращении к «родительской» таблице при использовании наследования.
В этом случае нам помогут скрытые системные поля, присутствующие в каждой записи:
Вплоть до PostgreSQL 11 существовала возможность объявить при создании таблицы атрибут WITH OIDS :
При вставке записи в такую таблицу генерируемое значение возвращается сразу с результатом запроса:
Такое поле невидимо при «обычном» запросе таблицы:
Его, как и остальные системные поля надо запрашивать в явном виде:
«Честное» время clock_timestamp
Иногда при длительном выполнении запроса или процедуры хочется привязать к записи «текущее» время. Неудача ждет того, кто попытается для этого использовать функцию now() — она возвратит одно и то же значение в рамках всей транзакции.
Чтобы получить «вот прямо текущее» время, существует функция clock_timestamp() (и еще пучок ее собратьев). Чем отличается поведение этих функций можно увидеть на примере простого запроса:
Что такое oid столбец
70.2.1. Формат файла данных
Общий формат файла: открывающая квадратная скобка, один или более наборов фигурных скобок, каждый из которых представляет строку каталога, и закрывающая квадратная скобка. После каждой закрывающей фигурной скобки должна идти запятая.
Все значения должны заключаться в апострофы. Апострофы внутри значений экранируются обратной косой чертой. Обратные косые черты в данных могут, но не обязательно должны дублироваться; это соответствует правилам Perl по оформлению простых строковых констант. Заметьте, что обратные косые черты, фигурирующие в данных, будут обрабатываться сканером исходных данных как символы экранирования, согласно тем же правилам записи строковых констант (см. Подраздел 4.1.2.2); например, \t преобразуется в символ табуляции. Если вы хотите получить именно обратную косую черту в окончательном значении, вам надо будет написать четыре этих символа: Perl отбрасывает два и оставляет \\ сканеру исходных данных.
Комментарии предваряются знаком # и должны размещаться в отдельных строках.
Для большей наглядности значения полей, выражающие OID других записей каталога, могут быть представлены именами, а не только числовыми кодами OID. Об этом рассказывается в Подразделе 70.2.3.
Так как хеши являются неупорядоченной структурой данных, порядок полей и расположение строк не имеют семантической значимости. Однако для поддержания согласованного представления мы установили несколько правил, которые применяет скрипт форматирования reformat_dat_file.pl :
Переводы строк при необходимости вставляются между полями для ограничения длины строки 80 символами, если это возможно. Перевод строки также вставляется между полями метаданных и обычными полями.
Скрипт reformat_dat_file.pl сохраняет пустые строки и строки комментариев в неизменном виде.
Если вы хотите добавить новый метод уменьшения представления данных, вы должны реализовать его в reformat_dat_file.pl и также научить Catalog::ParseData() разворачивать данные в полное представление.
70.2.2. Назначение OID
Предварительно загружаемым строкам каталога должны заранее назначаться OID, если на них по OID ссылаются другие предварительно загружаемые строки. Назначать OID также требуется, если на OID нужно будет ссылаться из кода на C. В отсутствие этих условий поле метаданных oid можно опустить и тогда загрузочный код назначит OID автоматически либо оставит его нулевым, если OID в данном каталоге не используются. На практике мы обычно явно назначаем OID для всех строк в определённом каталоге (даже если фактически присутствуют ссылки только на часть из них) либо не назначаем их вовсе.
70.2.3. Поиск OID по ссылке
Перекрёстную ссылку из одной строки исходного каталога на другую можно записать, просто указав предопределённый OID целевой строки. Однако этот подход провоцирует ошибки и сложен для понимания, поэтому для часто используемых каталогов в genbki.pl реализованы механизмы записи символических ссылок. В настоящее время по символическим ссылкам можно обращаться к методам доступа, функциям, операторам, классам и семействам операторов, а также типам. При этом действуют следующие правила:
Ни в одном из этих случаев не поддерживается указание схемы; все объекты, создаваемые на стадии начальной загрузки, будут принадлежать схеме pg_catalog.
Скрипт genbki.pl разрешает все символические ссылки при запуске и помещает в формируемый файл BKI обычные числовые OID. Таким образом, при начальной загрузке отпадает необходимость в разрешении имён.
70.2.4. Рецепты по редактированию файлов данных
Ниже приведены некоторые предложения по оптимальному решению некоторых распространённых задач при изменении файлов каталогов.
Указание значения по умолчанию для существующего столбца, который его не имел: Добавьте указание BKI_DEFAULT в заголовочный файл, а затем выполните make reformat-dat-files для удаления ставших избыточными записей поля.
Удаление столбца, со значением по умолчанию или без: Удалите столбец из заголовочного файла, а затем выполните make reformat-dat-files для удаления ставших избыточными записей поля.
Разовая массовая модификация: Скрипт reformat_dat_file.pl можно скорректировать для выполнения самых разных массовых модификаций. Просмотрев в нём блочные комментарии, вы найдёте место, куда можно вставить модифицирующий код. В следующем примере мы произведём объединение двух логических полей в pg_proc в символьном поле:
Добавьте новый столбец со значением по умолчанию в pg_proc.h :
SQL, OIDs Postgres, что они и чем они полезны?
Я смотрю на создание таблиц в PostgreSQL и наткнулся на это:
Я прочитал документацию, предоставленную postgres, и я знаю концепцию идентификатора объекта из ООП, но все же я не понимаю,
OID в основном предоставляют встроенный глобально уникальный идентификатор для каждой строки, содержащейся в системном столбце (в отличие от столбца пользовательского пространства). Это удобно для таблиц, в которых у вас нет первичного ключа, есть повторяющиеся строки и т. Д. Например, если у вас есть таблица с двумя одинаковыми строками и вы хотите удалить самую старую из двух, вы можете сделать это, используя колонка oid.
По моему опыту, эта функция, как правило, не используется в большинстве приложений с постгресовой поддержкой (вероятно, частично потому, что они нестандартны), и их использование по существу не рекомендуется :
В PostgreSQL 8.1 default_with_oids по умолчанию отключено; в предыдущих версиях PostgreSQL он был включен по умолчанию.
Использование OID в пользовательских таблицах считается устаревшим, поэтому большинство установок должны оставлять эту переменную отключенной. Приложения, которым требуются OID для конкретной таблицы, должны указывать WITH OIDS при создании таблицы. Эта переменная может быть включена для совместимости со старыми приложениями, которые не следуют этому поведению.
Тип oid в настоящее время реализован как беззнаковое четырехбайтовое целое число. Следовательно, он недостаточно велик, чтобы обеспечить уникальность всей базы данных в больших базах данных или даже в больших отдельных таблицах. Поэтому использование столбца OID созданной пользователем таблицы в качестве первичного ключа не рекомендуется. OID лучше всего использовать только для ссылок на системные таблицы.
OIDs постепенно сокращаются
Основная команда, ответственная за Postgres, постепенно сворачивает OID.
Postgres 12 удаляет специальное поведение столбцов OID
Использование OID в качестве необязательного системного столбца в ваших таблицах теперь удалено из Postgres 12. Вы больше не можете использовать:
После перехода на Postgres 12 любой необязательный системный столбец oid больше не будет невидимым по умолчанию. Выполнение SELECT * теперь будет включать этот столбец. Обратите внимание, что этот дополнительный «неожиданный» столбец может нарушить наивно написанный код SQL.
Как работать с Postgres в Go: практики, особенности, нюансы
Неожиданное поведение приложения в отношении работы с базой приводит к войне между DBA и разработчиками: DBA кричат: «Ваше приложение роняет базу», разработчики — «Но ведь до этого всё работало!». Хуже всего, что DBA и разработчики не могут помочь друг другу: одни не знают про нюансы работы приложения и драйвера, другие не знают про особенности, связанные с инфраструктурой. Было бы неплохо такой ситуации избежать.
Надо понимать, часто недостаточно полистать go-database-sql.org. Лучше вооружиться чужим опытом. Еще лучше, если это будет опыт, полученный кровью и потерянными деньгами.
Меня зовут Рябинков Артемий и эта статья — вольная интерпретация моего доклада с конференции Saints HighLoad 2019.
Инструменты
Минимально необходимую информацию о том, как в Go работать с любой SQL-подобной базой данных, вы сможете найти на go-database-sql.org. Если еще не читали — прочитайте.
На мой взгляд, сила Go в простоте. И это выражается, например, в том, что в Go принято писать запросы на голом SQL (ORM не в чести). Это и преимущество, и источник дополнительных трудностей.
Использование StructScan позволяет не перекладывать руками данные из столбцов в свойства структуры.
Использование NamedQuery позволяет в качестве плейсхолдеров в запросе использовать свойства структуры.
Использование Get и Select позволяет избавиться от необходимости руками писать циклы, которые достают строки из базы.
Драйверы
database/sql — это набор интерфейсов для работы с базой, а sqlx — их расширение. Чтобы эти интерфейсы работали, для них нужна реализация. Именно за реализацию и отвечают драйверы.
Наиболее популярные драйверы:
github.com/jackc/pgx — именно этот драйвер вы хотите использовать. Почему?
Типично мы пишем вот такой цикл, чтобы получать данные из базы:
Внутри драйвера мы получаем данные, накапливая их в буфер размером 4KB. rows.Next() порождает поход в сеть и наполняет буфер. Если буфера не хватает, то мы идём в сеть за оставшимися данными. Больше походов в сеть – меньше скорость обработки. С другой стороны, так как предел буфера – 4KB, не забьём всю память процесса.
Но, конечно, хочется выкрутить объём буфера на максимум, чтобы уменьшить кол-во запросов в сеть и снизить latency нашего сервиса. Добавляем такую возможность и попробуем выяснить ожидаемое ускорение на синтетических тестах:
Видно, что большой разницы по скорости обработки нет. Почему так?
Оказывается, мы упираемся в размер буфера на отправку данных внутри самого Postgres. Этот буфер имеет жестко заданный размер в 8KB. Используя strace можно увидеть, что ОС возвращает 8192 байта в системном вызове read. А tcpdump это подтверждает размером пакетов.
Tom Lane (один из основных разработчиков ядра Postgres) это комментирует так:
Traditionally, at least, that was the size of pipe buffers in Unix machines, so in principle this is the most optimal chunk size for sending data across a Unix socket.
Andres Freund (разработчик Postgres от EnterpriseDB) считает, что буфер в 8KB не лучший вариант реализации на сегодняшний день и нужно тестировать поведение на других размерах и с другой конфигурацией сокета.
Другая особенность драйвера pgx (v3): на каждую установку соединения он делает запрос в базу для получения информации об Object ID (OID).
Эти идентификаторы были добавлены в Postgres, чтобы уникально идентифицировать внутренние объекты: строки, таблицы, функции и т.д.
Вот момент, когда таких запросов насыпало на одну из наших баз:
15 транзакций в минуту в нормальном режиме, скачок до 6500 транзакций при деградации.
Что делать?
Первое и основное — ограничивайте сверху размер вашего пула.
Для database/sql это можно сделать функцией DB.SetMaxOpenConns. Если откажетесь от интерфейсов database/sql и будете использовать pgx.ConnPool (пул соединений, реализуемый самим драйвером), то в ConnPoolConfig можно указать MaxConnections (по умолчанию 5).
Кстати, при использовании pgx.ConnPool драйвер будет переиспользовать информацию о полученных OIDs и не будет на каждое новое соединение делать запросы к базе.
Если от database/sql отказываться не хочется, то можно кешировать информацию об OIDs самим.
Это рабочий способ, но его использование может быть опасно при двух условиях:
Срабатывание этих условий приводит к тому, что закешированые OIDs становятся невалидными. Но очистить мы их не сможем, так как не знаем момент переключения на новую базу.
В мире Postgres для организации высокой доступности обычно используют физическую репликацию, которая побитово копирует инстансы базы, поэтому проблемы из-за кеширования OIDs редко можно встретить в дикой природе. (Но лучше уточнить у своих DBA, как у вас standby работает).
Логирование и мониторинг
Мониторинг и логирование помогут заметить проблемы раньше, чем упадет база.
database/sql предоставляет метод DB.Stats(). Возвращаемый снепшот состояния даст вам понимание того, что происходит внутри драйвера.
Если используете пул в pgx напрямую, то похожую информацию вам даст метод ConnPool.Stat():
Скорее всего, вам даже не придется самому реализовывать этот интерфейс. В pgx из коробки есть набор адаптеров для наиболее популярных логеров, например uber-go/zap, sirupsen/logrus, rs/zerolog.
Инфраструктура
Почти всегда при работе с Postgres вы будете использовать connection pooler, и это будет PgBouncer (или odyssey – если вы Yandex).
Почему так, можно почитать в отличной статье brandur.org/postgres-connections. Если кратко, то при кол-ве клиентов больше 100 скорость обработки запросов начинает деградировать. Происходит это из-за особенностей реализации самого Postgres: запуск отдельного процесса на каждое соединение, механизм снятия снапшотов и использование общей памяти для взаимодействия — всё это влияет.
Вот benchmark различных реализаций connection pooler’ов:
И benchmark пропускной способности с PgBouncer и без него.
В результате ваша инфраструктура примет такой вид:
PgBouncer умеет работать в трех режимах:
Transaction Pooling + Prepared Statements
Давайте представим, что хотим подготовить запрос и потом его выполнить. В какой-то момент запускаем транзакцию, в которой отправляем запрос на Prepare, и получаем от базы данных идентификатор подготовленного запроса.
После, в любой другой момент времени, порождаем другую транзакцию. В ней обращаемся к базе и хотим по идентификатору выполнить запрос с заданными параметрами.
В режиме Transaction Pooling две транзакции могут быть выполнены в разных соединениях, но Statement ID действителен только в рамках одного соединения. Получаем ошибку prepared statement does not exist при попытке выполнить запрос.
Самое неприятное: так как при разработке и тестировании нагрузка небольшая, PgBouncer часто выдает одно и то же соединение и всё работает корректно. Но как только выкатываем в прод, запросы начинают падать с ошибкой.
А теперь найдите Prepared Statements в таком коде:
Что делать?
Второй вариант — готовить запрос на стороне клиента. Этого не хочется делать по двум причинам:
Еще один вариант — явно оборачивать каждый запрос в транзакцию. Ведь пока транзакция живет, PgBouncer не забирает соединение. Это работает, но, кроме многословности в нашем коде, мы вдобавок получаем большее количество сетевых вызовов: Begin, Prepare, Execute, Commit. Итого 4 сетевых вызова на один запрос. Latency растёт.
Но хочется и безопасно, и удобно, и эффективно. И такой вариант есть! Можно явно указать драйверу, что хочешь использовать режим Simple Query. В этом режиме не будет подготовки и весь запрос пройдёт в одном сетевом вызове. При этом драйвер сам сделает экранирование каждого из параметров ( standard_conforming_strings должен быть активирован на уровне базы или при установке соединения).
Отмена запросов
Следующие проблемы связаны с отменой запросов на стороне приложения.
Взгляните на этот код. Где тут подводные камни?
В языке Go есть метод контроля потока выполнения программы – context.Context. В этом коде мы передаём ctx драйверу, чтобы при закрытии контекста драйвер отменил запрос на уровне базы данных.
Отложенная отмена
Чтобы отменить запрос, нам нужно создать новое соединение с базой и запросить отмену. На каждое соединение Postgres создаёт отдельный процесс. Мы отправляем команду на отмену текущего запроса в конкретном процессе. Для этого создаём новое соединение и в нём передаём ID процесса (PID), интересующего нас. Но пока команда на отмену летит до базы, отменяемый запрос может завершиться самостоятельно.
Чеклист по работе с Postgres
Вместо выводов решил сделать чеклист по работе с Postgres. Это должно помочь статье уложиться в голове.
Что такое oid столбец
В каждой таблице есть несколько системных столбцов, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских столбцов. (Заметьте, что это не зависит от того, является ли имя ключевым словом или нет; заключение имени в кавычки не поможет избежать этого ограничения.) Эти столбцы не должны вас беспокоить, вам лишь достаточно знать об их существовании.
Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.) cmin
Номер команды (начиная с нуля) внутри транзакции, добавившей строку. xmax
Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено. cmax
Номер команды в удаляющей транзакции или ноль. ctid
Коды OID представляют собой 32-битные значения и выбираются из единого для всей СУБД счётчика. В больших или долгоживущих базах данных этот счётчик может пойти по кругу. Таким образом, не рекомендуется рассчитывать на уникальность OID, если только вы не обеспечите её дополнительно. Если вам нужно идентифицировать строки таблицы, настоятельно рекомендуется использовать последовательности. Однако можно использовать и коды OID, при выполнении следующих условий:
Когда для идентификации строк таблиц применяется OID, в каждой такой таблице должно создаваться ограничение уникальности для столбца OID. Когда такое ограничение уникальности (или уникальный индекс) существует, система позаботится о том, чтобы OID новой строки не совпал с уже существующими. (Конечно, это возможно, только если в таблице меньше 2 32 (4 миллиардов) строк, а на практике таблицы должны быть гораздо меньше, иначе может пострадать производительность системы.)
Никогда не следует рассчитывать, что OID будут уникальны среди всех таблиц; в качестве глобального идентификатора в рамках базы данных используйте комбинацию tableoid и OID строки.
Идентификаторы транзакций также являются 32-битными. В долгоживущей базе данных они могут пойти по кругу. Это не критично при правильном обслуживании БД; подробнее об этом см. Главу 24. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.