Обсуждение: BUG #14098: misleading message "out of shared memory" when lock table space exhausted

The following bug has been logged on the website:

Bug reference:      14098
Logged by:          John Lumby
Email address:      johnlumby@hotmail.com
PostgreSQL version: 9.5.2
Operating system:   linux 64-bit
Description:

If postgresql exhausts the space reserved for locks,  whose size is defined
by
 max_locks_per_transaction * (max_connections + max_prepared_transactions)
locks,
then it issues this message :


psql: FATAL:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

The words "out of shared memory" are misleading  (and ungrammatical,  and
somewhat contradict the HINT).
Shared mem space is not exhausted,   lock table space is exhausted.

Please change the message to

psql: FATAL:  lock table space is exhausted
HINT:  You might need to increase max_locks_per_transaction.

I believe all cases of this msg are in
 src/backend/storage/lmgr/lock.c

johnlumby@hotmail.com writes:
> If postgresql exhausts the space reserved for locks,  whose size is defined
> by
>  max_locks_per_transaction * (max_connections + max_prepared_transactions)
> locks,
> then it issues this message :

> psql: FATAL:  out of shared memory
> HINT:  You might need to increase max_locks_per_transaction.

> The words "out of shared memory" are misleading  (and ungrammatical,  and
> somewhat contradict the HINT).

They are also correct, unlike your proposed revision.  There isn't a hard
separation of lock table space from the generally-available shared memory
pool.

            regards, tom lane

=0A=
Thanks Tom=2C=0A=
=0A=
However Can you please explain a little more on this.=0A=
=0A=
Firstly=2C the documentation (Server Configuration=2C Lock Management) is v=
ery clear :=0A=
=0A=
_____________________________________________________=0A=
max_locks_per_transaction (integer)=0A=
=0A=
The shared lock table tracks locks on=0A=
max_locks_per_transaction * (max_connections + max_prepared_transactions) o=
bjects (e.g.=2C tables)=3B=0A=
=0A=
hence=2C *no more* than this many distinct objects can be locked at any one=
 time.=0A=
_____________________________________________________=0A=
=0A=
That apparently seems to support what I said and contradicts your assertion=
 -=0A=
depending on what you meant by "hard" separation.=0A=
=0A=
So if what you stated is correct=2C=A0 then two comments :=0A=
=0A=
=A0 .=A0=A0=A0 the documentation must be incorrect and should be corrected.=
=0A=
=A0 .=A0=A0=A0 can you please state exactly what is the limit for how many =
=0A=
=A0=A0=A0=A0=A0=A0 distinct objects can be locked at any one time=2C=A0 bas=
ed on configuration parameters.=0A=
=A0=A0=A0=A0=A0=A0 If this number can exceed max_locks_per_transaction * (m=
ax_connections + max_prepared_transactions)=2C=0A=
=A0=A0=A0=A0=A0=A0 then is this done by=0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 a) dynamically extending shared memory=0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 b) incursing into some other component's =
nominal portion of shared memory=2C=0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (the amount it says it needs dur=
ing postgresql startup when the total size of the=0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 initial shared memory segment=
 is being computed)=0A=
=A0=A0=A0=A0=A0=A0 If b)=2C=A0 then can other components equally incurse in=
to the nominal lock table space?=0A=
=0A=
=0A=
=0A=
Cheers=2C John=0A=
=0A=
                           =

John Lumby <johnlumby@hotmail.com> writes:
> Firstly, the documentation (Server Configuration, Lock Management) is very clear :

> _____________________________________________________
> max_locks_per_transaction (integer)

> The shared lock table tracks locks on
> max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables);

> hence, *no more* than this many distinct objects can be locked at any one time.

You're mistaking a guarantee for a hard limit.

What actually happens is that enough shared memory is reserved for at
least max_locks_per_transaction * (max_connections +
max_prepared_transactions) lock entries, plus a bunch of unrelated stuff,
plus some slop.  The other uses of shared memory are pretty static, so the
end result is that only the lock table is likely to eat up the slop.
max_locks_per_transaction is thus more of a floor than a ceiling.

I'm not sure if that documentation wording needs improvement or not.
Other references to max_locks_per_transaction don't seem to imply that
it's an exact bright line, but I can see where if you read only that
line in this para and not the rest of the para, you might come away
with such a conclusion.

            regards, tom lane

