Ctid postgresql что это
В каждой таблице есть несколько системных столбцов, неявно определённых системой. Как следствие, их имена нельзя использовать в качестве имён пользовательских столбцов. (Заметьте, что это не зависит от того, является ли имя ключевым словом или нет; заключение имени в кавычки не поможет избежать этого ограничения.) Эти столбцы не должны вас беспокоить, вам лишь достаточно знать об их существовании.
Идентификатор (код) транзакции, добавившей строку этой версии. (Версия строки — это её индивидуальное состояние; при каждом изменении создаётся новая версия одной и той же логической строки.) cmin
Номер команды (начиная с нуля) внутри транзакции, добавившей строку. xmax
Идентификатор транзакции, удалившей строку, или 0 для неудалённой версии строки. Значение этого столбца может быть ненулевым и для видимой версии строки. Это обычно означает, что удаляющая транзакция ещё не была зафиксирована, или удаление было отменено. cmax
Номер команды в удаляющей транзакции или ноль. ctid
Коды OID представляют собой 32-битные значения и выбираются из единого для всей СУБД счётчика. В больших или долгоживущих базах данных этот счётчик может пойти по кругу. Таким образом, не рекомендуется рассчитывать на уникальность OID, если только вы не обеспечите её дополнительно. Если вам нужно идентифицировать строки таблицы, настоятельно рекомендуется использовать последовательности. Однако можно использовать и коды OID, при выполнении следующих условий:
Когда для идентификации строк таблиц применяется OID, в каждой такой таблице должно создаваться ограничение уникальности для столбца OID. Когда такое ограничение уникальности (или уникальный индекс) существует, система позаботится о том, чтобы OID новой строки не совпал с уже существующими. (Конечно, это возможно, только если в таблице меньше 2 32 (4 миллиардов) строк, а на практике таблицы должны быть гораздо меньше, иначе может пострадать производительность системы.)
Никогда не следует рассчитывать, что OID будут уникальны среди всех таблиц; в качестве глобального идентификатора в рамках базы данных используйте комбинацию tableoid и OID строки.
Идентификаторы транзакций также являются 32-битными. В долгоживущей базе данных они могут пойти по кругу. Это не критично при правильном обслуживании БД; подробнее об этом см. Главу 23. Однако полагаться на уникальность кодов транзакций в течение длительного времени (при более чем миллиарде транзакций) не следует.
8.18. Идентификаторы объектов
Идентификатор объекта (Object Identifier, OID) используется внутри PostgreSQL в качестве первичного ключа различных системных таблиц. В пользовательские таблицы колонка OID добавляется, только если при создании таблицы указывается WITH OIDS или включён параметр конфигурации default_with_oids. Идентификатор объекта представляется в типе oid. Также для типа oid определены следующие псевдонимы: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig и regdictionary. Обзор этих типов приведён в Таблице 8-24.
В настоящее время тип oid реализован как четырёхбайтное целое. Таким образом оно может быть недостаточно большим для обеспечения уникальности в базе данных или даже в отдельных больших таблицах. Поэтому в пользовательских таблицах использовать колонку типа OID в качестве первичного ключа не рекомендуется. Лучше всего ограничить применение этого типа обращениями к системным таблицами.
Для самого типа oid помимо сравнения определены всего несколько операторов. Однако его можно привести к целому и затем задействовать в обычных целочисленных вычислениях. (При этом следует опасаться путаницы со знаковыми/беззнаковыми значениями.)
Типы-псевдонимы OID сами по себе не вводят новых операций и отличаются только специализированными функциями ввода/вывода. Эти функции могут принимать и выводить не просто числовые значения, как тип oid, а символические имена системных объектов. Эти типы позволяют упростить поиск объектов по значениям OID. Например, чтобы выбрать из pg_attribute строки, относящиеся к таблице mytable, можно написать:
Хотя второй вариант выглядит не таким уж плохим, но это лишь очень простой запрос. Если же потребуется выбрать правильный OID, когда таблица mytable есть в нескольких схемах, вложенный подзапрос будет гораздо сложнее. Преобразователь вводимого значения типа regclass находит таблицу согласно заданному пути поиска схем, так что он делает «всё правильно» автоматически. Аналогично, приведя идентификатор таблицы к типу regclass, можно получить символическое представление числового кода.
Все типы псевдонимов OID принимают имена, дополненные именем схемы, и выводят имена со схемой, если данный объект нельзя будет найти в текущем пути поиска без имени схемы. Типы regproc и regoper принимают только уникальные вводимые имена (не перегруженные), что ограничивать их применимость; в большинстве случаев лучше использовать regprocedure или regoperator. Для типа regoperator в записи унарного оператора неиспользуемый операнд заменяется словом NONE.
Дополнительным свойством типов псевдонимов OID является образование зависимостей. Когда в сохранённом выражении фигурирует константа одного из этих типов (например, в представлении или в значении колонки по умолчанию), это создаёт зависимость от целевого объекта. Например, если значение по умолчанию определяется выражением nextval(‘my_seq’::regclass), PostgreSQL понимает, что это выражение зависит от последовательности my_seq, и не позволит удалить последовательность раньше, чем будет удалено это выражение.
Третий тип идентификаторов, используемых в системе, — cid, идентификатор команды (command identifier). Этот тип данных имеют системные колонки cmin и cmax. Идентификаторы команд — это тоже 32-битные числа.
И наконец, последний тип системных идентификаторов — tid, идентификатор строки/кортежа (tuple identifier). Этот тип данных имеет системная колонка ctid. Идентификатор кортежа представляет собой пару (из номера блока и индекса кортежа в блоке), идентифицирующую физическое расположение строки в таблице.
(Подробнее о системных колонках рассказывается в Разделе 5.4.)
FAQ/ru
Перевод на русский язык выполнил Виктор Вислобоков (corochoone@gmail.com) 03.12.2010 ([1])
Contents
Переводы на другие языки
Вопросы специфичные для платформы
Пользователи Windows также должны прочитать platform FAQ for Windows. А также FAQs for other platforms.
Общие вопросы
Что такое PostgreSQL? Как произносится это название? Что такое Postgres?
PostgreSQL произносится Post-Gres-Q-L (Пост-Грес-Кью-Эл). (Для особо любопытствующих как произносить «PostgreSQL», существует аудиофайл audio file.)
Разработку PostgreSQL выполняет команда разработчиков, разбросанная по всему миру и связанная через Интернет. Разработка является общественным проектом и не управляется какой-либо компанией. Подробности смотрите в FAQ для разработчиков, FAQ для разработчиков.
Postgres — это широко используемое сокращение для PostgreSQL. Первоначальным именем проекта в Беркли было Postgres и этот ник теперь наиболее популярен в разговорах о PostgreSQL по сравнению с другими. Если вам трудно полностью проговаривать ‘PostgreSQL’, можно просто говорить ‘Postgres’.
Кто управляет PostgreSQL?
Что представляет из себя Всемирная Группа Разработчиков PostgreSQL (PGDG)?
Что представляет из себя PostgreSQL Core Team?
Это комитет, состоящий из 5-7 членов (в настоящий момент 6), которые внесли наибольший вклад в PostgreSQL и которые: (а) устанавливают даты выпусков, (b) управляют конфиденциальными вопросами проекта, (c) общаются как спикеры с PGDG, когда это требуется и (d) выступают арбитрами в тех вопросах, по которым в сообществе не достигнуто консенсуса. В настоящий момент Основная Команда представлена вверху страницы списка участников
Что можно сказать о различных фондах PostgreSQL?
Хотя проект PostgreSQL использует некоммерческие фирмы в США, Европе, Бразилии и Японии для координации проектом и сбора средств, эти фирмы не являются владельцами кода PostgreSQL.
Какова лицензия на PostgreSQL?
PostgreSQL распространяется по лицензии сходной с BSD и MIT. В своей основе она позволяет пользователям делать с кодом всё что угодно, включая перепродажу скомпилированных файлов без исходного кода. Единственное ограничение состоит в том, что вы не можете возложить на нас юридическую ответственность за проблемы с этим программным обеспечением. Также существует требование о том, что все копии данного программного обеспечения должны включать в себя данные сведения об авторских правах. Вот лицензия, которую мы используем:
На каких платформах работает PostgreSQL?
Обычно, PostgreSQL может работать на любой современной платформе совместимой с Unix.
Платформы, которые прошли явное тестирование перечислены на сайте Build farm. Документацию, которая содержит множество подробностей о поддерживаемых платформах можно найти на http://www.postgresql.org/docs/current/static/supported-platforms.html.
PostgreSQL также работает на операционных системах Microsoft Windows, основанных на NT, таких как Win2000 SP4, WinXP и Win2003. Пакет инсталлятора доступен по адресу http://www.postgresql.org/download/windows. Версии Windows, основанные на MS-DOS (Win95, Win98, WinMe) могут запускать PostgreSQL с помощью Cygwin.
Где можно взять PostgreSQL?
Дистрибутивы в скомпилированном виде для разных операционных систем и платформ; см. страницу скачивания.
Исходный код можно получить через веб-браузер или по ftp.
Какая на данный момент наиболее свежая версия?
Последнюю версию PostgreSQL можно узнать перейдя на главную страницу нашего сайта.
Обычно мы выпускаем новые старшие версии каждый год, а младшие версии каждые несколько месяцев. Младшие версии обычно выпускаются в одно и то же время для всех поддерживаемых старших версий. Больше информации о старших и младших версиях можно найти на http://www.postgresql.org/support/versioning.
Где получить поддержку?
Сообщество PostgreSQL предоставляет помощь множеству пользователей через E-mail. Основной web-сайт для подписки на списки рассылки по E-mail это: http://www.postgresql.org/community/lists/. Хорошим местом для того, чтобы начать задавать вопросы являются списки general (общие вопросы) или bugs (ошибки). Для наилучших результатов, прочитайте guide to reporting problems перед тем как отправлять сообщение об ошибке, чтобы убедиться, что вы включили всю необходимую информацию для того, чтобы вам помогли.
Главным IRC каналом является #postgreql, расположенный на сервере Libera (irc.libera.chat). На этом же сервере существуют каналы на испанском (#postgresql-es), французском (#postgresqlfr) и бразильском (#postgresql-br) языках. Также существует канал по PostgreSQL на сервере EFNet.
Как мне сообщить об ошибке?
Посетите страничку со специальной формой отчёта об ошибке в PostgreSQL по адресу: http://www.postgresql.org/support/submitbug, чтобы отправить сообщение об ошибке в список рассылки pgsql-bugs. Также проверьте наличие более свежей версии PostgreSQL на нашем FTP сайте ftp://ftp.postgresql.org/pub/.
Чтобы получить полезный и информативный ответ важно, чтобы вы прочитали guide to reporting problems, чтобы убедиться, что вы включили в ваше сообщение всю информацию, которая требуется для полного понимания и работы над вашим сообщением.
На ошибки, уведомления о которых были сделаны через специальную форму или отправленные в какой-либо список рассылки PostgreSQL, обычно генерируется один из следующих ответов:
Как найти информацию об известных ошибках или отсутствующих возможностях?
PostgreSQL поддерживает расширенный подкласс SQL:2008. См. список TODO на предмет известных ошибок, отсутствующих возможностей и будущих планов.
На запрос какой-либо возможности обычно приходят следующие ответы:
Ошибка, с которой я столкнулся исправлена в новой старшей версии PostgreSQL, но я не могу обновляться. Могу ли я получить патч, чтобы решить проблему?
Нет. Никто не будет делать специальный патч для вас, извлекая исправление, например из 8.4.3, чтобы применить его к 8.4.1. Это потому, что никогда не должно быть необходимости делать это.
PostgreSQL имеет строгую политику, по которой исправление ошибок осуществляется только в текущих выпусках, согласно политике версий. Это позволяет безопасно обновляться, например, с 8.4.1 до 8.4.3.
Поддерживается совместимость на бинарном (двоичном) уровне, не требуется выполнять дамп и восстановление, ничего не будет разрушено, но ошибки, которые могут вызывать проблемы, исправляются. Даже, если вы никогда не сталкивались с какой-то конкретной ошибкой, это может произойти в будущем и таким образом имеет смысл оперативно обновляться. Для этого просто нужно установить обновление и перезапустить сервер баз данных, ничего более.
Обновление с 8.3 до 8.4 или с 8.4 до 9.0 является обновлением старшей версии и не может производиться также. Однако, если ошибка найдена в 9.0, то она обычно исправляется во всех обслуживаемых старых версиях, т.е. в 8.4 и 8.3, если это безопасно и имеет практическую пользу.
Таким образом, если у вас работает версия 8.1.0, то выполнение обновления до 8.1.21 сильно рекомендуется и является безопасным. С другой стороны, обновление на следующую старшую версию, т.е. 8.2.x, может потребовать изменений в вашем приложении и потребует выполнения дампа, восстановления и перезагрузки.
Если вы хотите быть осторожным при любых обновлениях, вам необходимо прочитать замечания к выпуску для каждого выпуска между текущим и последней младшей версией. Если у вас просто параноидальная боязнь обновлений, вы можете получить исходный код для каждого списка изменений, внесённых в выпуски из PostgreSQL’s репозитория git и опробовать его.
Очень рекомендуется, чтобы вы всегда обновляли последнюю младшую версию. Избегайте попыток извлечь и применить отдельные исправления из выпусков; делая такое, вы обходите все проверки качества, выполненные командой PostgreSQL при подготовке выпуска и создаёте собственную специальную версию, которую не использует никто кроме вас. Намного безопасней обновиться до последнего, оттестированного, безопасного выпуска. Применение патчей к вашей собственной, нестандартной сборке потребует также множество времени и усилий и будет требовать того же времени простоя как и при обычном обновлении.
У меня есть программа, которая хочет версию PostgreSQL x.y.1. Могу я использовать вместо этого версию PostgreSQL x.y.2?
Любая программа, которая работает с конкретной версией, такой как 8.4.1, должна работать с любыми другими младшими версиям той же старшей версии. Это означает, что если программа хочет версию 8.4.1, то она может и должна работать вместо этого с последней версией из выпусков 8.4.
Подробности смотрите в предыдущем вопросе.
Какая документация имеется в наличии?
PostgreSQL содержит много документации, включая большое руководство, страницы электронного руководства man и некоторые маленькие тестовые примеры. Смотрите в каталог /doc. Вы также можете просматривать документацию в Интернет по адресу http://www.postgresql.org/docs.
Коллекцию технических статей по PostgreSQL вы можете найти в wiki.
Как мне научиться SQL?
Во-первых, возьмите одну из книг по PostgreSQL, о которых говорилось выше. Многим из наших пользователей также нравится книга The Practical SQL Handbook, Bowman, Judith S., et al., Addison-Wesley. Другим нравится The Complete Reference SQL, Groff et al., McGraw-Hill.
Многие люди рассматривают документацию по PostgreSQL как прекрасное руководство для обучения самому SQL, а также его реализации для PostgreSQL. Для достижения лучших результатов, используйте PostgreSQL вместе с другой полнофункциональной SQL СУБД, которую вы знаете, таким образом вы научитесь использовать SQL без специфичных для PostgreSQL особенностей. Документация по PostgreSQL обычно рассказывает когда та или иная возможность PostgreSQL расширяет стандарт.
Существует также множество прекрасных учебников доступных в online:
Как мне прислать исправление или присоединиться к команде разработчиков?
Как сравнить PostgreSQL с другими СУБД?
Существует несколько методов сравнения программного обеспечения: возможности, производительность, надежность, поддержка и цена.
Возможности
PostgreSQL имеет большинство возможностей представленных в больших коммерческих СУБД, такие как: транзакции, подзапросы, триггеры, представления, внешние ключи, ограничения целостности и разные блокировки. У нас есть некоторые возможности, которых нет у них: типы, определяемые пользователем, механизм наследования, правила и конкурентное многоверсионное управление для работы с содержимым блокировок.
Производительность
Производительность PostgreSQL сходна с другими коммерческими СУБД и с СУБД с открытым исходным кодом. В каких-то вещах мы быстрее, в каких-то медленнее. Наша производительности обычно +/-10% по сравнению с другими СУБД.
Надёжность
Мы понимали, что наша СУБД должна быть надежной или она ничего не будет стоить. Мы стараемся выпускать хорошо проверенный, стабильный код, который содержит минимум ошибок. Каждый выпуск проходит стадию бета-тестирования и наша история выпусков показывает, что мы можем предоставлять стабильные, монолитные выпуски, которые готовы к продуктивному использованию. Мы верим, что мы производим проверку не хуже, чем у других СУБД в данной области.
Поддержка
Наши списки рассылки предоставляют возможность общения с большой группой разработчиков и пользователей, которые могут помочь решить любые возникшие проблемы. В то же время, мы не гарантируем какие-либо исправления, но и разработчики коммерческих СУБД не всегда делают исправления. Прямой доступ к разработчикам, сообществу пользователей, руководствам и исходным текстам часто делают поддержку PostgreSQL превосходящей другие СУБД. Существует коммерческая поддержка по результатам возникших инцидентов, которая доступна для тех кому она нужна. (См. вопрос 3.10).
Наш продукт бесплатен как для коммерческого, так и для не коммерческого использования. Вы можете добавлять свой код в наш продукт без ограничений, за исключением тех, что описываются в нашей лицензии стиля BSD, которая приведена выше.
Может ли PostgreSQL быть встраиваемой СУБД?
PostgreSQL разрабатывается по архитектуре клиент/сервер, которая требует отдельных процессов для каждого клиента и сервера, а также несколько вспомогательных процессов. Многие встраиваемые архитектуры могут соответствовать таким требованиям. Однако, если ваша встраиваемая архитектура требует сервер баз данных для запуска внутри прикладного процесса, вы не можете использовать Postgres и вам лучше бы выбрать для базы данных какое-либо другое облегченное решение.
Популярные встраиваемые решения включают SQLite и Firebird SQL.
Как мне отписаться от списков рассылки PostgreSQL? Как избежать получения дублирующихся сообщений?
Страница PostgreSQL Majordomo позволяет подписаться или отписаться от любых списков рассылки. (Вам может понадобиться ваш пароль в Majordomo для авторизации).
Все списки рассылки PostgreSQL настраиваются как группа, отвечающая в список и первоначальному автору E-mail сообщения. Это сделано для того, чтобы пользователи получали ответы как можно быстрее. Если вы предпочитаете не получать дублирующихся сообщений из списка в случае если вы уже получили E-mail сообщение напрямую, установите флажок eliminatecc на странице настроек Majordomo. Вы также можете предотвратить получение копий вашего же сообщения в список самому себе, сняв флажок selfcopy.
Вопросы пользователей по клиентской части
Какие интерфейсы есть для PostgreSQL?
Ядро исходных кодов PostgreSQL включает только C и встроенные (embedded) в C интерфейсы. Все другие интерфейсы являются независимыми проектами и загружаются отдельно; самостоятельность проектов позволяет им организовать собственное расписание выпусков новых версий и иметь собственную команду разработчиков.
Некоторые языки программирования, такие как PHP включают в себя интерфейс к PostgreSQL. Интерфейсы для таких языков как Perl, TCL, Python и многих других, доступны на http://pgfoundry.org.
Какие инструменты существуют для использования PostgreSQL через Web?
Прекрасное введение во взаимодействие баз данных и Web можно найти на: http://www.webreview.com
Для интеграции с Web, PHP (http://www.php.net) является неплохим интерфейсом.
В сложных случаях многие пользуются Perl и DBD::Pg с CGI.pm или mod_perl.
Есть ли у PostgreSQL графический интерфейс пользователя?
Для PostgreSQL существует большое количество инструментов с графическим интерфейсом как коммерческих, так и открытых. Подробности можно найти в Community Guide to PostgreSQL GUI Tools.
Вопросы администрирования
Как мне установить PostgreSQL в место отличное от /usr/local/pgsql?
Я установил PostgreSQL и не знаю пароль пользователя postgres
Дэйв Пейдж написал blog post, рассказывающий, для чего используются разные типы паролей и как решить некоторые проблемы, такие как сброс этих паролей.
Как мне управлять соединениями от других компьютеров?
Как мне настроить СУБД для получения лучшей производительности?
Существует три главных области, которые потенциально могут увеличить производительность:
Изменение запроса
Это означает модификацию запросов для получения лучшей производительности:
Настройка сервера
Некоторые установки в postgresql.conf влияют на производительность. Подробный полный список установок см. в Administration Guide/Server Run-time Environment/Run-time Configuration.
Какие возможности для отладки есть в наличии?
Есть множество установок в настройках сервера, начинающихся на log_* на http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html, позволяющих протоколировать запросы и статистику работы процесса, которая очень полезна для отладки и измерения производительности.
Почему я получаю сообщение «Sorry, too many clients» когда пытаюсь подключиться к базе?
Вы достигли установленного по умолчанию ограничения в 100 сессий подключения к базе данных. Вам необходимо увеличить лимит на количество конкурентных backend процессов для вашего сервера БД, изменив значение max_connections в файле postgresql.conf и перестартовать сервер БД.
Как выполнить обновление PostgreSQL?
Будет ли PostgreSQL работать с последними изменениями в разных странах, касающимися дневного времени?
PostgreSQL, начиная с версии 8.0, зависит от базы данных часовых поясов tzdata (которая также называется базой данных zoneinfo Olson timezone database) в том, что касается информации о зимнем/летнем времени. Чтобы PostgreSQL работала с летним/зимнем временем, установите набор файлов tzdata и перезапустите сервер.
Все обновления выпусков PostgreSQL, включая последние доступные файлы tzdata, также обновляются в младших версиях для каждой старшей версии, чего обычно вполне достаточно.
На платформах, где производятся регулярные обновления программного обеспечения, включая файлы tzdata, возможно более правильным будет использование системных файлов tzdata. Это возможно с помощью указания соответствующей опции при компиляции. Большинство дистрибутивов Linux устанавливают эту опцию для сборки своих пакетов PostgreSQL.
Выпуски PostgreSQL до 8.0 всегда использовали информацию о часовых поясах, получаемую из операционной системы.
Какое компьютерное «железо» я должен использовать?
Поскольку «железо» персональных компьютеров является наиболее совместимым, люди склонны верить, что такое «железо» имеет одинаковое качество. Это не так. Память ECC, SCSI и качественные материнские платы являются более надёжными и имеют более лучшую производительность, чем менее дорогое «железо». PostgreSQL будет работать на любом «железе», но если для вас важны надёжность и производительность, то с вашей стороны будет мудро поставить соответствующее «железо».
Серверы баз данных, в отличие от многих других приложений, обычно упираются в производительность подсистемы ввода/вывода и память, так что вам нужно в первую очередь уделить внимание подсистеме ввода/вывода, а затем ёмкости памяти и, наконец, производительности процессора. Например, дисковый контроллер с кэшем, который работает на резервной батарейке, часто наиболее лёгкий и правильный способ увеличить производительность сервера базы данных. Обсудить разное «железо» можно в наших списках рассылки.
Как PostgreSQL использует ресурсы процессора?
Сервер PostgreSQL основан на процессах (не тредах(нитях)) и использует один процесс операционной системы на одну сессию с базой данных. Одиночная сессия с базой данных (соединение) не может использовать более чем один процессор. Разумеется, несколько соединений автоматически распределяются по всем доступным процессорам, которые доступны операционной системе. Клиентские приложения могут легко использовать треды(нити) и создавать несколько соединений к базе данных из каждого треда.
Один сложный и независящий от процессора запрос не может использовать более чем один процессор при обработке данного запроса. Система может оставаться доступной для использования других процессоров, дискового ввода/вывода и т.д., но вы не увидите значительного преимущества при использовании более чем одного ядра.
Почему у PostgreSQL так много процессов, даже когда СУБД простаивает?
Как отмечено в ответе выше, PostgreSQL основан на процессах, так что он запускает один процесс postgres (или postgres.exe на Windows) на соединение. Процесс postmaster (который принимает соединения и запускает для них новые процессы postgres) запущен всегда. В дополнение, PostgreSQL обычно имеет один или более «вспомогательных» процессов, таких как сборщики статистики, фоновой записи, демон автовакуума, walsender, и т.д., и все они отображаются как экземпляры «postgres» в большинстве инструментов системного мониторинга.
Несмотря на их количество, процессы фактически используют очень мало реальных ресурсов. См следующий вопрос.
Почему PostgreSQL использует так много памяти?
Несмотря на видимость это абсолютно нормально и на самом деле PostgreSQL используется не так много памяти как показывают инструменты типа top или монитора процессов в Windows.
Такие инструменты как top и монитор процессов Windows могут показывать множество экземпляров postgres (см. выше), каждый из которых использует огромное количество памяти. Часто, если просуммировать всю эту память, окажется что её больше, чем фактически установлено всего памяти на компьютере!
Вопросы эксплуатации
Как выполнить SELECT только для нескольких первых строчек запроса? Произвольной строки?
Для получения только нескольких строк, если вы знаете их количество на момент выполнения SELECT, используйте LIMIT. Если есть какой-либо индекс, который совпадает с ORDER BY, то возможно, что весь запрос выполнен и не будет. Если вы не знаете количества необходимых строк на момент выполнения SELECT, используйте курсор и FETCH.
Для выбора случайной строки с помощью SELECT используйте:
Подробности на эту тему см. также в blog entry by Andrew Gierth.
Как мне найти какие таблицы, индексы, базы данных и пользователи существуют? Как мне увидеть запросы, которые использует psql для получения этой информации?
Также существуют системные таблицы, начинающиеся с pg_, в которых есть эта же информация.
Также посмотрите файл pgsql/src/tutorial/syscat.source. Он показывает многие из операторов SELECT необходимых для получения информации из системных таблиц базы данных.
Как изменить тип данных колонки?
В 8.0 и более поздних версиях, изменение типа колонки выполняется очень легко через ALTER TABLE ALTER COLUMN TYPE.
В более ранних версиях сделайте так:
Чтобы освободить дисковое пространство, использованное устаревшими строками, вы можете затем захотеть воспользоваться командой VACUUM FULL.
Каковы максимальные размеры для строк в таблице, таблиц и базы данных?
Существуют следующие ограничения:
Разумеется, понятие «неограничено» на самом деле ограничивается доступным дисковым пространством и размерами памяти/своппинга. Когда значения, перечисленные выше, неоправданно большие, может пострадать производительность.
Максимальный размер таблицы в 32 TB не требует, чтобы операционная система поддерживала файлы больших размеров. Большие таблицы хранятся как множество файлов размером в 1 GB, так что ограничения, которые накладывает файловая система, не важны.
Максимальный размер таблицы и максимальное количество колонок могут быть увеличены в четыре раза, если размер блока по умолчанию будет увеличен до 32k. Максимальный размер таблицы также может быть увеличен при использовании разбиения таблиц.
Существует ограничение, по которому индексы не могут создаваться для колонок длиннее чем 2,000 символов. К счастью такие индексы вряд ли действительно кому-то нужны. Уникальность гарантируется наилучшим образом с помощью функционального индекса из хэша MD5 длинной колонки, а полнотекстовое индексирование позволяет искать слова внутри колонки.
Как много дискового пространства в базе данных нужно для сохранения данных из обычного текстового файла?
СУБД PostgreSQL может потребоваться дискового пространства до 5 раз больше для сохранения данных из простого текстового файла.
В качестве примера, рассмотрим файл в 100,000 строк, в каждой из которых целое число и текстовое описание. При этом длина текста, в среднем, составляет 20 байт. Размер простого файла составит 2.8 MB. Размер базы PostgreSQL, содержащей эти же данные составит приблизительно 5.2 MB из которых:
Размер страницы данных в PostgreSQL составляет 8192 байт (8 KB), так что:
Индексов не требуются так много, но, поскольку они создаются для большого количества данных, они также могут быть велики.
Значения NULL хранятся как битовые карты и поэтому они занимают очень мало места.
Обратите внимание, что значения типа long могут быть прозрачно сжаты.
Почему мои запросы работают медленно? Почему они не используют мои индексы?
Индексы не используются для каждого запроса. Они используются только если таблица больше минимального размера и запрос выбирает только маленький процент строк в таблице. Так устроено, потому что доступ к диску с применением рандомизации при сканировании индексов может быть медленнее, чем простое чтение таблицы или ее последовательное сканирование.
Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполняться сбор статистики.
Обычно индексы не используются для ORDER BY или для выполнения соединений таблиц. Последовательный перебор, следующий за явной сортировкой, обычно быстрее, чем поиск по индексам в большой таблице. Однако, ORDER BY часто комбинируется с LIMIT и в этом случае индекс будет использоваться, поскольку при выполнении будет возвращаться небольшая часть таблицы.
Если вам кажется, что оптимизатор некорректно выбирает последовательный перебор, используйте SET enable_seqscan TO ‘off’ и запустите запрос снова, чтобы увидеть, действительно ли сканирование индексов быстрее.
Когда используются операции с шаблонами, например LIKE или
, индексы могут быть использованы в следующих случаях:
* не использует индексы. Вместо него используйте индексы выражений, которые описываются в
Также возможно использовать полнотекстовое индексирование для поиска слов.
Статья SlowQueryQuestions содержит несколько больше советов и примеров.
Как посмотреть на то, как оптимизатор выполняет мой запрос?
Это выполняется с помощью команды EXPLAIN. См. страницу, посвященную Using EXPLAIN.
Как мне изменить порядок сортировки текстовых данных?
PostgreSQL сортирует текстовые данные в соответствии с порядком, который определяется текущей локалью, которая была выбрана при initdb. (В версиях, начиная с 8.4, возможно выбрать другую локаль при создании новой базы данных.) Если вам не нравится порядок сортировки, вам необходимо использовать другую локаль. В частности, большинство локалей, отличных от «C» сортируют по алфавиту, что игнорирует пунктуацию и пробелы. Если это не то, что вы хотите, то вам нужна локаль «C».
Как мне выполнить поиск по регулярному выражению и регистро-независимый поиск по регулярному выражению? Как мне использовать индекс для регистро-независимого поиска?
производит поиск по регулярному выражению, а оператор
* производит регистро-независимый поиск по регулярному выражению. Регистро-независимый вариант LIKE называется ILIKE.
Регистро-независимое сравнение обычно выглядит так:
Эта конструкция не будет использовать стандартный индекс на «col». Однако, если вы создадите функциональный индекс «lower(col)», он будет использован:
Если вышеуказанный индекс создаётся как UNIQUE, то колонка, для которой он создаётся может хранить символы и в верхнем, и в нижнем регистре, но индекс не может иметь идентичных значений, которые отличаются только регистром. Чтобы в колонке можно было хранить символы только в определённом регистре, используйте ограничение CHECK или проверку через триггер.
В PostgreSQL, начиная с версии 8.4, вы также можете использовать дополнительный тип данных CITEXT, который внутри реализует вызовы «lower()», так что вы можете фактически считать его полностью регистро-независимым типом данных. CITEXT также доступен для 8.3, его более ранняя версия работала только с ASCII символами и для 8.2 и более ранних версий доступна на pgFoundry.
Как мне определить, что значение поля в каком-либо запросе равно NULL? Как мне соединить возможные NULL? Могу я сортировать поля NULL или нет?
Вы можете проверять значение с помощью IS NULL или IS NOT NULL, как здесь:
Конкатенация NULL с чем-либо другим даёт другой NULL. Если это не то, что бы вам хотелось, используйте COALESCE() для замены NULL как здесь:
Чтобы отсортировать данные по значению используйте IS NULL или IS NOT NULL в выражении ORDER BY. Когда они будут генерировать значения истина, то при сортировке они будут выше, чем значения ложь, так что записи с NULL будут в отсортированном списке сверху:
В PostgreSQL, начиная с версии 8.3, вы также можете управлять порядком сортировки значений NULL, используя недавно стандартизированные модификаторы NULLS FIRST/NULLS LAST, как здесь:
Каковы отличия между разными символьными типами?
| Тип | Внутреннее имя | Замечания |
|---|---|---|
| VARCHAR(n) | varchar | размер задает максимальную длину, нет заполнения |
| CHAR(n) | bpchar | заполняется пустотой до фиксированной длины |
| TEXT | text | нет задаваемого верхнего ограничения или длины |
| BYTEA | bytea | массив байт переменной длины (можно использовать null-байт без опаски) |
| «char» (with the quotes) | char | один символ |
Внутреннее имя вы можете увидеть, когда смотрите системные каталоги и в некоторых сообщениях об ошибках.
Первые четыре типа являются «varlena» типами (т.е. первые четыре байта на диске являются длинной, за которой следуют данные). Таким образом, фактически используемое пространство больше, чем обозначенный размер. Однако, длинные значения также сжимаются, так что занимаемое дисковое пространство может также быть и меньше, чем ожидалось.
Тип «char» (кавычки требуются, чтобы отличать его от CHAR(n)) является специализированным типом данных, который может хранить точно один байт. Он есть в системных каталогах, но его использование в таблицах пользователей обычно не рекомендуется.
Как мне создать серийное поле/поле с авто увеличением?
PostgreSQL поддерживает тип данных SERIAL. Фактически он не является реальным типом данных. При его использовании создаётся колонка целого типа и связанная с этой колонкой последовательность.
автоматически транслируется в:
Также существует тип BIGSERIAL, который похож на SERIAL, за исключением того, что колонка создаётся с типом BIGINT, а не INTEGER. Используйте этот тип, если вы считаете, что вам будет нужно более чем 2 миллиарда серийных значений в таблице.
Как мне получить значение при вставке SERIAL?
Простейший способ получить назначенное значение SERIAL это использовать RETURNING. Используя в качестве примера таблицу из прошлого вопроса, это будет выглядеть так:
Вы также можете вызвать функцию nextval() и использовать полученное значение в INSERT, или вызвать функцию currval() после INSERT.
Не может ли получиться так, что использование currval() и nextval() приведет к зациклированию с другими пользователями?
Почему числа из моей последовательности не используются снова при отмене транзакции? Почему создаются разрывы при нумерации в колонке, где я использую последовательность/SERIAL?
Для реализации конкурентности, значения последовательностей при необходимости выдаются во время запуска транзакций и не блокируются до полного выполнения транзакций. Это может вызывать разрывы в нумерации при отмене транзакций.
Что такое OID?
Для уникального значения в строках таблицы пользователя, лучшим способом является создание уникального индекса на колонку OID (но обратите внимание, что выражение WITH OIDS само по себе не создаёт такой индекс).
Система проверяет индекс, чтобы увидеть не существует уже сгенерированный новый OID и если это так, генерирует новый OID и повторяет проверку. Это работает хорошо, пока таблица не содержащая OID имеет только маленькую часть от возможных 4-х миллиардов строк.
PostgreSQL использует OID для идентификаторов объектов в системных каталогах, где вышеописанное ограничение размера не может создавать проблему.
Для уникальной нумерации строк в таблицах пользователей, наилучшим решением будет использование типа SERIAL, вместо колонки OID или BIGSERIAL, если ожидается, что таблица будет иметь более 2-х миллиардов записей.
Что такое CTID?
CTID используется для идентификации специальных физических записей с блочными значениями и значениями смещений внутри таблицы. Они используются записями индекса, чтобы указать на физические строки в таблице. Логические CTID изменяется после того как строки в таблице были изменены, так что CTID нельзя использовать как долговременный идентификатор строки таблицы. Но иногда использование CTID является удобным для идентификации строки внутри транзакции, когда не ожидается каких-либо обновлений.
Почему я получаю ошибку «ERROR: Memory exhausted in AllocSetAlloc()»?
Предположительно у вас закончилась виртуальная память или что ваше ядро имеет маленький лимит на определенные ресурсы. Попытайтесь перед запуском сервера БД выполнить следующие команды:
В зависимости от командного интерпретатора shell, только одна из данных команд выполнится успешно, но она позволит вам установить больший сегмент данных процесса и возможно решит проблему. Эта команда изменяет параметры текущего процесса и всех его потомков, созданных после её запуска. Если у вас возникла проблема с SQL клиентом, потому что backend возвращает слишком большой объем данных, попытайтесь выполнить эту команду перед запуском клиента.
Как мне узнать, какая версия PostgreSQL запущена?
Из psql, наберите SELECT version();
Существует ли какой-либо способ аудита операций с базой данных?
Ничего встроенного нет, но не так уж и трудно сделать такую возможность для себя.
Как мне создать колонку которая по умолчанию будет содержать текущее время?
Как мне выполнить внешнее соединение таблиц?
PostgreSQL поддерживает внешние соединения, используя стандартный синтаксис SQL. Вот два примера:
Это идентичные запросы соединения t1.col и t2.col, также возвращают любые несоединённые строки из t1 (которые не совпадают с t2). RIGHT соединение должно добавить несоединённые строки из t2. FULL соединение должно возвратить совпавшие строки плюс все несоединённые строки из t1 и t2. Слово OUTER является необязательным и назначается в LEFT, RIGHT и FULL соединениях. Обычные соединения называются INNER соединениями.
Как выполнять запросы, использующие несколько баз данных?
Не существует способа создать запрос к базам данных отличным от текущей. Поскольку PostgreSQL загружает системные каталоги специфичные для базы данных, непонятно даже, как должен себя вести такой межбазовый запрос.
contrib/dblink позволяет запросы между базами, используя вызовы функций. Разумеется, клиент может одновременно также устанавливать соединения с различными базами данных и таких образом объединять информацию из них.
Как мне вернуть из функции несколько строк таблицы?
Вы можете легко использовать функции, возвращающие список, Return more than one row of data from PL/pgSQL functions.
Почему я получаю ошибку «relation with OID #### не существует», когда обращаюсь к временным таблицам в функциях PL/PgSQL?
В PostgreSQL до версии 8.3, PL/PgSQL кэширует сценарии функции и один из негативных эффектов этого состоит в том, что если функция PL/PgSQL обращается к временной таблице и эта таблица позднее удаляется и пересоздается, а функция затем вызывается снова, то ее вызов приведет к ошибке, потому что скэшированное содержимое функции содержит указатель на старую временную таблицу. Чтобы решить эту проблему, используйте EXECUTE для доступа к временным таблицам в PL/PgSQL. Использование этого оператора заставит запрос перегенерироваться каждый раз.
В PostgreSQL 8.3 и позднее, этой проблемы нет.
Какие есть решения для репликации?
Репликация Master/slave позволяет иметь один главный (master) сервер для выполнения запросов чтения/записи, в то время как подчинённые (slave) сервера могут производить только запросы чтения/SELECT. Наиболее популярным решением для репликации master-slave в PostgreSQL является Slony-I.
Репликация Multi-master позволяет выполнять запросы чтения/записи на нескольких, реплицируемых друг с другом компьютерах. Эта особенность также приводит к потере производительности, потому что необходима синхронизация изменений между несколькими серверами. Наиболее популярным решением для такой репликации в PostgreSQL является PGcluster.
Существуют также проприетарные и основанные на аппаратном обеспечении решения по репликации, работающие на основе различных репликационных моделей.
Возможно ли создать кластер серверов PostgreSQL с разделяемым устройством хранения?
PostgreSQL не поддерживает кластеризацию, используя shared storage на SAN, SCSI-платах, iSCSI томах или других разделяемых носителях. Кластеры такого типа как «RAC-style» не поддерживаются. В настоящий момент поддерживаются только кластеры основанные на репликации.
Использование разделяемых устройств хранения в режиме ‘failover’ возможно, но не безопасно, так как в одно и тоже время более запущен и имеет доступ одним и тем же данным более чем один процесс postmaster. Рекомендуется Heartbeat и STONITH или какие-либо другие жёсткие решения по разрыву соединения.
Почему имена таблицы и колонок не распознаются в в моём запросе? Почему не сохраняются заглавные буквы?
Наиболее часто имена не распознаются из-за использования двойных кавычек в имени таблицы или колонки при создании таблицы. При использовании двойных кавычек, имя таблицы и колонки (которые называют идентификаторами) сохраняются в регистро-зависимом виде; это означает, что вы должны использовать двойные кавычки, когда указываете эти имена в запросе. Некоторые интерфейсы, такие как pgAdmin, во время создания таблицы добавляют двойные кавычки автоматически. Таким образом, чтобы идентификаторы распознавались вы должны следовать одному из следующих правил:
Я потерял пароль от базы данных. Как я могу его восстановить?
Никак. Однако, вы можете сбросить этот пароль в какое-либо значение. Чтобы сделать это, вы должны
Есть ли в PostgreSQL хранимые процедуры?
В чистом виде нет. Однако PostgreSQL имеет очень мощные функции и определяемые пользователями функции, которые в своём большинстве совместимы с тем, что в других СУБД называют хранимыми процедурами (процедурами и функциями), а в некоторых случаях они могут больше.
Эти функции могут быть различных типов и могут быть написаны на разных языках программирования. (Подробности см. в документации Функции определяемые пользователями)
Функции могут быть вызваны несколькими способами. Если вы хотите вызвать функцию также как хранимую процедуру в других СУБД (обычно функцию выполняющую что-либо, но о получении результатов работы которой вы не волнуетесь, потому что она ничего не возвращает), то один из способов может быть использование языка PL/pgSQL для вашей процедуры и команды PERFORM. Например:
Обратите внимание, что если вместо этого вы вызовите:
то вы получите какой-то результат, даже если данная функция ничего не возвращает (в качестве результата будет одна строка, содержащее пустое значение).
Для того, чтобы избежать получения такого ненужного результата, можно использовать PERFORM.
Основные ограничения хранимых функций в PostgreSQL по сравнению с настоящими хранимыми процедурами это:
Почему не работают BEGIN, ROLLBACK и COMMIT внутри хранимых процедур/функций?
PostgreSQL не поддерживает автономных транзакций в хранимых функциях. Как и все запросы PostgreSQL, хранимые функции всегда запускаются в транзакции и не могут работать снаружи транзакции.
Если вам нужна хранимая процедура для управления транзакциями, вы можете посмотреть на интерфейс dblink или делать необходимую работу с помощью скрипта на клиенте. В некоторых случаях, вы можете сделать то, что вам нужно, используя блоки исключений в PL/PgSQL, потому что каждый блок BEGIN/EXCEPTION/END создаёт подтранзакцию.
Почему «SELECT count(*) FROM bigtable;» работает медленно?
Потому что не используется индекс. PostgreSQL выполняет проверку видимости каждой записи и таким образом производит последовательное сканирование всей таблицы. Если вы хотите, вы можете отслеживать количество строк в таблице с помощью триггеров, но это вызовет замедление при операциях записи в таблицу.
Вы можете получить некоторую оценку. Колонка reltuples в таблице pg_class содержит информацию из результата выполнения последнего оператора ANALYZE на эту таблицу. На большой таблице, точность этого значения составляет тысячные доли процента, что вполне достаточно для многих целей.
«Точный» результат count, часто не будет точным долгое время в любом случае; из-за конкурентности MVCC, count будет точным только на момент вызова запущенного запроса SELECT count(*) (или ограничиваться уровнями изоляции транзакций данной транзакции), и может потерять актуальность уже в момент завершения запроса. При постоянной работе транзакций, изменяющий таблицу, два вызова count(*), которые завершатся в одно и то же время могут показать разные значения, если изменяющая транзакция завершилась между их вызовами.
Почему мой запрос намного медленнее, чем когда он запускается в виде подготовленного запроса?
Когда PostgreSQL получает полный запрос со всеми параметрами, он может использовать статистику таблицы, чтобы понять являются ли значения, используемые в запросе, часто употребимыми или часто неупотребимыми в какой-либо колонке. Это позволяет изменить способ извлечения данных на более эффективный, так как известно ожидается ли очень много или очень мало результатов из определённой части запроса. Например, PostgreSQL может выбрать последовательное сканирование вместо использования индекса, если осуществляется поиск ‘active=y’ и при этом известно, что 99% записей в таблице имеют ‘active=y’, поскольку в этом случае последовательное сканирование будет намного быстрее.
В подготовленном запросе, PostgreSQL не получает значение всех параметров, когда создаёт план запроса. Он должен попытаться создать «безопасный» план, который должен работать хорошо вне зависимости от того какое значение вы предоставите как параметр, когда вы вызовите подготовленный запрос. К сожалению, такой план может не оказаться очень хорошим, если значение, которое вы предоставили гораздо более употребительно или гораздо менее употребительно, чем среднее арифметическое из выбираемых значений в таблице.
Иногда люди, у которых возникает эта проблема пытаются использовать подготовленные запросы как меру безопасности для предотвращения SQL иньекций, а не как инструмент тонкой настройки производительности тяжёлых запросов, часто запускающихся с различными параметрами. Такие люди должны подумать на использованием подготовленных операторов на стороне клиента, если их клиентский интерфейс (например PgJDBC) их поддерживает.
В настоящий момент, PostgreSQL не предлагает способа запросить перепланировку подготовленного оператора, используя отдельный список значений параметров; если делать так, то несколько теряется смысл подготовленных операторов на стороне сервера. Запуск статистики, чтобы проверить выходит ли конкретное значение параметра сильно за пределами нормы и в автоматическое перепланирование в этом случае обсуждались, по пока не согласованны и не реализованы.
См. Using_EXPLAIN. Если вы собираетесь обратится за помощью в список рассылки, прочтите Guide to reporting problems.
