hanged session on index creation

Поиск
Список
Период
Сортировка
От Mikhail
Тема hanged session on index creation
Дата
Msg-id 1444730438.906622710@f89.i.mail.ru
обсуждение исходный текст
Ответ на How to get cursor query  (Mikhail <bemewe@mail.ru>)
Список pgsql-general
Hi,
I'm running supposedly an easy operation - renaming index and my session hangs on it. pg_stat_activity shows that process is active and is waiting for lock:

=# select application_name, waiting, state, query from pg_stat_activity where pid = 15179;
application_name │ waiting │ state  │ query
─────────────────┼─────────┼───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
psql             │       t │ active │ ALTER INDEX ebsm.fki_eb_order_details_acquiring_order_details_guid RENAME TO idx_tst;locks for that database:

=# select locktype, relation, c.relname, virtualtransaction, pid, mode, granted, fastpath,
(SELECT MIN(l1.pid)
FROM pg_locks l1
WHERE GRANTED
AND (relation IN (SELECT relation FROM pg_locks l2 WHERE l2.pid = l.pid AND NOT granted)
OR
transactionid IN (SELECT transactionid FROM pg_locks l3 WHERE l3.pid = l.pid AND NOT granted))) AS blockedby
from pg_locks l
JOIN pg_class c ON c.oid = l.relation
where l.database in (select oid from pg_database where datname = 'mytstdb');

locktype ¦ relation ¦ relname ¦ virtualtransaction ¦ pid ¦ mode ¦ granted ¦ fastpath ¦ blockedby
---------+----------+---------------------------------------------------+--------------------+-------+---------------------+---------+----------+----------
relation ¦ 3455     ¦ pg_class_tblspc_relfilenode_index ¦ 3/482 ¦ 24388 ¦ AccessShareLock ¦ t ¦ t ¦ NULL
relation ¦ 2663     ¦ pg_class_relname_nsp_index ¦ 3/482 ¦ 24388 ¦ AccessShareLock ¦ t ¦ t ¦ NULL
relation ¦ 2662     ¦ pg_class_oid_index ¦ 3/482 ¦ 24388 ¦ AccessShareLock ¦ t ¦ t ¦ NULL
relation ¦ 1259     ¦ pg_class ¦ 3/482 ¦ 24388 ¦ AccessShareLock ¦ t ¦ t ¦ NULL
relation ¦ 11187    ¦ pg_locks ¦ 3/482 ¦ 24388 ¦ AccessShareLock ¦ t ¦ t ¦ NULL
relation ¦ 28686    ¦ eb_order_details ¦ -1/222236974 ¦ NULL ¦ AccessShareLock ¦ t ¦ f ¦ NULL
relation ¦ 28756    ¦ fki_eb_order_details_acquiring_order_details_guid ¦ -1/222236974 ¦ NULL ¦ AccessShareLock ¦ t ¦ f ¦ NULL
relation ¦ 28689    ¦ pk_eb_order_details ¦ -1/222236974 ¦ NULL ¦ AccessShareLock ¦ t ¦ f ¦ NULL
relation ¦ 28756    ¦ fki_eb_order_details_acquiring_order_details_guid ¦ 11/2284 ¦ 15179 ¦ AccessExclusiveLock ¦ f ¦ f ¦ NULL
(9 rows)So, it seems like no other sessions blocks my session. (If it makes sense, the fields page, tuple, virtualxid, transactionid, classid, objid, objsubid have NULL values).
There is no activity on cpu/hdd and even no activity inside process

# strace -p 15179
Process 15179 attached - interrupt to quit
recvfrom(9, "Q\0\0\0\200ALTER INDEX ebsm.fki_eb_ord"..., 8192, 0, NULL, NULL) = 129
setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={1, 0}}, NULL) = 0
semop(16973888, {{2, -1, 0}}, 1) = -1 EINTR (Interrupted system call)
--- SIGALRM (Alarm clock) @ 0 (0) ---
rt_sigreturn(0x1c4f7260806a7) = -1 EINTR (Interrupted system call)
semop(16973888, {{2, -1, 0}}, 1and no further action within hours. Perf and top shows the same: no action performing by process.

The situation is reproducible: i can interrupt the process, run index renaming again and i'll get the same situation, described above.

Is there any ideas what is happening inside the database?

Regards, Mikhail

В списке pgsql-general по дате отправления:

Предыдущее
От: Lele Gaifax
Дата:
Сообщение: Re: Understanding "seq scans"
Следующее
От: Mikhail
Дата:
Сообщение: hanged session on index creation