=0A=
> From: tgl@sss.pgh.pa.us=0A=
> To: johnlumby@hotmail.com=0A=
> CC: pgsql-bugs@postgresql.org=0A=
> Subject: Re: [BUGS] Re: BUG #14098: misleading message "out of shared mem=
ory" when lock table space exhausted=0A=
> Date: Tue=2C 19 Apr 2016 12:42:47 -0400=0A=
>=0A=
> John Lumby <johnlumby@hotmail.com> writes:=0A=
>> Firstly=2C the documentation (Server Configuration=2C Lock Management) i=
s very clear :=0A=
>=0A=
> You're mistaking a guarantee for a hard limit.=0A=
>=0A=
> What actually happens is that enough shared memory is reserved for at=0A=
> least max_locks_per_transaction * (max_connections +=0A=
> max_prepared_transactions) lock entries=2C plus a bunch of unrelated stuf=
f=2C=0A=
=0A=
I see =2C=A0 thanks for explaining.=0A=
=0A=
> I'm not sure if that documentation wording needs improvement or not.=0A=
=0A=
On reflection=2C=A0 I would still suggest to change the wording of the erro=
r message=2C=0A=
not the documentation.=0A=
=0A=
I now see that as you've explained=2C=A0 the words =A0=A0=A0=A0 =0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 out of shared memory=0A=
are not wrong.=A0=A0 However=2C=A0=A0 I think it may be more helpful to cha=
nge them to=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =0A=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 lock table space is exhausted=0A=
as I previously suggested.=0A=
The reason being that =2C=A0 for most people=2C=A0 intuitively=2C=A0 tellin=
g them=0A=
=A0=A0=A0 " out of shared memory "=0A=
would indicate to try to *reduce* the consumption of this resource=2C=0A=
whereas the hint tells them=A0 to *increase* something.=0A=
I think there is a clearer connection between insufficient lock table space=
 =0A=
and increasing max_locks_per_transaction.=0A=
=0A=
The documentation is also not wrong but it is conservative=0A=
and leads to the admin doing the right thing=2C=0A=
so I would leave that as is.=0A=
=0A=
Cheers=2C=A0=A0 John=0A=
=0A=
> regards=2C tom lane=0A=
                           =

On Tue, 19 Apr 2016 13:36:35 -0400
John Lumby <johnlumby@hotmail.com> wrote:


> I now see that as you've explained,=C2=A0 the words =C2=A0=C2=A0=C2=A0=C2=
=A0=20
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 out of=
 shared memory
> are not wrong.=C2=A0=C2=A0 However,=C2=A0=C2=A0 I think it may be more he=
lpful to change them to=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=20
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 lock table s=
pace is exhausted
> as I previously suggested.
> The reason being that ,=C2=A0 for most people,=C2=A0 intuitively,=C2=A0 t=
elling them
> =C2=A0=C2=A0=C2=A0 " out of shared memory "
> would indicate to try to *reduce* the consumption of this resource,
> whereas the hint tells them=C2=A0 to *increase* something.
> I think there is a clearer connection between insufficient lock table spa=
ce=20
> and increasing max_locks_per_transaction.

There are reasons besides locks that can lead to consuming all the allocated
shared memory. A message that specifically blamed locks would actively misl=
ead
a user who was trying to diagnose one of the less common cases.

-dg


--=20
David Gould                                   daveg@sonic.net
If simplicity worked, the world would be overrun with insects.

=0A=
> Date: Tue=2C 19 Apr 2016 12:48:55 -0700=0A=
> From: daveg@sonic.net=0A=
> To: johnlumby@hotmail.com=0A=
> CC: tgl@sss.pgh.pa.us=3B pgsql-bugs@postgresql.org=0A=
> Subject: Re: [BUGS] Re: BUG #14098: misleading message "out of shared mem=
ory" when lock table space exhausted=0A=
>=0A=
> On Tue=2C 19 Apr 2016 13:36:35 -0400=0A=
> John Lumby <johnlumby@hotmail.com> wrote:=0A=
>=0A=
>=0A=
>> I now see that as you've explained=2C  the words=0A=
>>             out of shared memory=0A=
>> are not wrong.   However=2C   I think it may be more helpful to change t=
hem to=0A=
>>            lock table space is exhausted=0A=
>> as I previously suggested.=0A=
>=0A=
> There are reasons besides locks that can lead to consuming all the alloca=
ted=0A=
> shared memory. A message that specifically blamed locks would actively mi=
slead=0A=
> a user who was trying to diagnose one of the less common cases.=0A=
=0A=
That may well be true=2C=A0 but in my original bug report I specifically si=
ngled out =0A=
messages originating from the lock manager which are in fact referring to a=
 failure =0A=
to allocate space for a new lock=A0 :=0A=
=0A=
from earlier posting :=0A=
_______________________________________________________=0A=
If postgresql exhausts the space reserved for locks=2C=0A=
whose size is defined by =0A=
 max_locks_per_transaction *=0A=
  =A0(max_connections + max_prepared_transactions)=0A=
locks=2C then it issues this message :=0A=
=0A=
psql: FATAL:  out of shared memory=0A=
HINT:  You might need to increase max_locks_per_transaction.[ ... ]=0A=
=0A=
I believe all cases of this msg are in=0A=
 src/backend/storage/lmgr/lock.c___________________________________________=
____________=0A=
Perhaps in view of your comment I should reword this as :=0A=
=0A=
whenever lock manager issues a message relating to failure to=0A=
allocate space for a lock =2C  then [ suggestion as before ]=0A=
=0A=
>=0A=
> -dg=0A=
>=0A=
> --=0A=
> David Gould daveg@sonic.net=0A=
=0A=
                           =