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

Hi,

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)

How long I'm waiting:

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_activity where query like 'CREATE%';
-[ 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=#

In attachment follows a strace sample of the running process.

2016-05-09 13:25 GMT-03:00 Melvin Davidson <melvin6925(at)gmail(dot)com>:

> 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.
>