Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:postgresql:postgresql_basic_querys

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
dba:postgresql:postgresql_basic_querys [2023/09/20 10:08] – [List column index] dodgerdba:postgresql:postgresql_basic_querys [2024/03/15 09:18] – [show object source] dodger
Line 161: Line 161:
  
 ===== list functions ===== ===== list functions =====
 +
 +<code sql>
 +\df
 +</code>
 +
 +Or
 +<code sql>
 +SELECT n.nspname as "Schema",
 +  p.proname as "Name",
 + CASE p.prokind
 +  WHEN 'a' THEN 'agg'
 +  WHEN 'w' THEN 'window'
 +  WHEN 'p' THEN 'proc'
 +  ELSE 'func'
 + END as "Type"
 +FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 +WHERE pg_catalog.pg_function_is_visible(p.oid)
 +      AND n.nspname <> 'pg_catalog'
 +      AND n.nspname <> 'information_schema'
 +ORDER BY 1, 2;
 +</code>
 +
 +
 +or complete:
 +<code sql>
 +SELECT n.nspname as "Schema",
 +  p.proname as "Name",
 +  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 +  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 + CASE p.prokind
 +  WHEN 'a' THEN 'agg'
 +  WHEN 'w' THEN 'window'
 +  WHEN 'p' THEN 'proc'
 +  ELSE 'func'
 + END as "Type"
 +FROM pg_catalog.pg_proc p
 +     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 +WHERE pg_catalog.pg_function_is_visible(p.oid)
 +      AND n.nspname <> 'pg_catalog'
 +      AND n.nspname <> 'information_schema'
 +ORDER BY 1, 2;
 +</code>
 +Note:
 +  "Result data type" and "Argument data types" are normally multi-lined...
 +
 +==== Aggregate functions ====
 +
 <code sql> <code sql>
 \da \da
Line 188: Line 235:
  
 ===== list objects in schema ===== ===== list objects in schema =====
-Change ''<SCHEMA_NAME>'':+Change ''<SCHEMA_NAME>'' : 
 + 
 <code sql> <code sql>
 select n.nspname as schema_name, select n.nspname as schema_name,
Line 280: Line 329:
 WHERE TABLE_NAME   = 'table_name' WHERE TABLE_NAME   = 'table_name'
 order by ordinal_position order by ordinal_position
 +;
 +</code>
 +==== dba_tab_columns ====
 +
 +inefficient but who cares
 +<code sql>
 +SELECT * from
 +(
 +SELECT table_catalog DB_NAME,
 +    table_schema SCHEMA_NAME,
 +    table_name,
 +    column_name,
 +    ordinal_position,
 +    data_type,
 +    character_maximum_length,
 +    column_default,
 +    is_nullable
 +FROM information_schema.columns
 +order by ordinal_position
 +) as dba_tab_columns
 +WHERE COLUMN_NAME LIKE 'concession'
 ; ;
 </code> </code>
Line 298: Line 368:
  
 ===== List column index ===== ===== List column index =====
-aka `dba_ind_columns`:+==== dba_ind_columns ==== 
  
 <code sql> <code sql>
 SELECT SELECT
-    t.relname AS TABLE_NAME,+    np.nspname AS schema_name, 
 +    t.relname AS table_name,
     a.attname AS column_name,     a.attname AS column_name,
-    i.relname AS index_name,+    i.relname AS index_name
 FROM FROM
     pg_class t,     pg_class t,
     pg_class i,     pg_class i,
     pg_index ix,     pg_index ix,
-    pg_attribute a+    pg_attribute a, 
 +    pg_namespace np
 WHERE WHERE
     t.oid = ix.indrelid     t.oid = ix.indrelid
Line 315: Line 388:
     AND a.attrelid = t.oid     AND a.attrelid = t.oid
     AND a.attnum = ANY(ix.indkey)     AND a.attnum = ANY(ix.indkey)
 +    AND np.oid = t.relnamespace
     AND t.relkind = 'r'     AND t.relkind = 'r'
-    AND t.relname LIKE '%TABLE_NAME%'+    AND a.attname like '%COLUMN_NAME%' 
 +    AND i.relname like '%INDEX_NAME%' 
 +    AND t.relname like '%TABLE_NAME%'
 ORDER BY ORDER BY
     t.relname,     t.relname,
     a.attname;     a.attname;
-<code>+</code> 
 ===== show current schema ===== ===== show current schema =====
 <code> <code>
Line 337: Line 414:
 ===== show object source ===== ===== show object source =====
 Change ''FUNCTION_NAME'': Change ''FUNCTION_NAME'':
 +
 +
 <code sql> <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ; select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
Line 512: Line 591:
  
 ===== Locks ===== ===== Locks =====
 +This query will list all locks:
 <code sql> <code sql>
 SELECT psa.datid, SELECT psa.datid,
Line 527: Line 607:
 ; ;
 </code> </code>
 +
 +This query will list all //blocking sessions//, see [[https://wiki.postgresql.org/wiki/Lock_Monitoring|official documentation]]:
 +<code sql>
 +SET application_name='%your_logical_name%';
 +SELECT blocked_locks.pid     AS blocked_pid,
 +         blocked_activity.usename  AS blocked_user,
 +         blocking_locks.pid     AS blocking_pid,
 +         blocking_activity.usename AS blocking_user,
 +         blocked_activity.query    AS blocked_statement,
 +         blocking_activity.query   AS current_statement_in_blocking_process,
 +         blocked_activity.application_name AS blocked_application,
 +         blocking_activity.application_name AS blocking_application
 +   FROM  pg_catalog.pg_locks         blocked_locks
 +    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
 +    JOIN pg_catalog.pg_locks         blocking_locks 
 +        ON blocking_locks.locktype = blocked_locks.locktype
 +        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
 +        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 +        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 +        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 +        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 +        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 +        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 +        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 +        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 +        AND blocking_locks.pid != blocked_locks.pid
 +    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
 +   WHERE NOT blocked_locks.GRANTED;
 +</code>
 +
 +
 ===== Change user password ===== ===== Change user password =====
 <code sql> <code sql>
Line 532: Line 643:
 </code> </code>
  
 +
 +===== dump/export all users with password =====
 +<code bash>
 +pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1
 +</code>
  
 ===== List extensions installed ===== ===== List extensions installed =====
dba/postgresql/postgresql_basic_querys.txt · Last modified: 2024/04/19 07:37 by dodger