Differences

This shows you the differences between two versions of the page.

--- 15_sovetov [2021/08/24 09:21] – chifek
+++ 15_sovetov [2023/09/14 06:06] (current) – external edit 127.0.0.1
@@ Line 87: / Line 87: @@
 pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.
+ ==== Перечень подключенных пользователей ====
+Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:
+<code>
+SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
+</code>
+ ==== Активность пользователя ====
+Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:
+<code>
+SELECT datname FROM pg_stat_activity WHERE usename = 'devuser';
+</code>
+===== Работа с данными и полями таблиц =====
+==== Удаление одинаковых строк ====
+Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:
+дублирующиеся строки,
+ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).
+Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).
+{{:postgre_01.png?600|}}
+Удалить все дубликаты поможет следующий запрос:
+<code>
+DELETE FROM customers WHERE ctid NOT IN
+(SELECT max(ctid) FROM customers GROUP BY customers.*);
+</code>
+Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.
+Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.
+Теперь рассмотрим случай, когда повторяются значения полей.
+{{:postgre_02.png?600|}}
+Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:
+<code>
+DELETE FROM customers WHERE ctid NOT IN
+(SELECT max(ctid) FROM customers GROUP BY customer_id);
+</code>
+Если данные важны, то сначала нужно найти записи с дубликатами:
+<code>
+SELECT * FROM customers WHERE ctid NOT IN
+(SELECT max(ctid) FROM customers GROUP BY customer_id);
+</code>
+{{:postgre_03.png?600|}}
+Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.
+Общая форма запроса на удаление описанных выше записей выглядит следующим образом:
+<code>
+DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, [column 2,] );
+</code>
+ ==== Безопасное изменение типа поля ====
+Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER. Давайте для примера снова рассмотрим таблицу с покупателями.
+Для поля customer_id используется строковый тип данных varchar. Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer. Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER:
+<code>
+ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;
+</code>
+Но в результате выполнения получим ошибку:
+<code>
+ERROR: column “customer_id” cannot be cast automatically to type integer
+SQL state: 42804
+Hint: Specify a USING expression to perform the conversion.
+</code>
+Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar, СУБД не может определить принадлежность значения к integer. Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING, чтобы корректно преобразовать наши данные в integer:
+<code>
+ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);
+</code>
+В результате всё прошло без ошибок:
+{{:postgre_04.png?600|}}
+Обратите внимание, что при использовании USING кроме конкретного выражения возможно использование функций, других полей и операторов.
+Например, преобразуем поле customer_id обратно в varchar, но с преобразованием формата данных:
+<code>
+ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || '-' || first_name);
+</code>
+В результате таблица примет следующий вид:
+{{:postgre_05.png?600|}}
+ ==== Поиск «потерянных» значений ====
+Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.
+{{:postgre_06.png?600|}}
+Рассмотрим два варианта поиска.
+**Первый способ**
+Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:
+<code>
+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;
+</code>
+В результате получим значения: 5, 9 и 11.
+Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:
+<code>
+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;
+</code>
+В результате видим следующий результат: 5, 9 и 6.
+**Второй способ**
+Получаем имя последовательности, связанной с customer_id:
+<code>
+SELECT pg_get_serial_sequence('customers', 'customer_id');
+</code>
+И находим все пропущенные идентификаторы:
+<code>
+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;
+</code>
+ ==== Подсчёт количества строк в таблице ====
+Количество строк вычисляется стандартной функцией count, но её можно использовать с дополнительными условиями.
+Общее количество строк в таблице:
+<code>
+SELECT count(*) FROM table;
+</code>
+Количество строк при условии, что указанное поле не содержит NULL:
+<code>
+SELECT count(col_name) FROM table;
+</code>
+Количество уникальных строк по указанному полю:
+<code>
+SELECT count(distinct col_name) FROM table;
+</code>
+ ==== Использование транзакций ====
+Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.
+Начнём транзакцию с помощью команды BEGIN.
+Для того, чтобы откатить все операции, расположенные после BEGIN, используем команду ROLLBACK.
+А чтобы применить — команду COMMIT.
+ ==== Просмотр и завершение исполняемых запросов ====
+Для того, чтобы получить информацию о запросах, выполним следующую команду:
+<code>
+SELECT pid, age(query_start, clock_timestamp()), usename, query
+FROM pg_stat_activity
+WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
+ORDER BY query_start desc;
+</code>
+Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):
+<code>
+SELECT pg_cancel_backend(procpid);
+</code>
+Для того, чтобы прекратить работу запроса, выполним:
+<code>
+SELECT pg_terminate_backend(procpid);
+</code>
+===== Работа с конфигурацией =====
+==== Поиск и изменение расположения экземпляра кластера ====
+Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:
+<code>
+SHOW data_directory;
+</code>
+Изменим расположение на другое с помощью команды:
+<code>
+SET data_directory to new_directory_path;
+</code>
+Но для того, чтобы изменения вступили в силу, требуется перезагрузка.
+ ==== Получение перечня доступных типов данных ====
+Получим перечень доступных типов данных с помощью команды:
+<code>
+SELECT typname, typlen from pg_type where typtype='b';
+</code>
+typname — имя типа данных.
+typlen — размер типа данных.
+ ==== Изменение настроек СУБД без перезагрузки ====
+Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf. После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:
+<code>
+SELECT pg_reload_conf();
+</code>
+Но, к сожалению, она применима не ко всем параметрам. В некоторых случаях для применения настроек перезагрузка обязательна.
+Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!
+ ===== Ссылки =====
+Источник [[https://tproger.ru/translations/useful-postgresql-commands/]]