Ciberterminal Wiki

User Tools

Site Tools

Trace: • Confinement wods
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/29 07:58] – [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>
Line 319: Line 390:
     AND np.oid = t.relnamespace     AND np.oid = t.relnamespace
     AND t.relkind = 'r'     AND t.relkind = 'r'
-    AND a.attname like '%obo%'+    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,
Line 341: 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 516: Line 591:
  
 ===== Locks ===== ===== Locks =====
 +This query will list all locks:
 <code sql> <code sql>
 SELECT psa.datid, SELECT psa.datid,
Line 531: 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 536: 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