Обсуждение: BUG #18094: max max_connections cannot be set
The following bug has been logged on the website:
Bug reference: 18094
Logged by: Nikolay Samokhvalov
Email address: nikolay@samokhvalov.com
PostgreSQL version: 15.4
Operating system: MacOS
Description:
(apologies for double posting, please disregard the previous message)
Expectation for max max_connections is 262143 in PG14, but it cannot be
achieved:
nik=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.4 (Homebrew) on aarch64-apple-darwin22.4.0, compiled by Apple
clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
(1 row)
nik=# select max_val from pg_settings where name = 'max_connections';
max_val
---------
262143
(1 row)
nik=# alter system set max_connections to 262143;
ERROR: invalid value for parameter "max_connections": 262143
nik=# alter system set max_connections to 262142;
ERROR: invalid value for parameter "max_connections": 262142
nik=# alter system set max_connections to 262140;
ERROR: invalid value for parameter "max_connections": 262140
nik=# alter system set max_connections to 262135;
ERROR: invalid value for parameter "max_connections": 262135
nik=# alter system set max_connections to 262130;
ERROR: invalid value for parameter "max_connections": 262130
nik=# alter system set max_connections to 262100;
ALTER SYSTEM
On Thu, Sep 7, 2023 at 9:03 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18094
Logged by: Nikolay Samokhvalov
Email address: nikolay@samokhvalov.com
PostgreSQL version: 15.4
Operating system: MacOS
Description:
(apologies for double posting, please disregard the previous message)
Expectation for max max_connections is 262143 in PG14
I don't recall, nor find in the two official places such a thing could reasonably be listed, there being a number documented as the limit for this.
Partially I suppose because the natural limit, whatever it may be, is higher than any reasonable choice a DBA might supply.
David J.
PG Bug reporting form <noreply@postgresql.org> writes:
> Expectation for max max_connections is 262143 in PG14, but it cannot be
> achieved:
Why do you think that's the maximum?
If you are looking at MAX_BACKENDS, that is not a limit on
max_connections. It is a limit on max_connections +
autovacuum_max_workers + 1 + max_worker_processes + max_wal_senders;
cf check_max_connections.
regards, tom lane
nik=# select max_val from pg_settings where name = 'max_connections';
max_val
---------
262143
(1 row)
max_val
---------
262143
(1 row)
-- here is why
On Thu, Sep 7, 2023 at 09:25 Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> Expectation for max max_connections is 262143 in PG14, but it cannot be
> achieved:
Why do you think that's the maximum?
If you are looking at MAX_BACKENDS, that is not a limit on
max_connections. It is a limit on max_connections +
autovacuum_max_workers + 1 + max_worker_processes + max_wal_senders;
cf check_max_connections.
regards, tom lane
On Thu, Sep 7, 2023 at 9:25 PM Nikolay Samokhvalov <nikolay@samokhvalov.com> wrote:
nik=# select max_val from pg_settings where name = 'max_connections';
max_val
---------
262143
(1 row)-- here is why
Glossed right over that...yeah, quite a few settings seem to have a max_val of "unsigned numeric", I'd be curious whether they all have some non-datatype maximum recognized during runtime that pg_settings is unable to see.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Sep 7, 2023 at 9:25 PM Nikolay Samokhvalov <nikolay@samokhvalov.com>
> wrote:
>> nik=# select max_val from pg_settings where name = 'max_connections';
>> max_val
>> ---------
>> 262143
>> (1 row)
>>
>> -- here is why
> Glossed right over that...yeah, quite a few settings seem to have a max_val
> of "unsigned numeric", I'd be curious whether they all have some
> non-datatype maximum recognized during runtime that pg_settings is unable
> to see.
Yeah, there are for example plenty of entries with max_val of INT_MAX,
but that doesn't necessarily mean you can set them that high. The
max_val is *a* constraint, it's not the *only* constraint.
regards, tom lane
On Fri, Sep 8, 2023 at 06:57 Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Sep 7, 2023 at 9:25 PM Nikolay Samokhvalov <nikolay@samokhvalov.com>
> wrote:
>> nik=# select max_val from pg_settings where name = 'max_connections';
>> max_val
>> ---------
>> 262143
>> (1 row)
>>
>> -- here is why
> Glossed right over that...yeah, quite a few settings seem to have a max_val
> of "unsigned numeric", I'd be curious whether they all have some
> non-datatype maximum recognized during runtime that pg_settings is unable
> to see.
Yeah, there are for example plenty of entries with max_val of INT_MAX,
but that doesn't necessarily mean you can set them that high. The
max_val is *a* constraint, it's not the *only* constraint
Thank you for the explanation.
Then, I think, the column name may be misleading to many. I will now memorize that it's not the actual max value, but I'm sure in a couple of years I'll forget (as it happens to me often), and think again that this is a bug.
On Friday, September 8, 2023, Nikolay Samokhvalov <nikolay@samokhvalov.com> wrote:
On Fri, Sep 8, 2023 at 06:57 Tom Lane <tgl@sss.pgh.pa.us> wrote:"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Sep 7, 2023 at 9:25 PM Nikolay Samokhvalov <nikolay@samokhvalov.com>
> wrote:
>> nik=# select max_val from pg_settings where name = 'max_connections';
>> max_val
>> ---------
>> 262143
>> (1 row)
>>
>> -- here is why
> Glossed right over that...yeah, quite a few settings seem to have a max_val
> of "unsigned numeric", I'd be curious whether they all have some
> non-datatype maximum recognized during runtime that pg_settings is unable
> to see.
Yeah, there are for example plenty of entries with max_val of INT_MAX,
but that doesn't necessarily mean you can set them that high. The
max_val is *a* constraint, it's not the *only* constraintThank you for the explanation.Then, I think, the column name may be misleading to many. I will now memorize that it's not the actual max value, but I'm sure in a couple of years I'll forget (as it happens to me often), and think again that this is a bug.
Maybe a documentation improvement… but bug or not it doesn’t seem worth the effort to try too hard here. I already noted that limits based upon data type size are probably insane to actually approach in reality and getting an error message at some marginally lower value as in this case might be a bit surprising but it isn’t at all harmful.
David J.