This crazy select will show you if there are some processes waiting for locks in database (not on whole server just database) in PostgreSQL 8.4. (Version for pg 9.3 is slightly different.)
Conflicting lock attempts will most probably happen if you use inheritance and select and insert into child tables.
Otherwise you will very probably see nothing if you just try it.
with locks as ( with sourcedata as ( select l.pid as connection_id, a.datname as "database", case when a.waiting is false then 'locks' else 'waits' end as query_lock_status, case when l.granted is true then 'held' else 'awaited' end as lock_granted, l.transactionid, l.virtualxid, l.virtualtransaction, l.locktype, l.mode as lock_mode, case relkind when 'r' then 'table' when 'i' then 'index' when 'S' then 'sequence' when 'v' then 'view' when 'c' then 'composite type' when 't' then 'TOAST table' else relkind||'?' end ||': '|| ns.nspname ||'.'||c.relname as "locked_object", a.current_query, a.xact_start as transaction_start, a.query_start, a.backend_start as connection_start from pg_locks l left join pg_database d on l.database = d.oid left join pg_class c on l.relation = c.oid left join pg_namespace ns on c.relnamespace = ns.oid left join pg_stat_activity a on l.pid = a.procpid where l.pid <> pg_backend_pid() and locktype not in ( 'virtualxid', 'tuple') ) select connection_id, "database", query_lock_status, lock_granted, transactionid, (select 'connection='||connection_id||', query= '||substr(current_query,1,100) from sourcedata src0 where src0.lock_granted = 'held' and src0.transactionid = src.transactionid and src0.connection_id<>src.connection_id) as lock_held_by, locktype, (select array_agg("locked_object") from (select * from sourcedata order by "locked_object") src0 where src0.connection_id=src.connection_id and src0.virtualtransaction=src.virtualtransaction and locktype='relation') as "locked_objects", virtualtransaction, current_query, transaction_start from sourcedata src group by query_lock_status, connection_id, "database", query_lock_status, lock_granted, transactionid, locktype, virtualtransaction, current_query, transaction_start--, case when ) select connection_id, "database", substr(current_query,1,40) as wants_to_run, "locked_objects" as "wants_to_lock", lock_held_by from locks where locktype<>'relation' and lock_granted = 'awaited' order by transaction_start desc