hanged session on index creation

From: Mikhail <bemewe(at)mail(dot)ru>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: hanged session on index creation
Date: 2015-10-13 10:21:40
Message-ID: 1444731700.391482285@f100.i.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry for my previous message, it went to wrong thread. Creating this as a new one.

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}}, 1 and 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.

Renaming and even creationg other indexes in mytstdb  takes milliseconds.

Is there any ideas what is happening inside the database?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-10-13 13:29:44 Re: hanged session on index creation
Previous Message Mikhail 2015-10-13 10:00:38 hanged session on index creation