Re: Connections hang indefinitely while taking a LWLockTranche buffer_content lock.

I managed to get a couple of hours to look at this this afternoon.

On Mon, Oct 23, 2017 at 10:39 AM, Chris Hanks
<christopher(dot)m(dot)hanks(at)gmail(dot)com> wrote:
> I'm not sure if you guys had noticed this already, but just in case,
> those two hung connections weren't making the inserts at exactly the
> same time - if you look at pg_stat_activity they executed about a day
> apart (since Heroku cycles the clients every 24 hours or so). And
> before I restarted the first DB that experienced the problem, there
> were ~30 backends built up over the course of a month. It seems like
> when one INSERT sticks, every following INSERT just stacks up on top
> of it, trying to take out the same lock.

Right. In both backtraces, we see that we're an inserter stuck on
getting an exclusive buffer lock on the buffer containing block 0, the
meta page block (GIN_METAPAGE_BLKNO). There is probably some
session/backend that has acquired two buffer locks in an order that is
inconsistent with these inserters, meaning that you get an
undetectable deadlock. (The only alternative to that theory is that
some backend sits on a meta page buffer lock for some other reason,
but that seems much less likely.)

The interesting question to my mind is: What backend is the other
backend that acquires buffer locks in an incompatible order, resulting
in this undetectable deadlock? What's it doing? (I worked for Heroku
until quite recently; I may be able to open a back channel, with
Chris' permission.)

I remember expressing befuddlement about commit e95680832854c over a
year ago, and never satisfying myself that it was correct [1]. I'm no
expert on GIN, so I dropped it. It feels like that might be relevant
here, since you seem to be using this GIN index with a queue table.
That usage pattern is one where entire leaf pages in indexes tend to
be routinely deleted and later recycled by VACUUM, at least with
B-Trees [2]. Whereas, in general I think B-Tree (and presumably GIN)
page deletion is fairly rare, since the entire page must be empty for
it to happen.

The follow up bugfix commit, e2c79e14, added a ConditionalLockPage()
to the insert ginInsertCleanup() path, while also adding a LockPage()
to the VACUUM path. In case you missed it, those are *heavyweight*
page lock acquisitions, not buffer lock acquisitions, which is pretty
unconventional (I though only hash still did that). Frankly, the
e2c79e14 fix seems kind of bolted on (though I don't want to focus on
that aspect right now).

[1] https://postgr.es/m/CAM3SWZSDxqDBvUGOoNm0veVOwgJV3GDvoncYr6f5L16qo8MYRg@mail.gmail.com
[2] https://brandur.org/postgres-queues
--
Peter Geoghegan