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

Поиск

Список

Период

Сортировка

От	Robert Anderson
Тема	Re: Create index concurrently hanging with big table on pgsql 9.3.12
Дата	9 мая 2016 г. 20:07:19
Msg-id	CAGrg8DVz+WQzMgaDKHZDJrwdMV=196oD4GPfzJmUkd47MLFL5w@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Create index concurrently hanging with big table on pgsql 9.3.12 (Melvin Davidson <melvin6925@gmail.com>)
Ответы	Re: Create index concurrently hanging with big table on pgsql 9.3.12 (Tom Lane <tgl@sss.pgh.pa.us>)
Список	pgsql-general

Дерево обсуждения

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;

---------------+-------------+--------+--------------+---------------+-----------

(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@gmail.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@commandprompt.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@postgresql.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.