Re: BUG #12469: pg_locks shows locks held by pids not found i n pg_stat_activity or ps

Marko Tiikkaja <marko@joh.to> wrote:
> On 2015-01-09 19:07, Kevin Grittner wrote:

>> "kop@meme.com" <kop@meme.com> wrote:
>>> I found that after running a large transaction (for days, it's still
>>> running) and then running out of shared memory that pg_locks reports locks
>>> held by pids that do not seem to exist, either in ps output or in
>>> pg_stat_activity.
>>
>> That is not a bug.
>
> I find that really surprising.  What if the pid gets reused and you have
> entries in pg_locks with the pid that aren't actually held by that
> backend?  That's enough to drive anyone having to debug that mess insane.
>
> Can we not clear the pids or something, at least at backend exit?

Indeed we could.  There was some discussion of whether it would be
better to leave the pid in pg_locks so it could be matched up
against log entries which led up to the lock, or whether it should
be removed in case it lasted long enough to survive PID wrap-around
and thereby possibly confuse someone looking at the lock
information.  (It is just an "informational" column, not one used
to manage the serializable logic, so its presence or absence
doesn't affect the correctness of serializable behavior.)  The
argument that it was more useful to leave it there than to remove
it held sway.

It would be trivial to remove it on commit, but that would
eliminate its usefulness where there isn't any possibility of
ambiguity.  To remove it on connection close would require scanning
a list for matches, so I think it would add an O(N^2) performance
hit to closing a connection.  I guess the question is whether PID
wraparound occurs while a single database transaction is open often
enough to make it worthwhile add that overhead.  Also keep in mind
that there might still be server logs with the pid which could
usefully be matched against this value, even after PID wraparound
-- it's just that if the same pid had been reused for a new
connection you would have an ambiguous reference.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company