Re: hanged session on index creation

=?UTF-8?B?TWlraGFpbA==?= <bemewe(at)mail(dot)ru> writes:
> I'm runningsupposedly an easy operation - renaming index and my session hangs on it. pg_stat_activity shows that process is 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