Редактор SQL [PLPGSQL] для СУБД PostgreSQL

Не нашли нужных функций? Пишите в комментариях на форуме и мы добавим нужный функционал

Для работы с СУБД, установка дополнительного ПО не требуется. Все необходимые библиотеки идут в комплекте

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

​

ROLES

​

Список ролей в кластере PostgreSQL. Роль от пользователя отличается только возможностью подключатся к кластеру.

​

Список групп (ролей)

->Запрос для извлечения данных<-

SELECT r.rolname,
       r.rolsuper::int,
       r.rolinherit::int,
       r.rolcreaterole::int,
       r.rolcreatedb::int,
       r.rolcanlogin::int,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
           FROM pg_catalog.pg_auth_members m
           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
              WHERE m.member = r.oid)::text as memberof,
       pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, 
       r.rolreplication::int, 
       r.rolbypassrls::int
FROM pg_catalog.pg_roles r
  where rolcanlogin = false
ORDER BY 1 

-----

​

Меню

• Copy name - скопировать в буфер обмена имя выделенной роли

• Edit role - открыть редактор роли

USERS

​

Список пользователей БД (роли которым разрешено подключатся к кластеру PostgreSQL)

​

Список пользователей 

->Запрос для извлечения данных<-

SELECT r.rolname,
       r.rolsuper::int,
       r.rolinherit::int,
       r.rolcreaterole::int,
       r.rolcreatedb::int,
       r.rolcanlogin::int,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
           FROM pg_catalog.pg_auth_members m
           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
              WHERE m.member = r.oid)::text as memberof,
       pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, 
       r.rolreplication::int, 
       r.rolbypassrls::int
FROM pg_catalog.pg_roles r
  where rolcanlogin = true
ORDER BY 1 

-----

​

Меню

• Copy name - скопировать имя роли в буфер обмена

• Edit role - открыть редактор роли

PostgreSQL Monitor

​

Основное средство управления и мониторинга работы кластера PostgreSQL

Для запуска дважды кликнуть по "MONITOR" или "PARAMETER"

​

DASHBOARD

Основная информация по нагрузке на БД. Данные обновляются 1 раз в сек. 

• Server session - информация по сессиям (всего, активных, простаивающих)

• Transaction per second - информация по транзакциям (активных, фиксирующих данные, выполняющих откат)

• Tuples In - данные по операциям изменяющим данные (вставка, обновление, удаление)

• Tuples Out - данные по операциям извлечения данных из базы (Количество строк, возвращённое запросами в этой базе данных; Количество строк, возвращённое запросами в этой базе данных)

• Block I/O - количество блоков прочитанных с диска или найденных в памяти

​

Для выбора базы по которой выводить статистику использовать выпадающий список "Base statistics". Доступно: имя базы или ALL - по всем суммарно

Управление сбором данных осуществляется через переключатель "Running"

​

->Запрос для извлечения данных<-

SELECT 'session_stats' AS chart_name, t.*
    FROM (SELECT  * from
       (SELECT 'Total'  "Name1", count(*) "COUNT1" FROM pg_stat_activity
          where datname = $$DB_NAME
       ) AS "1",
       (SELECT 'Active' "Name2", count(*) "COUNT2" FROM pg_stat_activity WHERE state = 'active'
          and datname = $$DB_NAME
       ) "2",
       (SELECT 'Idle'   "Name3", count(*) "COUNT3" FROM pg_stat_activity WHERE state = 'idle'
         and datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'tps_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Transactions' "Name1", sum(xact_commit) + sum(xact_rollback) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Commits' "Name2", sum(xact_commit) "COUNT2"  FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT 'Rollbacks' "Name3", sum(xact_rollback) "COUNT3" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'ti_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Inserts' "Name1", sum(tup_inserted) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Updates' "Name2", sum(tup_updated) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT 'Deletes' "Name3", sum(tup_deleted) "COUNT3" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'to_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Fetched' "Name1", sum(tup_fetched) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Returned' "Name2", sum(tup_returned) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT '' "Name3", 0 "COUNT3") "3"
    ) t
    UNION ALL
    SELECT 'bio_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Reads' "Name1", sum(blks_read) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       )"1",
       (SELECT 'Hits' "Name2", sum(blks_hit) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT '' "Name3", 0 "COUNT3") "3"
    ) t 

-----

​

SESSION LIST

Менеджер сессий в кластере.

Обновление данных выполняется в ручном режиме. 

​

Элементы управления

Выпадающий список "Query type" - вид SQL запроса используемого для извлечения данных

• Version >= 10

• Version <10

​

Version >= 10  - для баз версии 10 и старше

->Запрос для извлечения данных<-

SELECT
        datname,
        pid,
        usename,
        application_name,
        cast(client_addr as text),
        to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start,
        to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start,
        state,
        wait_event_type || ': ' || wait_event AS wait_event,
        cast(pg_blocking_pids(pid) as text) AS blocking_pids,
        query,
        backend_type
    FROM
        pg_stat_activity
        ORDER BY pid

