Обсуждение: pg_locks relation cant be found in pg_class
Hey,
I'm trying to map an oid of relations in pg_lock to a relation name with the following query :
select locktype,relname,database,mode,pid,granted from pg_locks,pg_class where locktype='relation' and pg_locks.relation=pg_class.oid;
However, I'm getting only relations of system catalogs and not other relations.. pg_locks contains rows for many relations, for example :
postgres=# select relation,count(*) from pg_locks where locktype='relation' group by relation order by count desc;
relation | count
----------+-------
16531 | 4
320587 | 3
16535 | 3
320585 | 3
relation | count
----------+-------
16531 | 4
320587 | 3
16535 | 3
320585 | 3
but in the result of my first query I dont get the relation name. For example :
postgres=# select count(*) from pg_class where oid in (16531,320587,16535);
count
-------
0
count
-------
0
any idea ?
On 2019-07-24 11:15, Mariel Cherkassky wrote: > However, I'm getting only relations of system catalogs and not other > relations.. pg_locks contains rows for many relations, for example : > postgres=# select relation,count(*) from pg_locks where > locktype='relation' group by relation order by count desc; > relation | count > ----------+------- > 16531 | 4 > 320587 | 3 > 16535 | 3 > 320585 | 3 > > but in the result of my first query I dont get the relation name. For > example : > postgres=# select count(*) from pg_class where oid in (16531,320587,16535); > count > ------- > 0 > "pg_locks provides a global view of all locks in the database cluster, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations, this will only work correctly for relations in the current database (those for which the database column is either the current database's OID or zero)." (from the documentation) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services