Топ-100
 
CerebroSQL

SQL/PLPGSQL developer for PostgreSQL

Didn't find the features you want? Write in the comments on the forum and we will add the required functionality
 

To work with a DBMS, you do not need to install additional software. All necessary libraries are included

Library version 9.6 is used by default

 
OBJECT Node

Root node, contains groups of basic objects in the database. The name of the database is displayed in brackets.

The list of schemes for all child nodes is formed by the request:

-> Request to retrieve data <-

SELECT n.oid, n.nspname AS "schema_name"
FROM pg_catalog.pg_namespace n
WHERE n.nspname not like 'pg_%' and n.nspname <> 'information_schema'
ORDER BY 1

-----

 
TABLES

List of database tables grouped by schema.

Partitioned tables are marked with a separate icon.

tree view - table.jpg

-> Request to retrieve data <-

SELECT c.oid, n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 't' THEN 'TOAST' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partition_table' END as "Type",
pg_catalog.pg_get_userbyid (c.relowner) as "Owner",
pg_catalog.obj_description (c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 't', 'f', 'p')
AND c.relispartition = false
and n.nspname = $$SCHEMA_NAME
ORDER BY c.relname

-----

Menu:
  • Copy name - copy the name of the object to the clipboard

  • Create code [select] - form the query text for the object (the names of all fields are displayed in the select clause)

  • Get code - generate table creation code

  • Get statistics - display statistics on table usage. The data is displayed on the Statistics tab of the main window

  • Reindex - rebuild all indexes on a table (using the reindex command)

  • Data - display table data (limit 50)

 

  • COLUMN - list of table columns, the name of the column and its type are displayed

-> Request to retrieve data <-

select column_name || ' ['|| data_type ||'] '"col"
  from information_schema.columns
where Upper (table_schema || '.' || table_name) = Upper ($$ TABLE_NAME)

order by column_name

-----

  • INDEX is a list of indexes on the table. For each index, a list of columns by which it is built is additionally displayed

For partitioned tables, information on indexes in this node is not displayed

-> Request to retrieve data <-

SELECT c.relname,
       i.indkey,
       a.attname
  FROM pg_catalog.pg_class c
  JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
  JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
  LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  LEFT JOIN pg_attribute a on a.attrelid = c.oid
WHERE c.relkind IN ('i')
   and n.nspname = $$SCHEMA_NAME
   and c2.relname = $$TABLE_NAME
ORDER BY 1

-----

  • TRIGGER - list of triggers on the table.

-> Request to retrieve data <-

select t.trigger_name,
       t.event_manipulation,
       t.event_object_schema || '.' || t.event_object_table "table",
       t.action_order,
       t.action_condition,
       t.action_statement,
       t.action_orientation,
       t.action_timing,
       t.action_reference_old_table,
       t.action_reference_new_table,
       t.action_reference_old_row,
       t.action_reference_new_row,
       t.created
  from information_schema.triggers t
where t.event_object_schema = $$ SCHEMA_NAME
   and t.event_object_table = $$ TABLE_NAME

-----

  • SIZE OBJECT - the size of the table and all its indexes

-> Request to retrieve data <-

SELECT pg_size_pretty (pg_total_relation_size ($$SCHEMA_NAME.$$TABLE_NAME)) "Size"

-----

  • STATISTICS - table usage statistics

-> Request to retrieve data <-

select t.seq_scan,
       t.seq_tup_read,
       t.idx_scan,
       t.idx_tup_fetch,
       t.n_tup_ins,
       t.n_tup_del,
       t.n_tup_hot_upd,
       t.n_live_tup,
       t.n_dead_tup,
       t.last_vacuum,
       t.last_autovacuum,
       t.last_analyze,
       t.last_autoanalyze
  from pg_catalog.pg_stat_user_tables t
where t.schemaname = $$SCHEMA_NAME
   and t.relname = $$TABLE_NAME

-----

For partitioned tables, the following are additionally displayed

  • PARKEY - partitioning key

-> Request to retrieve data <-

SELECT pg_catalog.pg_get_partkeydef($$TABLE_OID::pg_catalog.oid) "parkey"

-----

  • PARTITION - list of table partitions

-> Request to retrieve data <-