-----

​

Version < 10  - баз версий меньше 10 

->Запрос для извлечения данных<-

SELECT
        datname,
        pid,
        usename,
        application_name,
        cast(client_addr as text),
        to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start, 
        to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start,
        state,
        '' wait_event,
        '---' blocking_pids,
        query,
        '9.0' backend_type
    FROM
        pg_stat_activity
        ORDER BY pid

-----

​

Обновить данные

Завершить выполнение текущего запроса без разрыва соединения (select pg_cancel_backend($$PID))

Завершить текущую сессию (select pg_terminate_backend($$PID))

При выделении сессии в списке "Session list" в поле "SQL Text" выводится текущий SQL запрос в сессии.

При переключении на вкладку "Query plan" выводится план выполнения запроса

​

​

LOCK

Блокировки в БД

​

->Запрос для извлечения данных<-

select l.locktype,
       d.datname,
       c.relname,
       l.page,
       l.tuple,
       l.transactionid,
       cl.relname,
       l.objid,
       l.pid,
       l.mode
  from pg_locks l left join pg_database d on l.database=d.oid
                  left join pg_class c on l.relation=c.oid
                  left join pg_class cl on l.classid=cl.oid
order by 2

-----

​

При выделении строки в списке "Lock list" в блоке "Process" выводится подробная информация по процессу.

Двойной клик по строке в поле "Process" отображает текст запроса в сессии

​

​

Server statistics

Различные статистики работы, полезная информация

Страница Database stats

​

->Запрос для извлечения данных<-

select l.locktype,
       d.datname,
       c.relname,
       l.page,
       l.tuple,
       l.transactionid,
       cl.relname,
       l.objid,
       l.pid,
       l.mode
  from pg_locks l left join pg_database d on l.database=d.oid
                  left join pg_class c on l.relation=c.oid
                  left join pg_class cl on l.classid=cl.oid
order by 2

-----

​

Страница "PG_CONFIG DATA"

Данные из системной таблицы pg_config

​

->Запрос для извлечения данных<-

select * from pg_config

-----

​

Страница "pg_file_settings"

Данные из системной таблицы pg_file_settings

​

->Запрос для извлечения данных<-

select f.sourcefile, f.name, f.setting, f.applied, f.error from pg_file_settings f

-----

​

Страница "Language support"

Список поддерживаемых языков программирования

​

->Запрос для извлечения данных<-

select l.lanname,
       a.rolname,
       l.lanispl,
       l.lanpltrusted,
       l.lanplcallfoid,
       l.laninline,
       l.lanvalidator
  from pg_language l left join pg_authid a on l.lanowner=a.oid

-----

​

Страница "Percentage before freezing"

На странице отображается информация о качестве работы процессов autovacuum. Процент в поле "Percentage before freezing" близкий к нулю говорит о том, что авто вакуум не успевает очищать таблицы и велика вероятность ситуации при которой потребуется переводить базу в однопользовательский режим и выполнять vacuum full.

При большом количестве таблиц с низким процентом необходимо проанализировать значения параметров авто вакуума на предмет их изменения на более агрессивные значения.

​

->Запрос для извлечения данных<-

with rel_frozen as (select c.oid::regclass as table_name,
                           (select e.nspname from pg_catalog.pg_namespace e where oid = c.relnamespace) as schema, 
                        greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
                        greatest(age(c.relfrozenxid), age(t.relfrozenxid))*100./(pow(2,31)-1) as perc_wrpa
from pg_class c left join pg_class t on c.reltoastrelid = t.oid
where c.relkind in ('r','m'))
select table_name::text "table", schema, 100 - round(perc_wrpa::numeric,2) "froz" from rel_frozen order by 3  des

-----

​

​

PARAMETER EDITOR

Редактор параметров кластера PostgreSQL

​

Поле "Filter" служит для фильтрации списка параметров по введённому тексту. Текст ищется по всем колонкам и в любой части строки

Поле "Category" служит для блочной фильтрации списка

​

Изменение значения параметра

• выделить параметр на вкладке "Server parameter"

• ввести новое значение в поле "Value"

• нажать кнопку "Apply"

​

->Запрос для извлечения данных<-

SELECT name,
       category,
       setting,
       unit,
       short_desc
  FROM pg_settings
order by category 

-----

Изменение значения

->Запрос для извлечения данных<-

alter system set $$PARAMETER_NAME = $$NEW_VALUE

SELECT pg_reload_conf()

-----

​

NOT USING INDEX

Список индексов к которым не было обращений или их количество меньше заданного в поле "show indices with hit less"

​

->Запрос для извлечения данных<-

select * from pg_catalog.pg_stat_all_indexes
  where idx_scan < $$NUMBER
    and schemaname not in ('pg_toast','pg_catalog')
order by idx_scan 

-----

​

Кнопки управления

​​

• Execute current SQL - Выполнить текущий выделенный запрос или все запросы на листе

​

