Re[2]: [GENERAL] hanged session on index creation

Thanks for advice about queries, i guess you're talking about those from page  https://wiki.postgresql.org/wiki/Lock_Monitoring . 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.

>Вторник, 13 октября 2015, 9:29 -04:00 от Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>=?UTF-8?B?TWlraGFpbA==?= < bemewe(at)mail(dot)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