Groovy DBA

 To determine or check timeout settings in PostgreSQL, you can use the SHOW command for individual parameters or query the pg_settings system view for a comprehensive list.  1. View Current Timeout Settings  You can check the current value for any specific timeout parameter in your active session using the following SQL commands:  Query Timeout: SHOW statement_timeout; (Stops queries that run too long) Lock Timeout: SHOW lock_timeout; (Stops a command if it can't acquire a lock quickly enough) Idle Transaction Timeout: SHOW idle_in_transaction_session_timeout; (Closes sessions left open but inactive within a transaction) Idle Session Timeout: SHOW idle_session_timeout; (Closes sessions that are completely inactive) Transaction Timeout (v17+): SHOW transaction_timeout; (Sets a limit for the entire duration of a transaction)  2. Search All Timeout Parameters To find every setting that contains "timeout" in its name and see its current value, use the pg_settings vi...

 1. View Locks on a Specific Table  Use this query to see all active locks for a specific table by name: sql SELECT      locktype,      relation::regclass AS table_name,      mode,      granted,      pid  FROM pg_locks  WHERE relation = 'your_table_name'::regclass; granted: t means the process has the lock; f means it is waiting for it. mode: The type of lock (e.g., AccessShareLock for selects, AccessExclusiveLock for schema changes).    2. Identify "Who is Blocking Whom" To find which session is holding a lock and preventing others from proceeding, use the pg_blocking_pids function:  sql SELECT      pid,      usename,      pg_blocking_pids(pid) AS blocked_by,      query AS blocked_query FROM pg_stat_activity  WHERE cardinality(pg_blocking_pids(pid)) > 0; The blocked_by column will show the PID of the process ho...

Role and Group Memberships Since users often inherit permissions from groups (roles), use this to see what roles they belong to:     SELECT r.rolname as username,         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) as member_of FROM pg_catalog.pg_roles r WHERE r.rolname = 'edduser';  Understanding Privilege Abbreviations When using commands like \dp, you will see a string of letters like arwdDxt. These correspond to: r: SELECT (read) w: UPDATE (write) a: INSERT (append) d: DELETE D: TRUNCATE x: REFERENCES t: TRIGGER U: USAGE (for schemas/sequences) C: CREATE (for databases/schemas) c: CONNECT (for databases) 

 SELECT grantee, privilege_type, table_name  FROM information_schema.role_table_grants  WHERE grantee = 'YOUR_USER_NAME'; -- WHERE grantee = 'edduser' -- order by table_name;

TABLES:-   SELECT table_schema, table_name, table_type FROM information_schema.tables ORDER BY table_schema, table_name; FUNCTIONS: SELECT routine_schema, routine_name FROM information_schema.routines WHERE routine_type = 'FUNCTION' ORDER BY routine_schema, routine_name; ALL OBJECTS SELECT n.nspname as schema_name,        c.relname as object_name,        CASE c.relkind          WHEN 'r' THEN 'table'          WHEN 'v' THEN 'view'          WHEN 'm' THEN 'materialized view'          WHEN 'i' THEN 'index'          WHEN 'S' THEN 'sequence'          WHEN 't' THEN 'TOAST table'          WHEN 'f' THEN 'foreign table'          WHEN 'p' THEN 'partitioned table'          WHEN 'I' THEN 'partitioned index'     ...

  select distinct table_schema from information_schema . tables ORDER BY table_schema ;