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

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;

On Mon, May 9, 2016 at 11:50 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>
wrote:

> On 05/09/2016 05:04 AM, Robert Anderson wrote:
>
>> Hi,
>>
>> We are trying to create a index concurrently but, at least apparently,
>> it hangs in a infinite loop and never ends.
>>
>
> Apparently how?
>
> How long did you wait?
>
> JD
>
> --
> Command Prompt, Inc. http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.