Differences

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

--- dba:postgresql:postgresql_basic_querys [2023/10/09 09:11] – [list functions] dodger
+++ dba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] dodger
@@ Line 235: / Line 235: @@
 ===== list objects in schema =====
-Change ''<SCHEMA_NAME>'':
+Change ''<SCHEMA_NAME>'' :
 <code sql>
 select n.nspname as schema_name,
@@ Line 412: / Line 414: @@
 ===== show object source =====
 Change ''FUNCTION_NAME'':
 <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
@@ Line 587: / Line 591: @@
 ===== Locks =====
+This query will list all locks:
 <code sql>
 SELECT psa.datid,
@@ Line 602: / Line 607: @@
 ;
 </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 =====
 <code sql>
@@ Line 607: / Line 643: @@
 </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 =====
@@ Line 844: / Line 885: @@
-====== Section: EMPTY ======
+====== Section: XML (tables) ======
+This is an example on how to use XMLTABLE function to extract data from a XML column:
+<code sql>
+    select
+        bdef.build_definition_id as build_definition_id,
+        tasks.task_definition_id as task_definition_id,
+        tasks.user_description as user_description,
+        tasks.is_enabled as is_enabled,
+        tasks.plugin_key as plugin_key,
+        tasks.finalising as finalising,
+        config_items.item_key as item_key,
+        config_items.item_value as item_value
+    FROM
+        build_definition bdef,
+        XMLTABLE('//taskDefinition' PASSING XMLPARSE(DOCUMENT xml_definition_data)
+                  COLUMNS
+                      task_definition_id integer PATH 'id',
+                      user_description text PATH 'userDescription',
+                      is_enabled boolean PATH 'isEnabled',
+                      plugin_key text PATH 'pluginKey',
+                      finalising boolean PATH 'finalising',
+                      config XML PATH 'config') AS tasks
+    left join XMLTABLE('//item' PASSING tasks.config COLUMNS item_key text PATH 'key',item_value text PATH 'value') AS config_items
+    on true
+    where xml_definition_data LIKE '%\/dip\/%'
+    order by 1
+;
+</code>
+====== Section: EMPTY ======
+<code sql>
-<code>
 </code>