В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.
Такие команды, или сниппеты , редко описаны в документации. Рассмотрим несколько на примерах, полезных как для разработчиков, так и для администраторов баз данных.
Получение информации о базе данных
Размер базы данных
Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:
SELECT pg_database_size(current_database());
Результат будет представлен как число вида 41809016 .
current_database() - функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:
SELECT pg_database_size("my_database");
Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty:
SELECT pg_size_pretty(pg_database_size(current_database()));
В результате получим информацию вида 40 Mb .
Перечень таблиц
Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema","pg_catalog");
information_schema - стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.
Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("information_schema", "pg_catalog") AND table_schema IN("public", "myschema");
В последнем условии IN можно указать имя определенной схемы.
Размер таблицы
По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:
SELECT pg_relation_size("accounts");
Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.
Имя самой большой таблицы
Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname - имя таблицы, индекса, представления и т.п.
relpages - размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class - системная таблица, которая содержит информацию о связях таблиц базы данных.
Перечень подключенных пользователей
Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
Активность пользователя
Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
SELECT datname FROM pg_stat_activity WHERE usename = "devuser";
Работа с данными и полями таблиц
Удаление одинаковых строк
Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
- дублирующиеся строки,
- ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
Удалить все дубликаты поможет следующий запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);
Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.
Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
Теперь рассмотрим случай, когда повторяются значения полей.
Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Если данные важны, то сначала нужно найти записи с дубликатами:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.
Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, );
Безопасное изменение типа поля
Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.
Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER:
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;
Но в результате выполнения получим ошибку:
ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.
Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer:
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);
В результате всё прошло без ошибок:
Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.
Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:
ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);
В результате таблица примет следующий вид:
Поиск «потерянных» значений
Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
Рассмотрим два варианта поиска.
Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
SELECT customer_id + 1 FROM customers mo WHERE NOT EXISTS (SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;
В результате получим значения: 5 , 9 и 11 .
Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
WITH seq_max AS (SELECT max(customer_id) FROM customers), seq_min AS (SELECT min(customer_id) FROM customers) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM customers;
В результате видим следующий результат: 5 , 9 и 6 .
Второй способ
Получаем имя последовательности, связанной с customer_id:
SELECT pg_get_serial_sequence("customers", "customer_id");
И находим все пропущенные идентификаторы:
WITH sequence_info AS (SELECT start_value, last_value FROM "SchemaName"."SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM customers;
Подсчёт количества строк в таблице
Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.
Общее количество строк в таблице:
SELECT count(*) FROM table;
Количество строк при условии, что указанное поле не содержит NULL:
SELECT count(col_name) FROM table;
Количество уникальных строк по указанному полю:
SELECT count(distinct col_name) FROM table;
Использование транзакций
Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
Начнём транзакцию с помощью команды BEGIN .
Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .
А чтобы применить - команду COMMIT .
Просмотр и завершение исполняемых запросов
Для того, чтобы получить информацию о запросах, выполним следующую команду:
SELECT pid, age(query_start, clock_timestamp()), usename, query
FROM pg_stat_activity
WHERE query != "
Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
SELECT pg_cancel_backend(procpid);
Для того, чтобы прекратить работу запроса, выполним:
SELECT pg_terminate_backend(procpid);
Работа с конфигурацией
Поиск и изменение расположения экземпляра кластера
Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра - достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:
SHOW data_directory;
Изменим расположение на другое с помощью команды:
SET data_directory to new_directory_path;
Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
Получение перечня доступных типов данных
Получим перечень доступных типов данных с помощью команды:
SELECT typname, typlen from pg_type where typtype="b";
typname - имя типа данных.
typlen - размер типа данных.
Изменение настроек СУБД без перезагрузки
Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:
SELECT pg_reload_conf();
Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.
.Как сделать постраничный вывод из mysql "как в яндексе"?
по 10 записей на страницу, внизу - ссылки на остальные страницы?
Сначала научимся получать из базы нужные записи.
Их получение в mysql обеспечивается оператором LIMIT, который вызывается с двумя параметрами - с какой записи начинать, и сколько выводить (внимание! не по какую, а сколько!)
SELECT * FROM table LIMIT 0,10
этот запрос вернет записи с первой по 10, поскольку нумерация начинается с 0
соответственно, запрос для третьей страницы будет выглядеть, как
SELECT * FROM table LIMIT 20,10
получается, что нам всего лишь надо передать в скрипт число, которое потом подставить в запрос.
Этим будет заниматься код, который выводит ссылки на страницы.
Естественно, в цикле.
Для цикла нам понадобится количество записей, которое возвращает запрос без лимита.
Это число можно получить двумя путями. Либо отдельным запросом, в котором отсутствует оператор LIMIT, а вместо перечисления полей после оператора SELECT запрашивается только count(*):
$q
=
"SELECT count(*) FROM table"
;
$res
=
mysql_query
($q
);
$row
=
mysql_fetch_row
($res
);
$total_rows
=
$row
[
0
];
Либо, если версия mysql больше 4.0, то общее количество строк можно запросить в том же запросе. См. документацию mysql по функции FOUND_ROWS()
Однако, первый сособ представляется более удобным, хотя и немного более медленным.
Для тех, кто предпочитает думать самостоятельно, этой информации достаточно.
Вывод ссылок - это простейшая арифметическая операция и никаких особенных знаний для нее не требуется.
Для тех, же, кто ищет готовый код, продолжим.
Для начала определим, сколько всего получится страниц. Для этого надо поделить общее число записей на количество оных на одной странице и округлить результат в большую сторону. Таким округлением занимается в пхп функция ceil()
$num_pages
=
ceil
($total_rows
/
$per_page
);
В этом выражении участвует переменная $per_page
, в которую мы положим количество выводимых на странице записей.
Ведь, если это количество изменится, мы же не хотим ползать по всему коду и исправлять цифры? проще сделать это один раз в начале скрипта при объявлении переменной. В запрос, вторым параметром LIMIT, подставлять нужно, конечно же, тоже ее.
Ну, а дальше, собственно, вывод ссылок.
for($i
=
1
;
$i
<=
$num_pages
;
$i
++) {
echo
""
.
$i
.
"\n"
;
}
в цикле от 1 до $num_pages выводим ссылку с параметром num, равным числу, которое надо передать в LIMIT, а в тексте ссылки пишем номер страницы, поскольку людям понятнее видеть номер страницы, а не записи. На код это не влияет, а людям приятно.
Дальше пойдут украшения.
Во-первых, некрасиво, что номер страницы не совпадает с тем, что видно в адресной строке. Эту проблему можно решить, передавая по ссылке номер страницы в человекопонятном формате, а в скрипте вычислять первый операнд для LIMIT.
Во-вторых, мы явно захотим выделить текущую страницу, не оформляя ее ссылкой.
В-третьих, мы захотим нумеровать записи
Разумеется, вышеприведённый код подходит только как учебное пособие. С его помощью становится понятным принцип, но в реальных условиях мы сразу же столкнемся, как минимум, с двумя проблемами:
Во-первых, кроме переменной $page нашему крипту явно будут переданы и другие переменные, да и адрес может совсем не совпадать с именем скрипта. А мы это при формировании ссылок не учитываем.
Во-вторых, нормальный современный сайт немыслим без шаблонов. И такая ужасная лапша из SQL запросов, PHP кода и HTML тегов никуда не годится.
Плюс надо избавляться от устаревшего расширения mysql и организовывать работу с БД более интеллектуально.
Займемся решением этих проблем.
Первая решается очень просто при использовании функции http_build_query()
Вторая - тоже несложно. Шаблонизаторов много, но мы воспользуемся самым универсальным - PHP.
Что же у нас получилось? А получился у нас - рефакторинг ! Переделка старого кода в соответствии с требованиями современности, плюс мелкое причесывание:
include
"safemysql.class.php"
;
$db
= new
safeMysql
();
$per_page = 10 ;
//получаем номер страницы и значение для лимита
$cur_page
=
1
;
if (isset($_GET
[
"page"
]) &&
$_GET
[
"page"
] >
0
)
{
$cur_page
=
$_GET
[
"page"
];
}
$start
= ($cur_page
-
1
) *
$per_page
;
//выполняем запрос и получаем данные для вывода
$sql
=
"SELECT SQL_CALC_FOUND_ROWS * FROM Board LIMIT ?i, ?i"
;
$data
=
$db
->
getAll
($sql
,
$start
,
$per_page
);
$rows
=
$db
->
getOne
("SELECT FOUND_ROWS()"
);
//узнаем общее количество страниц и заполняем массив со ссылками
$num_pages
=
ceil
($rows
/
$per_page
);
// зададим переменную, которую будем использовать для вывода номеров страниц
$page
=
0
;
//а дальше выводим в шаблоне днные и навигацию:
?>
Найдено сообщений:
.
">
Страницы:
">
Postgres, разработанный в оригинале департаментом UC Berkeley Computer Science Department, был пионером многих объектно-ориентированных концепций, ставших теперь доступными в некоторых коммерческих БД. Он предоставляет поддержку языка SQL92/SQL99, целостности транзакций и расширяемости типов. PostgreSQL это открытый ресурс, потомок оригинального Berkeley-кода.
PostgreSQL это продукт, доступный бесплатно. Для использования поддержки PostgreSQL вам необходим PostgreSQL 6.5 или новее. PostgreSQL 7.0 или новее - для всех возможностей модуля PostgreSQL. PostgreSQL поддерживает многие кодировки символов, включая кодировку многобайтных символов. Текущая версия и информация о PostgreSQL находятся на http://www.postgresql.org/ .
Чтобы включить поддержку PostgreSQL, необходима опция --with-pgsql[=DIR] при компиляции PHP. Если модуль совместно используемых/shared объектов доступен, PostgreSQL-модуль может быть загружен с использованием директивы extension в файле php.ini или функции dl() . Поддерживаемые ini-директивы описаны в файле php.ini-dist , поставляемом вместе с исходным кодом дистрибутива.
Таблица 1. Изменения имён функций
| Старое имя | Новое имя |
|---|---|
| pg_exec() | pg_query() |
| pg_getlastoid() | pg_last_oid() |
| pg_cmdtuples() | pg_affected_rows() |
| pg_numrows() | pg_num_rows() |
| pg_numfields() | pg_num_fields() |
| pg_fieldname() | pg_field_name() |
| pg_fieldsize() | pg_field_size() |
| pg_fieldnum() | pg_field_num() |
| pg_fieldprtlen() | pg_field_prtlen() |
| pg_fieldisnull() | pg_field_is_null() |
| pg_freeresult() | pg_free_result() |
| pg_result() | pg_fetch_result() |
| pg_loreadall() | pg_lo_read_all() |
| pg_locreate() | pg_lo_create() |
| pg_lounlink() | pg_lo_unlink() |
| pg_loopen() | pg_lo_open() |
| pg_loclose() | pg_lo_close() |
| pg_loread() | pg_lo_read() |
| pg_lowrite() | pg_lo_write() |
| pg_loimport() | pg_lo_import() |
| pg_loexport() | pg_lo_export() |
Не все функции поддерживаются во всех построениях/builds. Это зависит от версии вашей libpq (The PostgreSQL C Client interface) и от того, как libpq скомпилирована. Если имеется отсутствующая функция, libpq не поддерживает возможности, требуемые для этой функции.
Важно также, чтобы вы использовали libpq более новую, чем PostgreSQL Server, с которым соединяетесь. Если вы используете libpq более старую, чем ожидает PostgreSQL Server, у вас будут проблемы.
Начиная с версии 6.3 (03/02/1998), PostgreSQL использует по умолчанию сокет домена unix. TCP-порт НЕ открывается по умолчанию. В таблице описаны эти новые возможности соединений. Этот сокет можно найти в in /tmp/.s.PGSQL.5432 . Данная опция может быть включена флагом "-i" для postmaster , и его значением будет: "прослушивать TCP/IP-сокеты, а также сокеты Unix-домена".
Таблица 2. Postmaster и PHP
Соединение с PostgreSQL-сервером может быть установлено следующими парами значений в командной строке: $conn = pg_connect("host=myHost port=myPort tty=myTTY options=myOptions dbname=myDB user=myUser password=myPassword ") ;
Предыдущий синтаксис: $conn = pg_connect ("host", "port", "options", "tty", "dbname") теперь не рекомендуется.
Переменные окружения влияют на поведение PostgreSQL server/client. Например, PostgreSQL-модуль будет искать переменную окружения PGHOST, если hostname отсутствует в строке соединения. Поддерживаемые переменные окружения отличаются в разных версиях. См. детали в PostgreSQL Programmer"s Manual (libpq - Environment Variables).
Убедитесь, что вы установили переменные окружения для соответствующего пользователя. Используйте $_ENV или getenv() для проверки того, какие переменные окружения доступны текущему процессу.
Начиная работу с PostgreSQL 7.1.0, вы можете сохранять 1GB в поле типа text. В более старых версиях могут быть ограничения на размер блоков (по умолчанию было 8KB, максимум был 32KB, определяемые на этапе компиляции).
Для использования интерфейса больших объектов/large object (lo) необходимо включать lo-функции внутри блока транзакции. Блок транзакции начинается с SQL-оператора BEGIN , и, если транзакция была верной, заканчивается COMMIT или END . Если транзакция терпит неудачу, она должна быть закрыта с помощью ROLLBACK или ABORT .
Вы не должны закрывать соединение с PostgreSQL-сервером до закрытия large-объекта.
Содержание pg_affected_rows - возвращает количество задействованных записей (пар/tuples) pg_cancel_query - отменяет async-запрос pg_client_encoding - получает клиентскую кодировку pg_close - закрывает PostgreSQL-соединение pg_connect - открывает PostgreSQL-соединение pg_connection_busy - получает, закрыто соединение или нет pg_connection_reset - восстанавливает соединение (reconnect) pg_connection_status - получает статус соединения pg_convert - конвертирует значение ассоциативного массива в значение, подходящее для оператора SQL pg_copy_from - вставляет записи в таблицу из массива pg_copy_to - копирует таблицу в массив pg_dbname - получает имя базы данных pg_delete - удаляет записи pg_end_copy - синхронизирует с PostgreSQL backend pg_escape_bytea - мнемонизирует/еscape binary для типа bytea pg_escape_string - мнемонизирует string для типа text/char pg_fetch_array - извлекает ряд как массив pg_fetch_object - извлекает ряд как объект pg_fetch_result - возвращает значения из результирующего ресурса pg_fetch_row - получает ряд как перечислимый массив pg_field_is_null - проверяет, является ли поле NULL pg_field_name - возвращает имя поля pg_field_num - возвращает номер именованного поля pg_field_prtlen - возвращает печатаемый размер pg_field_size - возвращает внутренний размер хранения именованного поля pg_field_type - возвращает имя типа для соответствующего номера поля Советы
