Thanks for advice about queries, i guess you're talking about those from page
. Previously, I was using "blockedby" template from pgAdmin's "Server Status".
And you are absolutely right suspecting distributed transactions. The most interesting is that this database has been restored from backup using PITR set to 2015-10-11 12:00, and distributed transaction was prepared at '2015-10-11 12:00:00.130069'. From all that i draw a conclusion, that 1. while restoring from backup prepared transactions also restore; 2. they restore with their locks on database objects; 3. if you're restoring using PITR, you should rollback all the prepared transactions after database start.
Thank you, Tom.
Regards, Mikhail.
Mikhail <
bemewe@mail.ru> writes:
> 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:
> 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
