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

From Tom Lane
Subject Re: Create index concurrently hanging with big table on pgsql 9.3.12
Date
Msg-id 21126.1462814437@sss.pgh.pa.us
Whole thread Raw
In response to Re: Create index concurrently hanging with big table on pgsql 9.3.12  (Robert Anderson <ranomail@gmail.com>)
Responses Re: Create index concurrently hanging with big table on pgsql 9.3.12  (Robert Anderson <ranomail@gmail.com>)
List pgsql-general
Robert Anderson <ranomail@gmail.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


pgsql-general by date:

Previous
From: Robert Anderson
Date:
Subject: Re: Create index concurrently hanging with big table on pgsql 9.3.12
Next
From: Robert Anderson
Date:
Subject: Re: Create index concurrently hanging with big table on pgsql 9.3.12