SELECT c.oid::pg_catalog.regclass "oid", c.relname,
       pg_catalog.pg_get_expr (c.relpartbound, c.oid) "rel",
       c.relkind
  FROM pg_catalog.pg_class c,
       pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = $$TABLE_OID
ORDER BY pg_catalog.pg_get_expr (c.relpartbound, c.oid) = 'DEFAULT',
c.oid::pg_catalog.regclass::pg_catalog.text

-----

For each partition, information is displayed as for ordinary tables (COLUMN, INDEX, TRIGGER, SIZE OBJECT, STATISTICS), as well as

  • BOUND - a filter on the basis of which a decision is made to place a record in this partition

For foreign tables, the following is additionally displayed:

  • srvname - name of the remote server

  • ftoptions - remote table options (schema name on remote server and table name)

  • srvoptions - parameters of the remote server (server name or its IP, port and database name)

-> Request to retrieve data <-

SELECT ft.ftrelid,
       ft.ftserver,
       ft.ftoptions :: text,
       fs.srvname,
       fs.srvoptions :: text
  FROM pg_foreign_table ft,
       pg_foreign_server fs
where ft.ftserver = fs.oid
   and ft.ftrelid = $$OID_TABLE

-----

 
INDEX

List of indexes in the database grouped by schema

Additionally, for each index, information is displayed on the table name, the size of the index on disk and the list of columns by which the index is built

Index list

-> Request to retrieve data <-

SELECT c.relname as "Name",
       c2.relname as "TabName",
       ns.nspname "Schema"
  FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_namespace ns ON c2.relnamespace = ns.oid
WHERE c.relkind IN ('i')
AND n.nspname = $$SCHEMA_NAME
ORDER BY 1

-----

Index size

-> Request to retrieve data <-

select pg_size_pretty (pg_relation_size ($$SCHEMA_NAME.$$INDEX_NAME));

-----

 

List of columns by which the index is built

-> Request to retrieve data <-

select
       t.relname as table_name,
       i.relname as index_name,
       array_to_string(array_agg(a.attname), ',') as column_names
  from
       pg_class t,
       pg_class i,
       pg_index ix,
       pg_attribute a
where
       t.oid = ix.indrelid
   and i.oid = ix.indexrelid
   and a.attrelid = t.oid
   and a.attnum = ANY (ix.indkey)
   and t.relkind = 'r'
   and i.relname = $$INDEX_NAME
   and i.relnamespace = (select oid from pg_namespace where nspname = $$SCHEMA_NAME)
group by
t.relname,
i.relname

-----

 
tree view - index.jpg
VIEW

List of views in the database grouped by schema

tree view - view.jpg

Menu

  • Copy name - copy the name of the view to the clipboard

  • Source - generate view creation code

  • Data - display view data (limit 50)

List of views

-> Request to retrieve data <-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
where relnamespace = (select oid from pg_catalog.pg_namespace where nspname = $$SCHEMA_NAME)
   and relkind in ('v')

order by 2

-----

Source

-> Request to retrieve data <-

select view_definition from information_schema.views where table_schema = $$SCHEMA_NAME and table_name = $$VIEW_NAME

-----

 
MATERIALIZED VIEW

 

List of materialized views in the database grouped by schema

tree view - materialized view.jpg

Menu:

  • Copy name - copy the name of the materialized view to the clipboard

  • Refresh matview - generate and display a command to update the materialized view data into the editor

  • Source - generate the materialized view creation code

  • Data - display data from materialized view (limit 50)

List of materialized views

