Posts

Postgres: Timeouts

Image
 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...
Post a Comment
Read more

Postgres: How to identify and release locks lock

 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...
Post a Comment
Read more

Postgres: Role and group memberships

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) 
Post a Comment
Read more

Postgres: To see every table a specific user has been granted access to

 SELECT grantee, privilege_type, table_name  FROM information_schema.role_table_grants  WHERE grantee = 'YOUR_USER_NAME'; -- WHERE grantee = 'edduser' -- order by table_name;
Post a Comment
Read more
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'     ...
Post a Comment
Read more

Postgres: To gernerate list of schemas

  select distinct table_schema from information_schema . tables ORDER BY table_schema ;
Post a Comment
Read more

Oracle: Check Patching backup files

  Hello How to check the patching backup files Check what patches OPatch thinks are installed:   $ORACLE_HOME /OPatch/opatch lsinventory $ORACLE_HOME /OPatch/opatch lsinventory  -detail   Oracle documents  lsinventory  as the standard way to verify applied patches and Oracle home inventory state. Check whether rollback files exist under  .patch_storage :   cd   $ORACLE_HOME ls   -ld  .patch_storage find  .patch_storage  -maxdepth   2   -type  d |  sort du  -sh  .patch_storage   If the patch was applied normally, you should see patch-related content there, because OPatch saves the replaced files in  $ORACLE_HOME/.patch_storage  during installation. Check OPatch logs and history:   ls   -ltr   $ORACLE_HOME /cfgtoollogs/opatch tail  -100   $ORACLE_HOME /cfgtoollogs/opatch/history.txt   Regards Martin Gomez 
Post a Comment
Read more