=?UTF-8?B?TWlraGFpbA==?= <bemewe@mail.ru> writes:
> I'm running supposedly an easy operation - renaming index and my session hangs on it. pg_stat_activity shows that
processis active and is waiting for lock:
> locktype ¦ relation ¦ relname ¦ virtualtransaction ¦ pid ¦ mode ¦ granted ¦ fastpath ¦ blockedby
>
---------+----------+---------------------------------------------------+--------------------+-------+---------------------+---------+----------+----------
> relation ¦ 28756 ¦ fki_eb_order_details_acquiring_order_details_guid ¦ -1/222236974 ¦ NULL ¦ AccessShareLock ¦ t ¦
f¦ NULL
> relation ¦ 28756 ¦ fki_eb_order_details_acquiring_order_details_guid ¦ 11/2284 ¦ 15179 ¦ AccessExclusiveLock ¦ f ¦
f¦ NULL
> So, it seems like no other sessions blocks my session.
Huh? Whatever's holding that AccessShareLock on
fki_eb_order_details_acquiring_order_details_guid is what's blocking your
RENAME. The pid = NULL bit is interesting; I think that must mean it's
a prepared transaction. So check for uncommitted prepared transactions...
So one thing wrong with that "blockedby" subselect is that you can't
tell "not blocked" from "blocked by prepared transaction". Also worth
noting is that that test for matching lock targets is seriously sloppy.
Better versions can be found in our mail list archives --- I seem to
remember concluding that a ROW(...) IS NOT DISTINCT FROM ROW(...) test
that lists locktype and all the other lock-target-defining fields is the
best way to write it.
regards, tom lane