-> Request to retrieve data <-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
where relnamespace = (select oid from pg_catalog.pg_namespace where nspname = $$SCHEMA_NAME
   and relkind in ('m')
order by 2

-----

Source

-> Request to retrieve data <-

select definition from pg_catalog.pg_matviews m where schemaname = $$SCHEMA_NAME and matviewname = $$MATVIEWNAME

-----

 
FUNCTION

List of functions in the database grouped by schema

Menu

  • Get code - extract the code for creating the selected function and add it to the query editor window

Feature List

-> Request to retrieve data <-

SELECT p.oid, n.nspname AS schema_name
, p.proname AS function_name
, pg_get_function_arguments (p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = $$SCHEMA_NAME
and prokind in ('f', 'a', 'w')
order by n.nspname

-----

Source

-> Request to retrieve data <-

select pg_get_functiondef ($$OID_FUNCTION)

-----

tree view - function.jpg
 
PROCEDURE

List of procedures in the database grouped by schema.

The node is displayed for PostgreSQL version 11 and later.

tree view - procedure.jpg

Menu

  • Get code - extract the code for creating the selected function and add it to the query editor window

Feature List

-> Request to retrieve data <-

SELECT p.oid, n.nspname AS schema_name
, p.proname AS function_name
, pg_get_function_arguments (p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = $$SCHEMA_NAME
and prokind in ('p')
order by n.nspname

-----

Source

-> Request to retrieve data <-

select pg_get_functiondef ($$OID_PROCEDURE)

-----

 
TRIGGER

List of triggers in the database grouped by schema

tree view - trigger.jpg

Trigger List

-> Request to retrieve data <-

select t.trigger_name,
       t.event_manipulation,
       t.event_object_schema || '.' ||                   t.event_object_table "table",
       t.action_order,
       t.action_condition,
       t.action_statement,
       t.action_orientation,
       t.action_timing,
       t.action_reference_old_table,
       t.action_reference_new_table,
       t.action_reference_old_row,
       t.action_reference_new_row,
       t.created
  from information_schema.triggers t
where t.trigger_schema = $$SCHEMA_NAME

-----

 
SEQUENCE

List of sequence generators in the database grouped by schemes

tree view - sequence.jpg

Sequence list

-> Request to retrieve data <-

select s.sequence_name,
       s.data_type,
       s.numeric_precision,
       s.numeric_precision_radix,
       s.numeric_scale,
       s.start_value,
       s.minimum_value,
       s.maximum_value,
       s.increment,
       s.cycle_option
  from information_schema.sequences s
where s.sequence_schema = $$SCHEMA_NAME

-----

 
DOMAIN

List of domains in the database grouped by schema

PostgreSQL tree view - domain.jpg

Domain list

-> Request to retrieve data <-

select d.domain_name,
       d.data_type,
       d.character_maximum_length,
       d.character_octet_length,
       d.character_set_catalog,
       d.character_set_schema,
       d.character_set_name,
       d.collation_catalog,
       d.collation_schema,
       d.collation_name,
       d.numeric_precision,
       d.numeric_precision_radix,
       d.numeric_scale,
       d.datetime_precision,
       d.interval_type,
       d.interval_precision,
       d.domain_default,
       d.udt_catalog,
       d.udt_schema,
       d.udt_name,
       d.scope_catalog,
       d.scope_schema,
       d.scope_name,
       d.maximum_cardinality,
       d.dtd_identifier
  from information_schema.domains d
where d.domain_schema = $$SCHEMA_NAME

-----

 
EVENT TRIGGER

List of event triggers in the database

List of event triggers

-> Request to retrieve data <-

select t.oid,
       t.evtevent,
       p.proname,
       t.evtname,
       p.oid "proc_oid",
       case evtenabled when 'O' then 'Origin'
            when 'D' then 'Disabled'
            when 'R' then 'Replica'
            when 'A' then 'Always'
       end "Modes"
  from pg_catalog.pg_event_trigger t,
       pg_catalog.pg_proc p
where t.evtfoid = p.oid

-----

 
PostgreSQL tree view - event trigger.jpg
EXTENSION

List of installed add-ons

PostgreSQL tree view - extension.jpg

List of event triggers

-> Request to retrieve data <-

select t.oid,
       t.evtevent,
       p.proname,
       t.evtname,
       p.oid "proc_oid",
       case evtenabled when 'O' then 'Origin'
           when 'D' then 'Disabled'
           when 'R' then 'Replica'
           when 'A' then 'Always'
       end "Modes"
  from pg_catalog.pg_event_trigger t,
       pg_catalog.pg_proc p
where t.evtfoid = p.oid

-----

 

Menu

  • View default manager - open the default add-on installation manager

PostgreSQL - view default extension.jpg
 

 

The manager allows you to install / remove add-ons by default.

FOREIGN SERVER

Third party server. Used in PostgreSQL DBMS to connect to external servers.

PostgreSQL tree view - foreign server.jp

List of third party servers

-> Request to retrieve data <-

select oid,
       srvname,
       srvtype,
       srvversion,
       srvacl::text,
       srvoptions::text
from pg_catalog.pg_foreign_server

-----

wrapper

-> Request to retrieve data <-

select w.oid,
       w.fdwname,
(select u.usename from pg_catalog.pg_user u where u.usesysid = w.fdwowner) "owner",
(select p.proname from pg_catalog.pg_proc p where p.oid = w.fdwhandler) "proc",
(select p.proname from pg_catalog.pg_proc p where p.oid = w.fdwvalidator) "validator",
w.fdwacl::text,
w.fdwoptions::text
  from pg_catalog.pg_foreign_data_wrapper w,
       pg_catalog.pg_foreign_server s
where s.oid = $$OIDSERVER
   and s.srvfdw = w.oid

-----

 

Tables using this "external server"

-> Request to retrieve data <-

SELECT (select (select n.nspname from pg_catalog.pg_namespace n where n.oid = c.relnamespace) || '.' || relname
          from pg_catalog.pg_class c
         where c.oid = f.ftrelid) "table_name",
       ftrelid,
       ftserver,
       ftoptions::text
  FROM pg_foreign_table f
where ftserver = $$OID_SERVER

-----

MAPPING

-> Request to retrieve data <-

SELECT (select (select n.nspname from pg_catalog.pg_namespace n where n.oid = c.relnamespace) || '.' || relname
          from pg_catalog.pg_class c
         where c.oid = f.ftrelid) "table_name",
       ftrelid,
       ftserver,
       ftoptions::text
  FROM pg_foreign_table f
where ftserver = $$OID_SERVER

-----

 
STANDBY STATUS

Status of the processes of broadcasting operations to the backup server

[CerebroSQL] Tree object for PostgreSQL - standby standby.jpg

List of session replication operations

-> Request to retrieve data <-

SELECT pid,
       usesysid,
       usename,
       application_name,
       client_addr::text,
       client_hostname::text,
       client_port,
       backend_start,
       backend_xmin,
       state,
       sent_lsn::text,
       write_lsn::text,
       flush_lsn::text,
       replay_lsn::text,
       write_lag::text,
       flush_lag::text,
       replay_lag::text,
       sync_priority,
       sync_state,
       reply_time,
       pg_current_wal_lsn()::text "current_wal_lsn"
  FROM pg_stat_replication

-----

 
TABLESPACE

List of created tablespaces

PostgreSQL tree view - tablespace.jpg

List of tablespaces

-> Request to retrieve data <-

SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid (spcowner) AS "Owner",
pg_catalog.pg_tablespace_location (oid) AS "Location",
pg_catalog.array_to_string (spcacl, E '\ n') AS "Access privileges",
spcoptions::text AS "Options",
pg_catalog.pg_size_pretty (pg_catalog.pg_tablespace_size (oid)) AS "Size",
pg_catalog.shobj_description (oid, 'pg_tablespace') AS "Description"
FROM pg_catalog.pg_tablespace
ORDER BY 1

-----

By default, the size of the space is not displayed. To enable output, set the "Show the size of the db" switch to "List settings"

 
ROLES

List of roles in the PostgreSQL cluster. The role differs from the user only in the ability to connect to the cluster.

PostgreSQL tree view - roles.jpg

List of groups (roles)

-> Request to retrieve data <-

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

-----

 
USERS

List of database users (roles that are allowed to connect to the PostgreSQL cluster)

PostgreSQL tree view - users.jpg

a list of users

-> Request to retrieve data <-

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

-----

 

PostgreSQL Monitor

The main tool for managing and monitoring the operation of the PostgreSQL cluster

To run, double-click on "MONITOR" or "PARAMETER"

DASHBOARD

Basic information on the load on the database. The data is updated 1 time per second.

  • Server session - information on sessions (total, active, idle)

  • Transaction per second - information on transactions (active, committing data, performing a rollback)

  • Tuples In - data on operations that change data (insert, update, delete)

  • Tuples Out - data on operations to retrieve data from the database (Number of rows returned by queries in this database; Number of rows returned by queries in this database)

  • Block I/O - the number of blocks read from disk or found in memory

Use the "Base statistics" drop-down list to select a base for displaying statistics. Available: base name or ALL - for all in total

Data collection is controlled via the "Running" switch

 
PostgreSQL - Monitor - Dashboard.jpg

-> Request to retrieve data <-

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
) "one",
(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
) "one",
(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
) "one",
(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
)"one",
(SELECT 'Hits' "Name2", sum (blks_hit) "COUNT2" FROM pg_stat_database
where datname = $$DB_NAME
) "2",
(SELECT '' "Name3", 0 "COUNT3") "3"
) t

-----

SESSION LIST

Cluster session manager.

Data update is performed manually.

 
PostgreSQL - Monitor - session list.jpg
Control elements

Drop-down list "Query type" - the type of SQL query used to retrieve data

  • Version> = 10

  • Version <10

Version> = 10 - for bases version 10 and older

-> Request to retrieve data <-

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 - version bases less than 10

-> Request to retrieve data <-

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

-----

PostgreSQL - Monitor - session list - button

Update the data

End the execution of the current request without breaking the connection ( select pg_cancel_backend ($$ PID) )

End current session ( select pg_terminate_backend ($$PID) )

 

When a session is selected in the "Session list", the current SQL query in the session is displayed in the "SQL Text" field.

When switching to the "Query plan" tab, the query execution plan is displayed

LOCK

Database locks

PostgreSQL - Monitor - lock.jpg

-> Request to retrieve data <-

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

-----

When a line is selected in the "Lock list" in the "Process" block, detailed information on the process is displayed.

Double-clicking on the line in the "Process" field displays the request text in the session

Server statistics

Various work statistics, useful information

 

Database stats page

 
PostgreSQL - Server statistics - db stats.jpg

-> Request to retrieve data <-

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

-----

Page "PG_CONFIG DATA"

Data from the pg_config system table

 
[CerebroSQL] PostgreSQL - pg_config.jpg
 

-> Request to retrieve data <-

select * from pg_config

-----

Page "pg_file_settings"

Data from the pg_file_settings system table

[CerebroSQL] PostgreSQL - pg_file_settings.jpg
 
[CerebroSQL] PostgreSQL - pg_language.jpg

-> Request to retrieve data <-

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

-----

Language support page

List of supported programming languages

-> Request to retrieve data <-

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 page

The page displays information about the quality of the autovacuum processes. The percentage in the "Percentage before freezing" field close to zero means that the auto vacuum does not have time to clear the tables and there is a high probability of a situation in which it will be necessary to transfer the database to single-user mode and perform vacuum full.

With a large number of tables with a low percentage, it is necessary to analyze the values ​​of the auto vacuum parameters for their change to more aggressive values.

 
[CerebroSQL] PostgreSQL - Percentage bef

-> Request to retrieve data <-

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 Cluster Parameter Editor

 
[CerebroSQL] PostgreSQL - parameter editor.jpg

The "Filter" field is used to filter the list of parameters by the entered text. The text is searched for in all columns and in any part of the line

The "Category" field is used for block filtering the list

Change parameter value

  • highlight the parameter on the "Server parameter" tab

  • enter a new value in the "Value" field

  • click the "Apply" button

-> Request to retrieve data <-

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

-----

Change value

-> Request to retrieve data <-

alter system set $$PARAMETER_NAME = $$NEW_VALUE

SELECT pg_reload_conf()

-----

NOT USING INDEX

The list of indices to which there were no hits or their number is less than specified in the "show indices with hit less" field

 
[CerebroSQL] PostgreSQL - list index not

-> Request to retrieve data <-

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

-----

 

Control buttons

  • Execute current SQL - Execute the currently selected query or all queries in the sheet

[CerebroSQL] PostgreSQL list control button

Execute - similar to clicking a button

To run the script from a file - execute the script from disk. The script is not loaded into the editor.

Check the script file for validate - check the script on disk for correctness without executing it

Show a list of command for execute - parse commands in the editor into execution blocks. The internal code and text of a single command is displayed

CerebroSQL for Oracle - execute menu
  • Commit - commit changes

  • Rollback - roll back changes made to data by the last requests

  • Stop execute - stop execution of the current request

  • Close open cursor - close the current dataset

  • Show plan - show the execution plan of the selected query

[CerebroSQL] PostgreSQL query plan execu

-> Request to retrieve data <-

EXPLAIN VERBOSE <query>

-----

  • Export data in file - exporting grid data (visible part of the data) to a file on disk in:

CerebroSQL for Oracle - export data

Export data in XLS - export data to a file in XLS format

Export data in TXT - export data to a file in TXT format

Export data in CSV - export data to a file in CSV format

Export data in HTML - export data to a file in HTML format

Export data in RTF - export data to a file in RTF format

  • Save current list in file - save the contents of the current sheet to a file on disk

  • Open file - open file manager

CerebroSQL for Oracle - open file
  • List saving code - open the manager for creating code blocks. Code blocks are code constructs saved in the program and can be accessed from the drop-down menu of the button

CerebroSQL - code block

Creating a block of code

  1. Press the "New" button

  2. From the drop-down list "Parent name" select the name of the root node. The root node is also the parent of the menu. To add a new entry to the list, click the "Add new parent" button and enter new values

  3. From the drop-down list "Page type" select the type of DBMS for which the code block should be displayed in the list

  4. In the "Code" field, enter the text of the code block, this text is added to the editor

  5. Click the "Save" button

CerebroSQL - code block saved
[CerebroSQL] Query block add list.jpg
 
  • Project manager - open the project manager

Workspace

 

This is a field on the sheet for entering queries / commands.

Splitting the code into its component parts

The engine of the query editor, before executing commands, parses the code, identifying the constituent parts.

To separate the code, special characters are used (; - semicolon, / - slash, go - keyword on a separate line).

The program supports two code syntaxes:

  • Classic Oracle syntax: a symbol is used to separate commands from each other; (semicolon) except for the create, begin, declate commands - these commands are separated from others by the / (slash) character on a separate line

  • MSSQL Server syntax: use the go keyword on a separate line to separate commands from each other

Any of two syntaxes are allowed, including both at the same time

 
[CerebroSQL] PostgreSQL query execute.jp
 

Logging of operations performed

The text of any command / query in the SQL language after execution is saved in the local storage (the Core database is the cer_history_query table). The following is recorded in the database: the text of the request / command actually executed by the program kernel, the execution status, the execution time, the number of changed lines, the DB messages (output)

CerebroSQL for Oracle - save query execute

In addition to saving data to the database, the progress of each command execution is recorded on the "Messages" tab of the sheet: the status of the commands execution, execution time, line numbers in the editor on which the executed command is located is displayed. These tabs are cleared before running command execution

CerebroSQL for Oracle - query executing messages
 

Icons on the left side of the window display the progress of execution (executed commands, current command, pending execution, query execution error, line with an error in the query)

Code hinter

The code hinter helps to speed up the writing of a query by suggesting the names of objects and columns.

For schemes - all objects of the scheme are displayed

[CerebroSQL] code compliter 1.jpg

For aliases of tables, views - a list of columns

[CerebroSQL] code compliter 2.jpg

As part of the object name - objects are displayed in the database whose name begins with the entered text. The tooltip is displayed by the key combination Crtl + Space

CerebroSQL for Oracle - code compliter l
Query aliases

In the program for queries, you can assign an alias and subsequently execute a query on it, see the details in the section "Query Alias ​​Manager"

Workspace menu
  • Copy - copy the selected text to the clipboard

  • Past - paste the contents of the clipboard into the editor window

  • Cut - cut the selected text

  • SelectAll - select all text on the stage

  • View table - viewing detailed information on the selected table

  • Get DDL - show the code for creating the selected table

  • Show command help - show help for the selected command

 
 
CerebroSQL for Oracle - Show selected command
[CerebroSQL] PostgreSQL help command.jpg
  • Show object data - show data of the selected table, view, synonym

  • Project manager -> Add object - create a page for the highlighted code in the current default project

CerebroSQL for Oracle - add code in project
  • Project manager -> Search object card - find the card of the selected object in the current project by default

  • Comment -> Add current line - add comment characters to the beginning of the current line

  • Comment -> Remove current line - remove comment characters at the beginning of the current line

  • Comment -> Add selected text - add comment characters to the beginning of each of the selected lines

  • Comment -> Remove selected text - remove comment characters at the beginning of each of the selected lines