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?