Execute - аналогично нажатию на кнопку

To run the script from a file - выполнить скрипт с диска. Скрипт не загружается в редактор.

Check the script file for validate - проверить на корректность скрипт на диске без его выполнения

Show a list of command for execute - произвести разбор команд в редакторе на блоки выполнения. Выводится внутренний код и текст единичной команды

​

• Commit - зафиксировать изменения

• Rollback - откатить изменения внесенные в данные последними запросами

• Stop execute - остановить выполнение текущего запроса

• Close open cursor - закрыть текущий набор данных

• Show plan - показать план выполнения выделенного запроса

​

->Запрос для извлечения данных<-

EXPLAIN VERBOSE <query> 

-----

​

• Export data in file - выгрузка данных грида (видимой части данных) в файл на диске в:

​

Export data in XLS - выгрузить данные в файл в формате XLS

Export data in TXT - выгрузить данные в файл в формате TXT

Export data in CSV - выгрузить данные в файл в формате CSV

Export data in HTML - выгрузить данные в файл в формате HTML

Export data in RTF - выгрузить данные в файл в формате RTF

​​

• Save current list in file - сохранить содержимое текущего листа в файл на диске

• Open file - открыть менеджер файлов

​

​​

• List saving code - открыть менеджер создания блоков кода. Блоки кода - это конструкции кода сохраненные в программе, доступ к которым осуществляется из выпадающего меню кнопки

​

Создание блока кода

1. Нажать кнопку "New"

2. Из выпадающего списка "Parent name" выбрать имя корневого узла. Корневой узел так же является родителем в меню. Для добавления новой записи в список нажать кнопку "Add new parent" и внести новые значения

3. Из выпадающего списка "Page type" выбрать тип СУБД для которых должен отображаться блок кода в списке

4. В поле "Code" ввести текст блока кода, данный текст добавляется в редактор

5. Нажать кнопку "Save" 

​

​​

• Project manager - открыть менеджер проектов

​

​

Рабочая область

Это поле на листе для ввода запросов/команд.

​

Разделение кода на составные части

Движок редактора запросов перед выполнением команд производит разбор кода, определяя составные части.

Для разделения кода используются специальные символы (; - точка с запятой, / - слеш, go - ключевое слово на отдельной строке).

​

Программа поддерживает два синтаксиса кода:

• Классический синтаксис Oracle: для отделения команд друг от друга используется символ ; (точка с запятой) за исключением команд create, begin, declate - данные команды отделяет от других символ / (слеш) на отдельной строке

• Синтаксис MSSQL Server: для отделения команд друг от друга используется ключевое слово go на отдельной строке

Разрешено использовать любой из двух синтаксисов, в том числе оба одновременно

​

Логирование выполняемых операций

Текст любой команды/запроса языка SQL после выполнения сохраняется в локальном хранилище (база Core - таблица cer_history_query). В базе фиксируется: текст запроса/команды фактически выполненной ядром программы, статус выполнения, время выполнения, количество измененных строк, сообщения БД (output)

​

Дополнительно к сохранению данных в БД прогресс выполнения каждой команды фиксируется на вкладке "Messages" листа: отображается статус выполнения команд, время выполнения, номера строк в редакторе на которых располагается выполненная команда. Данные вкладки очищаются перед запуском выполнения команд

​

Иконки в левой части окна отображают прогресс выполнения(выполненные команды, текущая команда, ожидают выполнения, ошибка выполнения запроса, строка с ошибкой в запросе)

​

Подсказчик кода

Подсказчик кода помогает ускорить написание запрос, подсказывая имена объектов, колонок.

Для схем - отображаются все объекты схемы

​

Для алиасов таблиц, представлений - список колонок

​

По части имени объекта - отображаются объекты в БД имя которых начинается с введенного текста. Подсказка отображается по комбинации клавиш Crtl+Space

​

Алиасы запросов

В программе для запросов можно назначить алиас и в последствии выполнять запрос по нему, подробно смотрите в разделе "Диспетчер алиасов запросов"

​

Меню рабочей области

• Copy - скопировать выделенный текст в буфер обмена

• Past - вставить содержимое буфера обмена в окно редактора

• Cut - вырезать выделенный текст

• SelectAll - выделить весь текст в рабочей области

• View table - просмотр подробной информации по выделенной таблице

• Get DDL - показать код создания выделенной таблицы

• Show command help - показать справку по выбранной команде

​

​​

• Show object data - показать данные выделенной таблицы, представления, синонима

• Project manager -> Add object - создать страницу для выделенного кода в текущем проекте по умолчанию

​

​​

• Project manager -> Search object card - найти карточку выделенного объекта в текущем проекте по умолчанию

• Comment -> Add current line - добавить символы комментария в начало текущей строки 

• Comment -> Remove current line - удалить символы комментария в начале текущей строки

• Comment -> Add selected text - добавить символы комментария в начало каждой из выделенных строк

• Comment -> Remove selected text - удалить символы комментария в начале каждой из выделенных строк