What is the use case of validation parameter of pgagroal.conf ?

[nomade33]

Hi

I have read this page https://agroal.github.io/[pgagroal](https://agroal.github.io/pgagroal/configuration.html)/configuration.html but I don't understand the use case of the "validation (no required") parameter which is present in pgagroal.conf.
Could you please describe me a use case ?

Thanks,

[jesperpedersen]

Some frameworks will do their own validation of the connection. So, you can choose your own strategy based on whatever your clients need.

None: Let clients validate if they want (max performance)
Foreground: Validate connection when client wants one - the connection will always work (lowest performance)
Background: Validate free connections in the background - the connection handed out to the client may or may not be valid (trade-off)

Hope this helps !

[nomade33]

Merci. Thanks

[sheshjee]

validation = on
what will happen if set this parameter on?

[sheshjee]

many users simultaneously using transaction on my server where i installed postgresql and pgagrol.
i am getting this error: Error while preparing App DB connection: org.postgresql.util.PSQL Exception: FATAL: connection pool is full

[jesperpedersen]

You should use off, foreground or background as the setting.

FATAL: connection pool is full means that the pool is max'ed out -- you can increase the pool size if the PostgreSQL instance supports that, or better figure out why there are so many client connections (maybe that is needed, but maybe something can be done)

[sheshjee]

But the max_connection is already 1000 and current connection which are being used is just 31. Then how poll is full

…

On Wed, Jan 17, 2024, 1:24 AM Jesper Pedersen ***@***.***> wrote: You should use off, foreground or background as the setting. FATAL: connection pool is full means that the pool is max'ed out -- you can increase the pool size if the PostgreSQL instance supports that, or better figure out why there are so many client connections (maybe that is needed, but maybe something can be done) — Reply to this email directly, view it on GitHub <#220 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG62TVWXJV4EFFOYIRFTYO3LIRAVCNFSM5TOKFYR2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMJUHA3TEMA> . You are receiving this because you commented.Message ID: ***@***.***>

[jesperpedersen]

Is that what pgagroal-cli details shows ?

[sheshjee]

pgagroal-cli status Status: Running Active connections: 1 Total connections: 10 Max connections: 900 select count(*) from pg_stat_activity; 31 this is the issue , when max_connection is 900 and total connection is 10 in pgagroal and 31 in postgres, then why i am facing this error: connection pool is full?

…

On Wed, Jan 17, 2024 at 9:04 AM Jesper Pedersen ***@***.***> wrote: Is that what pgagroal-cli details shows ? — Reply to this email directly, view it on GitHub <#220 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG673SKUQJHISKSZQYGLYO5BEXAVCNFSM5TOKFYR2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMJVGE3TIMA> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

[jesperpedersen]

And you are using 1.5.1 ?

What is your client stack ?

[sheshjee]

yes 1.5.1 i dont know what is client stack. we are using postgresql 13 and pgagroal

…

On Fri, Jan 19, 2024 at 4:31 AM Jesper Pedersen ***@***.***> wrote: And you are using 1.5.1 ? What is your client stack ? — Reply to this email directly, view it on GitHub <#220 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG66AXHCGQF5GWJXNVKTYPGSUDAVCNFSM5TOKFYR2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMJXGQ4TKOI> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee

[jesperpedersen]

What connects to pgagroal ? Is psql or something else ?

[sheshjee]

Psql

…

On Fri, Jan 19, 2024, 4:49 PM Jesper Pedersen ***@***.***> wrote: What connects to pgagroal ? Is psql or something else ? — Reply to this email directly, view it on GitHub <#220 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG65OSVXFF576HZZLGATYPJJELAVCNFSM5TOKFYR2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMJYGAZDSNQ> . You are receiving this because you commented.Message ID: ***@***.***>

[jesperpedersen]

Are you sure that the client closes ? E.g. \q

Can you make a script that shows the issue ?

[sheshjee]

The issue is simply that an error is occurring: the connection pool is full. Clients are connecting to database with heavy transactions and this error occurs. This is pgagroal config and service file: /etc/pgagroal/pgagroal.conf [pgagroal] host = * port = 2345 log_type = file log_level = info log_path = /var/log/pgagroal/pgagroal.log max_connections = 900 idle_timeout = 600 validation = off unix_socket_dir = /tmp [primary] host = localhost port = 5432 ################ /etc/pgagroal/pgagroal_hba.conf # # TYPE DATABASE USER ADDRESS METHOD # host all all all all ############# /etc/systemd/system/pgagroal.service # systemd service unit for pgagroal # # - Adjust the user running the service in User # - Adjust the path in ExecStart # [Unit] Description=High-performance connection pool for PostgreSQL Documentation=man:pgagroal(1) Documentation=https://agroal.github.io/pgagroal/ After=network.target [Service] Type=exec User=postgres ExecStart=/bin/pgagroal ExecReload=/bin/kill -HUP $MAINPID KillSignal=SIGINT LimitNOFILE=10240 [Install] WantedBy=multi-user.target

…

On Fri, Jan 19, 2024 at 5:33 PM Jesper Pedersen ***@***.***> wrote: Are you sure that the client closes ? E.g. \q Can you make a script that shows the issue ? — Reply to this email directly, view it on GitHub <#220 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BFMCG6ZQLLQEUS2WW3FETGLYPJOKDAVCNFSM5TOKFYR2U5DIOJSWCZC7NNSXTOKENFZWG5LTONUW63SDN5WW2ZLOOQ5TQMJYGA3DSMA> . You are receiving this because you commented.Message ID: ***@***.***>

-- sheshjee