What makes me suspect it's a lock on the parent table is the word "ShareLock" in the logs. A SELECT ... FOR UPDATE statement shouldn't place that type of lock on the table it's selecting.
This looks 100% like a normal, multi-row deadlock situation. The CONTEXT shows it is a row-level problem:
I'm not sure I understand. The two queries are referencing separate, single rows in the child table (primary keys payroll_endpoint.id = 1 and 2), so where does the multi-row bit come in? Is it because the two parent tables are also being locked, in possibly different orders?
CONTEXT: while locking tuple (7,15) in relation “paiyroll_endpoint”
The ShareLocks are on the transaction, because each backend is waiting for the other to finish their transaction, and thus release the lock(s) it may have.
If you implement Tom's suggestion, I think you will find that this is a classic failing to lock the rows in the same order problem.
I'm not seeing "Tom's suggestion". Is there a way to specify that the parent tables need not be locked? Perhaps by omitting them from the query?
