Re: Re: BUG #14098: misleading message "out of shared memory" when lock table space exhausted

Thanks Tom,

However Can you please explain a little more on this.

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.
_____________________________________________________

That apparently seems to support what I said and contradicts your assertion -
depending on what you meant by "hard" separation.

So if what you stated is correct,  then two comments :

  .    the documentation must be incorrect and should be corrected.
  .    can you please state exactly what is the limit for how many
       distinct objects can be locked at any one time,  based on configuration parameters.
       If this number can exceed max_locks_per_transaction * (max_connections + max_prepared_transactions),
       then is this done by
            a) dynamically extending shared memory
            b) incursing into some other component's nominal portion of shared memory,
               (the amount it says it needs during postgresql startup when the total size of the
                initial shared memory segment is being computed)
       If b),  then can other components equally incurse into the nominal lock table space?

Cheers, John

John Lumby <johnlumby(at)hotmail(dot)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

> From: tgl(at)sss(dot)pgh(dot)pa(dot)us
> To: johnlumby(at)hotmail(dot)com
> CC: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] Re: BUG #14098: misleading message "out of shared memory" when lock table space exhausted
> Date: Tue, 19 Apr 2016 12:42:47 -0400
>
> John Lumby <johnlumby(at)hotmail(dot)com> writes:
>> Firstly, the documentation (Server Configuration, Lock Management) is very clear :
>
> 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,

I see ,  thanks for explaining.

> I'm not sure if that documentation wording needs improvement or not.

On reflection,  I would still suggest to change the wording of the error message,
not the documentation.

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

The documentation is also not wrong but it is conservative
and leads to the admin doing the right thing,
so I would leave that as is.

Cheers,   John

> regards, tom lane

On Tue, 19 Apr 2016 13:36:35 -0400
John Lumby <johnlumby(at)hotmail(dot)com> wrote:

> I now see that as you've explained,  the words     
>             out of shared memory
> are not wrong.   However,   I think it may be more helpful to change them to          
>            lock table space is exhausted
> as I previously suggested.
> The reason being that ,  for most people,  intuitively,  telling them
>     " out of shared memory "
> would indicate to try to *reduce* the consumption of this resource,
> whereas the hint tells them  to *increase* something.
> I think there is a clearer connection between insufficient lock table space
> 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 mislead
a user who was trying to diagnose one of the less common cases.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.

> Date: Tue, 19 Apr 2016 12:48:55 -0700
> From: daveg(at)sonic(dot)net
> To: johnlumby(at)hotmail(dot)com
> CC: tgl(at)sss(dot)pgh(dot)pa(dot)us; pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] Re: BUG #14098: misleading message "out of shared memory" when lock table space exhausted
>
> On Tue, 19 Apr 2016 13:36:35 -0400
> John Lumby <johnlumby(at)hotmail(dot)com> wrote:
>
>
>> I now see that as you've explained, the words
>> out of shared memory
>> are not wrong. However, I think it may be more helpful to change them to
>> lock table space is exhausted
>> as I previously suggested.
>
> There are reasons besides locks that can lead to consuming all the allocated
> shared memory. A message that specifically blamed locks would actively mislead
> a user who was trying to diagnose one of the less common cases.

That may well be true,  but in my original bug report I specifically singled out
messages originating from the lock manager which are in fact referring to a failure
to allocate space for a new lock  :

from earlier posting :
_______________________________________________________
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.[ ... ]

I believe all cases of this msg are in
src/backend/storage/lmgr/lock.c_______________________________________________________
Perhaps in view of your comment I should reword this as :

whenever lock manager issues a message relating to failure to
allocate space for a lock , then [ suggestion as before ]

>
> -dg
>
> --
> David Gould daveg(at)sonic(dot)net