Pooler Error: Unsupported startup parameter: extra_float_digits

[tsachih]

When using the pq library along with pgbouncer, the following error is generated:
Pooler Error: Unsupported startup parameter: extra_float_digits

As a workaround, one could follow the instructions in Athou/commafeed#559

However, better control over the sent configuration would be a better approach.

i.e. existing library is hard-coding the following :
o.Set("extra_float_digits", "2")

which is the suspected cause for the issue.

[ajvb]

@tsachih I believe this can be solved by implementing a custom dialer: #470 (comment)

[tsachih]

Yes, implementing a custom dialer would provide a possible solution for this issue.However, note that the problem is more parameters setting related rather then dialing related.
If we could have had a pre-dial custom filter that would have the ability to update the connection options map, it would be even better. ( which doesn't conflict with having a custom dialer )
Tsachi
Date: Wed, 20 Jul 2016 14:53:12 -0700
From: notifications@github.com
To: pq@noreply.github.com
CC: tsachih@hotmail.com; mention@noreply.github.com
Subject: Re: [lib/pq] Pooler Error: Unsupported startup parameter: extra_float_digits (#475)

@tsachih I believe this can be solved by implementing a custom dialer: #470 (comment)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[jchamberlain]

Just ran into this myself. Is there a reason extra_float_digits needs to be hardcoded into the driver? I see that parseOpts() lets me set options in the DSN, so I can easily override the value, e.g.,

sql.Open("host=hostname port=5432 sslmode=disable extra_float_digits=3")

Problem is, even though I can set it back to its default value, I can't remove it entirely. What in the driver requires it?

[johto]

Problem is, even though I can set it back to its default value, I can't remove it entirely. What in the driver requires it?

Getting out correct values for floats from the database.

You'll see that other drivers (such as the JDBC one) send the same setting over, so it's not like pq is doing anything out of the ordinary here. I don't think this is really worth changing.

[jchamberlain]

I see this has been discussed before (#205) so I won't belabor the point. I'm just surprised the driver needs to hard code this when it's trivial to configure in at least two other places, the DSN and postgresl.conf.

Also, I'm guessing what pgJDBC is doing isn't entirely usual as I don't have this problem in PHP and I can't find a reference to extra_float_digits in the Python driver either.

But I'll go ahead and use ignore_startup_parameters.

[tsachih]

Is the DSN string is a known standard ? i.e. can we set it with some custom instrumentation values so that the dialer doesn't add that string ?
few ideas :
sql.Open("host=hostname port=5432 sslmode=disable -extra_float_digits")
sql.Open("host=hostname port=5432 sslmode=disable no_extra_float_digits=true")
sql.Open("host=hostname port=5432 sslmode=disable pgexclude=extra_float_digits")

The last one is the most versatile.

Note that for my application, I cannot use ignore_startup_parameters since that particular pgbouncer is used by other clients. I don't want to modify the behavior of these clients.

Note that the default value for extra_float_digits is zero. (https://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-FLOAT)

[tsachih]

Another option which I didn't include above is to pass
sql.Open("host=hostname port=5432 sslmode=disable extra_float_digits=-1")

Since the extra_float_digits is a positive integer number, we can use this value to indicate that we want to use the default value.

[miladz68]

@tsachih
I think your suggestions are sound.
I tried to implement a custom dialer but got lost in the complexity of all the different things that I needed to implement again. So I simply removed o["extra_float_digits"] = "2" from the source code and it worked like a charm.

[igorwwwwwwwwwwwwwwwwwwww]

Just ran into this problem as well. +1 for making this behaviour optional.

[archfish]

from https://github.com/pgbouncer/pgbouncer/blob/master/NEWS.rst
2008-07-29 - PgBouncer 1.2 - "Ordinary Magic Flute"

New config var 'ignore_startup_parameters' to allow and ignore extra parameters in startup packet. By default only 'database' and 'user' are allowed, all others raise error. This is needed to tolerate overenthusiastic JDBC wanting to unconditionally set 'extra_float_digits=2' in startup packet.

just enable ignore_startup_parameters = extra_float_digits in pgbouncer config.

;
; Comma-separated list of parameters to ignore when given
; in startup packet.  Newer JDBC versions require the
; extra_float_digits here.
;
ignore_startup_parameters = extra_float_digits

[pateljoel]

I'm using a managed postgres service that doesn't allow me to edit the pgbouncer config.

I have found that @miladz68 solution works and takes only < 2 mins to fix and I see no reason why this line was included in the first place.

Please make this optional.

[NKame]

8 years later: no, changing the server configuration you have no authority over is not the right place to "fix" a client decision to disregard the server's choice. https://www.postgresql.org/docs/8.4/datatype-numeric.html#DATATYPE-FLOAT extra_float_digits has been formally introduced in 8.4 to increase the precision sent from the server to the client ; since 11, it's here to decrease that precision. In no way a client should have a hard coded confing trying to guess the application need regarding a lossy datatype, especially when it creates a bug with the widespread PgBouncer.