Re: [GENERAL] Non-overlapping updates blocking each other

> > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote: > > I'd bet on the last one, especially since you found that the problem > > was a page-level lock. Did you look to see which relation the page > > lock was in? On Mon, Oct 16, 2017, at 12:34 PM, Seamus Abshere wrote: > The specific relation

Re: [GENERAL] Non-overlapping updates blocking each other

> Seamus Abshere writes: > > * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor... > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote: > I'd bet on the last one, especially since you found that the problem > was a page-level lock. Did you look to see which

Re: [GENERAL] Non-overlapping updates blocking each other

Seamus Abshere writes: > 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

Re: [GENERAL] Non-overlapping updates blocking each other

Did you try using SELECT FOR NO KEY UPDATE instead of SELECT FOR UPDATE? However: Seamus Abshere wrote: > 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. But we only

Re: [GENERAL] 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

Re: [GENERAL] Non-overlapping updates blocking each other

On Sun, Oct 15, 2017 at 8:01 AM, Seamus Abshere wrote: > > On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere > > > UPDATE [...] WHERE id BETWEEN 'ff00----' > AND > > > 'ff0f----' > > > and > > >

Re: [GENERAL] Non-overlapping updates blocking each other

> On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere > > UPDATE [...] WHERE id BETWEEN 'ff00----' AND > > 'ff0f----' > > and > > UPDATE [...] WHERE id BETWEEN 'f8c0----' AND > >

Re: [GENERAL] Non-overlapping updates blocking each other

Melvin Davidson writes: > On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer > wrote: >> Since when does Postgres lock the whole table during an update? > When the optimizer thinks it needs to do a TABLE SCAN! Sorry, but that's nonsense. More likely

Re: [GENERAL] Non-overlapping updates blocking each other

On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer wrote: > Melvin Davidson schrieb am 14.10.2017 um 17:32: > >> More than likely, the optimizer has determined that a table scan is best, >> in which case it will use a table lock. >> That means one updates will be blocking each

Re: [GENERAL] Non-overlapping updates blocking each other

Melvin Davidson schrieb am 14.10.2017 um 17:32: More than likely, the optimizer has determined that a table scan is best, in which case it will use a table lock. That means one updates will be blocking each other. Since when does Postgres lock the whole table during an update? -- Sent

Re: [GENERAL] Non-overlapping updates blocking each other

On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere wrote: > hi, > > I've got 2 updates on non-overlapping uuid (primary key) ranges. For > example: > > UPDATE [...] WHERE id BETWEEN 'ff00----' AND > 'ff0f----' > and >

[GENERAL] Non-overlapping updates blocking each other

hi, I've got 2 updates on non-overlapping uuid (primary key) ranges. For example: UPDATE [...] WHERE id BETWEEN 'ff00----' AND 'ff0f----' and UPDATE [...] WHERE id BETWEEN 'f8c0----' AND