BUG #18094: max max_connections cannot be set

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)

-- here is why

On Thu, Sep 7, 2023 at 09:25 Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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.