Search Postgresql Archives

hanged session on index creation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Is there any ideas what is happening inside the database?

Regards, Mikhail


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux