Re: Non-overlapping updates blocking each other

On 2017-10-14 16:32:33 Tom Lane wrote:
> More likely explanations for the OP's problem involve foreign key
> constraints that cause two different row updates to need to lock
> the same referenced row, or maybe he's using some index type that
> has greater locking demands than a btree, or he's using serializable

hi Tom,

I hesitate to share my query and indexes because it makes this question
seem more esoteric than I think it really is... but here we go.

* Version 9.6.3.
* I don't have any foreign key constraints.
* I don't use serializable.
* My update query is very careful to stay in an id range. [1]
* I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...

My current theory is that, since the table is not clustered by id, rows
with very distant ids get stored in the same page, and the whole page is
locked during an update. Or something.

[1] Update SQL:
https://gist.github.com/seamusabshere/d04dad259e383c13f5559241d2fcad70

[2] Indexes:
https://gist.github.com/seamusabshere/acba364b97e1dd221a589b1aaf22bddb

Thanks,
Seamus