How to debug ShareLock in Postgres

Question

I see quite a few cases where the following occurs in my Postgres log:

LOG:  process x still waiting for ShareLock on transaction y after 1000.109 ms 
DETAIL:  Process holding the lock: z. Wait queue: x.
CONTEXT:  while inserting index tuple (a,b) in relation "my_test_table"
    SQL function "my_test_function" statement 1 
...
LOG:  process x acquired ShareLock on transaction y after 1013.664 ms
CONTEXT:  while inserting index tuple (a,b) in relation "my_test_table"

I am using Postgres 9.5.3 . In addition, I work for Heroku, so I don’t have access to the fine-grained debugging tools only for the superuser.

I am wondering what is the best way to debug such a problem taking into account these limitations and the fact that each individual lock is relatively temporary (usually 1000-2000 ms).

Things I tried:

• Monitoring (and joining for context). pg_locks pg_class
• The investigation . pageinspect
• , pgbench . (, - , ).

, ( > 1), , , , . , , , .

Answer 1

, ShareLock .

, , /, . "". , , PostgreSQL ExclusiveLock . , , ShareLock , , ExclusiveLock /. .

, () INSERT a UNIQUE PRIMARY KEY , ​​/ . , - , , , / ​​/.

, . proc "x" "my_test_table" , proc "y" xact "z", , .

, - upsert . , - /, , , .

Answer 2

, .