Re: Create index concurrently hanging with big table on pgsql 9.3.12 - Mailing list pgsql-general

Try the following query. See if it shows you if another transaction is blocking the needed locks to create the index.

SELECT
   w.query as waiting_query,
   w.pid as waiting_pid,
   w.usename as w_user,
   l.pid as blocking_pid,
   l.usename as blocking_user,
   t.schemaname || '.' || t.relname as tablename
   FROM pg_stat_activity w
   JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
   JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
   JOIN pg_stat_activity l ON (l2.pid = l.pid)
   JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
   WHERE w.waiting;