Re: Create index concurrently hanging with big table on pgsql 9.3.12

Only one line returned:

postgres=# select * from pg_stat_activity where pid=3990;
-[ RECORD 1 ]----+----------------------------------------
datid | 16434
datname | flip
pid | 3990
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2016-05-07 11:48:39.218398-03
xact_start | 2016-05-07 11:48:43.417734-03
query_start | 2016-05-07 11:48:43.417734-03
state_change | 2016-05-07 11:48:43.417742-03
waiting | f
state | active
query | CREATE INDEX CONCURRENTLY index_texto
| ON flip_pagina_edicao
| USING hash
| (texto COLLATE pg_catalog."default");

postgres=#

2016-05-09 14:20 GMT-03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Robert Anderson <ranomail(at)gmail(dot)com> writes:
> > There aren't transactions blocking:
>
> > postgres=# SELECT
> > postgres-# w.query as waiting_query,
> > postgres-# w.pid as waiting_pid,
> > postgres-# w.usename as w_user,
> > postgres-# l.pid as blocking_pid,
> > postgres-# l.usename as blocking_user,
> > postgres-# t.schemaname || '.' || t.relname as tablename
> > postgres-# FROM pg_stat_activity w
> > postgres-# JOIN pg_locks l1 ON (w.pid = l1.pid and not l1.granted)
> > postgres-# JOIN pg_locks l2 on (l1.relation = l2.relation and
> l2.granted)
> > postgres-# JOIN pg_stat_activity l ON (l2.pid = l.pid)
> > postgres-# JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
> > postgres-# WHERE w.waiting;
> > waiting_query | waiting_pid | w_user | blocking_pid | blocking_user |
> > tablename
> >
> ---------------+-------------+--------+--------------+---------------+-----------
> > (0 rows)
>
> This test proves little, because that last JOIN will discard locks on
> non-table objects, and what CREATE INDEX CONCURRENTLY would be most
> likely to be blocked on is transaction VXIDs. However, since
> pg_stat_activity claims that "waiting" is false, probably there isn't
> anything in pg_locks. Still, it'd be better to do
> "select * from pg_stat_activity where pid = 3990" and be sure.
>
> regards, tom lane
>