Generally speaking, PostgreSQL makes locks less of an issue than in some other databases. However, high performance applications till need to be careful to miimize how often they take stronger locks. Here are some useful SQLs show how to get PostgreSQL9.4 locks information.

 


You can find details of how locking works in the PostgreSQL

http://www.postgresql.org/docs/current/static/explicit-locking.html


Or in the description of the pg_locks view,

http://www.postgresql.org/docs/current/static/view-pg-locks.html

 

Below I'll show you some useful SQLs to get postgreSQL9.2 locks information, I have another article for postgreSQL9.4 locks

Locks information overview

The information provided by pg_locks is very basic, here is an example tells you that gives you overview of locks information in running system

select locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,
cast(date_trunc('second',query_start) as timestamp) as query_start,
substr(query,1,60) as query
from pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
left outer join pg_namespace on (pg_namespace.oid = pg_class.relnamespace),
pg_stat_activity
where not pg_locks.pid=pg_backend_pid() and pg_locks.pid = pg_stat_activity.pid
order by virtualtransaction;
locktype | virtualtransaction | transactionid | nspname | relname | mode | granted | query_start | query
----------+--------------------+---------------+---------+---------+------+---------+-------------+-------

Transaction lock waits

Here is another example tells you transaction lock waits, shows excess of clients that are waiting for a lock to be granted.

select locked.pid as locked_pid, locker.pid as locker_pid,
locked_act.usename as locked_user,
locker_act.usename as locker_user, locked.virtualtransaction,
locked.transactionid,locked.locktype
from pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
where
locker.granted=true and
locked.granted=false and
locked.pid=locked_act.pid and
locker.pid=locker_act.pid and
(locked.virtualtransaction=locker.virtualtransaction
or locked.transactionid=locker.transactionid);
locked_pid | locker_pid | locked_user | locker_user | virtualtransaction | transactionid | locktype
------------+------------+-------------+-------------+--------------------+---------------+----------

Table lock waits, -- Clients waiting to acuire a lock on an extire table

One more for table lock waits, -- Clients waiting to acuire a lock on an extire table

select locked.pid as locked_pid, locker.pid as locker_pid, 
locked_act.usename as locked_user,
locker_act.usename as locker_user, locked.virtualtransaction,
locked.transactionid,locked.locktype
from pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
where
locker.granted=true and
locked.granted=false and
locked.pid=locked_act.pid and
locker.pid=locker_act.pid and
(locked.virtualtransaction=locker.virtualtransaction
or locked.relation=locker.relation);
locked_pid | locker_pid | locked_user | locker_user | virtualtransaction | transactionid | locktype
------------+------------+-------------+-------------+--------------------+---------------+----------