CODEWITHSUNDEEP
postgresql
Gen Eva
Gen Eva

Reputation: 49

Query to find tables being busy at the moment in Postgresql database

Please, suggest query to find tables (or, as an additional extra option(s), may be other objects in DB) being busy at the moment of query in Postgresql database, i.e.:

of course, query activity itself should be excluded from result as it is service/tool/maintanance activity.

Thank you

Upvotes: 0

Views: 659

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

Are you looking for something like this?

SELECT l.relation::regclass AS tablename,
       CASE l.mode
          WHEN 'AccessShareLock' THEN 'select'
          WHEN 'AccessExclusiveLock' THEN 'altered'
          ELSE 'modified'
       END AS activity
FROM pg_locks AS l
   JOIN pg_database AS d
      ON l.database = d.oid
WHERE d.datname = current_database()
  AND l.relation IS NOT NULL
  AND pid <> pg_backend_pid();

Upvotes: 1

Related Questions