ValueError: invalid literal for int() with base 10: '3 (Ubuntu 10' when connecting

[ThomasChr]

Here the full stack trace:

Traceback (most recent call last):
File "", line 1, in
File "/usr/local/lib/python3.6/dist-packages/postgresql/init.py", line 94, in open
c.connect()
File "/usr/local/lib/python3.6/dist-packages/postgresql/driver/pq3.py", line 2427, in connect
self._establish()
File "/usr/local/lib/python3.6/dist-packages/postgresql/driver/pq3.py", line 2559, in _establish
self.version_info = pg_version.normalize(pg_version.split(sv))
File "/usr/local/lib/python3.6/dist-packages/postgresql/versionstring.py", line 28, in split
vlist = [int(x or '0') for x in v[:-1]]
File "/usr/local/lib/python3.6/dist-packages/postgresql/versionstring.py", line 28, in
vlist = [int(x or '0') for x in v[:-1]]
ValueError: invalid literal for int() with base 10: '3 (Ubuntu 10'

This issue has already been reported here: #93

The problem is that on Postgres 10 Ubuntu adds the Ubuntu Version to the Postgres Version string which py-postgresql can't handle.
My version string looks like this:
10.3 (Ubuntu 10.3-1)

I have a fix ready and will attach it here.

[ThomasChr]

Already fixed in this PR: #94

[jwp]

I think this issue is best handled using a patch managed by the packaging system that made the changes to the server software.

[greatvovan]

@jwp
Your explanation about "packaging system" is vague. What a database user can do in case of this error? The problem happens on client side. Patching the database is not available to users.

[ThomasChr]

Simple fix: Use psycopg2-binary :-(

[greatvovan]

@ThomasChr I use it in some projects, but this library has several advantages which are very useful and missing in psycopg2.

[ThomasChr]

That leaves only the choice to manually patch the library everytime you install it. Or maybe we can convince @jwp to patch it!

[greatvovan]

Yes, I have to patch this function every time on program start. New users won't do things like this, having encountered the bug they won't be figuring out how to fix it, and decide (reasonably) this lib is abandoned. Honestly, I don't understand the position of maintainers, usually authors are interested in growing community around their product...

[jwp]

Debian fixed this in their distribution using a downstream patch, this is what I recommend to other packaging systems that wish to modify the version string supplied by postgresql.org.

[greatvovan]

Dear @jwp, I am afraid you are still misinterpreting the issue.
Try the latest official Docker image for the DBMS:
docker run -it --rm -v -p 5432:5432 postgres:11
Ensure the library does not work with it:

import postgresql
db = postgresql.open('pq://postgres@localhost/postgres')

ValueError: invalid literal for int() with base 10: '1 (Debian 11'

Docker image is only the simplest way to check it, but this string is now everywhere, in almost all distributions. Are you waiting for the monster project will adapt for your lib and not vice versa?

[greatvovan]

Additionally, what are you talking about here?

that wish to modify the version string supplied by postgresql.org

Sorry, what is provided by postgresql.org and where?
I only found this page: https://www.postgresql.org/support/versioning/ but it is only general guideline for people, not parsing specification for computers. Could you please post evidence that having additional information in version string violates any specification from postgresql.org?

[jwp]

Sorry, what is provided by postgresql.org and where?

py-postgresql is tested against builds whose sources are normally retrieved from: https://www.postgresql.org/ftp/source/
The "version string supplied by postgresql.org" being the one that is defined in those sources.

[ThomasChr]

I don't want to offend anyone, but sometimes there is the right thing to do, like "let xxx fix the root cause for the problem" and sometimes you should just help the users of your library and fix it for them, even if it doesn't seem like the perfect thing to do.
I suspect that nearly any complex software has fixes for undocumented (or wrong documented) behaviour. If every linux kernel hacker would wait for the hardware provider of Chip XY to fix the root cause for the Kernel Oops (being the chip which does not behave correctly), Linux would never made it to a usable operating system.

[greatvovan]

@jwp I am afraid the issue will not be fixed in the way you suppose it should be. The reason is that the people who are related to Postgres distribution and the ones who use your lib, mostly belong to different groups. The first group is concerned about Postgres server issues and don't care a lot about what software clients use. The second group are Postgres client developers and they basically have no power to persuade the first group to remove this useful information from version strings (especially when there is no evidence that this format contradicts to some specification).

Imagine you are working on an integration project which spreads across multiple platforms and operation systems. Your customer is using Postgres on Ubuntu or in Docker. You cannot persuade your customer to reinstall (and probably recompile) the RDBMS (which may involve downtime) just because you have a Python lib that does not support some basic string parsing stuff. It is not in your power. Instead, you have to do some tricks to patch the lib. Do you think this solution is nice?

https://www.postgresql.org/ftp/source/
The "version string supplied by postgresql.org" being the one that is defined in those sources.

As I wrote above, since there is no explicit specification for the version string, the result returned by these distributions in only "one of valid version strings", not the model.

May I ask you, why are you so resistant against this change? All other libraries (psycopg2, asyncpg, pg8000) either have fixed this problem or even have never had it.

[jwp]

If every linux kernel hacker would wait for the hardware provider of Chip XY

py-postgresql is a small project with a relatively narrow scope in comparison to linux. The practices employed by larger projects often do not suite smaller ones.

All other libraries (psycopg2, asyncpg, pg8000) either have fixed this problem or even have never had it.

I am not aware of other libraries that perform version parsing in the driver. py-postgresql uses the parsed version internally and provides it to the user on Connection objects as a convenience. The current internal use case involves older versions of PostgreSQL and can probably be eliminated at this point. However, moving forward it may be desired (to use the parsed version) again so leaving it as-is was reasonable.

I am afraid the issue will not be fixed in the way you suppose it should be.

Supporting all possible variations is not within the scope of the project. It is regrettable that people have been inconvenienced by an overloaded version string, and I am grateful that debian packaging has chosen to supply a fix for the users of their packaging system. For people using packaging systems without the empowered PostgreSQL version string, the patch provided by debian should function for them as well given that they wish to connect to a debianized PostgreSQL server.