BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15759
Logged by:          anup singh
Email address:      [hidden email]
PostgreSQL version: 10.7
Operating system:   linux
Description:        

Lot of idle connection created by "SET extra_float_digits = 3" which is
killing our applicaiton.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

Tomas Vondra-4
On Tue, Apr 16, 2019 at 11:04:25AM +0000, PG Bug reporting form wrote:

>The following bug has been logged on the website:
>
>Bug reference:      15759
>Logged by:          anup singh
>Email address:      [hidden email]
>PostgreSQL version: 10.7
>Operating system:   linux
>Description:
>
>Lot of idle connection created by "SET extra_float_digits = 3" which is
>killing our applicaiton.
>

SET extra_float_digits = 3 (or any other SET command) does not create any
connections at all. It's much more likely your application opens a new
connection, executes the SET command on it (so it's in pg_stat_activity)
and then goes to do something else.

If that's not the case, we need much more information. Why do you think
the SET command is creating idle connections?

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Reply | Threaded
Open this post in threaded view
|

Re: BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

anup singh
Hi Tomas,

Thanks for your response. Kindly see the below screen shot where we have lot of idle connection which is reaching max pool size soon, but the actual request is only one.

image.png

On Tue, Apr 16, 2019 at 5:29 PM anup singh <[hidden email]> wrote:
Hi Tomas,

Thanks for your response. Kindly see the below screen shot where we have lot of idle connection which is reaching max pool size soon, but the actual request is only one.
image.png


On Tue, Apr 16, 2019 at 5:15 PM Tomas Vondra <[hidden email]> wrote:
On Tue, Apr 16, 2019 at 11:04:25AM +0000, PG Bug reporting form wrote:
>The following bug has been logged on the website:
>
>Bug reference:      15759
>Logged by:          anup singh
>Email address:      [hidden email]
>PostgreSQL version: 10.7
>Operating system:   linux
>Description:
>
>Lot of idle connection created by "SET extra_float_digits = 3" which is
>killing our applicaiton.
>

SET extra_float_digits = 3 (or any other SET command) does not create any
connections at all. It's much more likely your application opens a new
connection, executes the SET command on it (so it's in pg_stat_activity)
and then goes to do something else.

If that's not the case, we need much more information. Why do you think
the SET command is creating idle connections?

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

Tomas Vondra-4
On Tue, Apr 16, 2019 at 05:31:42PM +0530, anup singh wrote:
>Hi Tomas,
>
>Thanks for your response. Kindly see the below screen shot where we have
>lot of idle connection which is reaching max pool size soon, but the actual
>request is only one.
>

I'm pretty sure that's the expected behavior of a connection pool. It
opens a bunch of connections, does some initialization on them (which
likely includes the "SET extra_float_digits = 3" bit) and then keeps them
around so that the application can use them.

You have not really explained how it's killing your application, though.
My guess is it's either not going through the connection pool (and so is
hitting max_connections or something) or it's misconfigured in some other
way. In any case, extra_float_digits is not the root cause here.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Reply | Threaded
Open this post in threaded view
|

Re: BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

anup singh
Thank you very much Tomas for such a nice explanation.
We have Max 100 connection. If you see the screen shot in previous mail, most of the idle connection are taken by "SET extra_float_digits = 3" bit. After sometime application says "fatal error - reached Max connection, 3 connection are reserved for PG".

Regards
Anup


On Tue, Apr 16, 2019, 6:07 PM Tomas Vondra <[hidden email]> wrote:
On Tue, Apr 16, 2019 at 05:31:42PM +0530, anup singh wrote:
>Hi Tomas,
>
>Thanks for your response. Kindly see the below screen shot where we have
>lot of idle connection which is reaching max pool size soon, but the actual
>request is only one.
>

I'm pretty sure that's the expected behavior of a connection pool. It
opens a bunch of connections, does some initialization on them (which
likely includes the "SET extra_float_digits = 3" bit) and then keeps them
around so that the application can use them.

You have not really explained how it's killing your application, though.
My guess is it's either not going through the connection pool (and so is
hitting max_connections or something) or it's misconfigured in some other
way. In any case, extra_float_digits is not the root cause here.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15759: Lot of idle connection created by "SET extra_float_digits = 3"

Francisco Olarte
Anup:

On Tue, Apr 16, 2019 at 4:19 PM anup singh <[hidden email]> wrote:
> We have Max 100 connection. If you see the screen shot in previous mail, most of the idle connection are taken by "SET extra_float_digits = 3" bit. After sometime application says "fatal error - reached Max connection, 3 connection are reserved for PG".

What Tomas was trying to tell you is that your connections are not
"taken" or "executing" the "set extra..." they are Idle, not doing
anything. When a conection is Idle, not doing anything, the "query
text" shows the last command executed, but it has already finished.

The root cause of your problem is probably a connection leak in your
application, or something like that, someone opening connections,
preparing them with the set and them forgetting about them without
closing, but given you have not told anything about your application
setup or other details there is nothing people can do to aid you (
except telling "you need to post more details" ).

Francisco Olarte.