What command returns the tables that are in LOCK in Postgresql?

Asked

Viewed 3,541 times

5

What command can I execute to return the tables that are in LOCK in the PostgreSQL.

Example query of lock: 

BEGIN; LOCK TABLE documento_sequencial  
     ...  
COMMIT;
postgresql

2 answers

3

SELECT
  l.locktype,
  t.relname,
  l.page,
  l.virtualtransaction,
  l.pid,
  l.mode,
  l.granted
FROM pg_locks l,
     pg_stat_all_tables t
WHERE l.relation = t.relid
AND t.relname NOT IN ('pg_class', 'pg_index', 'pg_namespace')
ORDER BY relation ASC;

locktype |relname              |page |virtualtransaction |pid  |mode                |granted |
---------|---------------------|-----|-------------------|-----|--------------------|--------|
relation |pg_database          |     |2/952099           |1944 |AccessShareLock     |true    |
relation |documento_sequencial |     |2/952099           |1944 |AccessExclusiveLock |true    |

coderwall

3


select c.relname, n.nspname, l.locktype, l.mode, l.granted, l.fastpath
from
    pg_locks l
    inner join
    pg_database d on l.database = d.oid
    inner join
    pg_class c on l.relation = c.oid
    inner join
    pg_namespace n on c.relnamespace = n.oid
where d.datname = 'cpn' and n.nspname = 'public'
;
 relname | nspname | locktype |        mode         | granted | fastpath 
---------+---------+----------+---------------------+---------+----------
 t       | public  | relation | AccessExclusiveLock | t       | f
(1 row)

https://www.postgresql.org/docs/current/static/catalogs.html

  • Thanks for the help @Clodoaldoneto, but when running this select it did not return any record.

  • 1

    @Oliveira Do you have a lock table? Use your base name instead of cpn and the name of the scheme if it is not public

  • returned the sequential document_table that is in lock, after changing the 'cpn' by the name of the base I am using, I did not change the name of the schema.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.