Error determining the version of a Postgres server

[Steven Winfield]

In sqlalchemy.dialects.postgresql.base.PGDialect._get_server_version_info, the server version is found by regex matching the result of

select version()

but this doesn't match the string returned by the v10 betas that are currently available, so an AssertionError is raised.

An example of the version string there is: PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

However, a more robust way to check the version number - the way that the postgres devs recommend now - would be to query the value of server_version_num. This is an integer (returned as a string) of the form (major * 10000 + minor * 100 + bugfix), e.g. Postgres v9.6.2 => 90602 and 10beta1 => 100000

So _get_server_version_info could do something like:

v = int(connection.execute("show server_version_num").scalar())
major, v = divmod(v, 10000)
minor, bugfix = divmod(v, 100)
return (major, minor, bugfix)

If you prefer a select rather than a show, then

select current_setting('server_version_num')

does the same thing.

This should work all the way back to postgres v8.2. Not that it matters too much, but the designation above of "major", "minor", and "bugfix" is correct for v10 onwards.

Cheers.

[Mike Bayer]

well in theory we support pre 8.2 as well. there are also postgresql variants like Redshift and postgresql "wire equivalents" like CockroachDB that are building on top of this dialect.

the less risky, and easy to commit to 1.1.x fix, would be:

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 1d6951c..5f10ab9 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2291,7 +2291,7 @@ class PGDialect(default.DefaultDialect):
         v = connection.execute("select version()").scalar()
         m = re.match(
             r'.*(?:PostgreSQL|EnterpriseDB) '
-            r'(\d+)\.?(\d+)?(?:\.(\d+))?(?:\.\d+)?(?:devel)?',
+            r'(\d+)\.?(\d+)?(?:\.(\d+))?(?:\.\d+)?(?:devel|beta)?',
             v)
         if not m:
             raise AssertionError(

this is after all what we just did w/ the "devel" that was added in 6d13f0ace31dd5307eb2c9deb8287eaa603fd099 just three months ago.

if you want to work up a PR to do the "server_version_num" version, that can be against 1.2 or 1.3 but we have to verify at least w/ the two most prominent PG variant dialects, sqlalchemy-redshift and sqlalchemy-cockroachdb, that they aren't going to break, as well as if a < 8.2 postgresql version is used. usually we just run the newer check, catch "DBError" if it fails, then fall back to the "old" method. Which sort of means the "old" method doesn't really go away.

[Mike Bayer]

the "easy" fix is running through at https://gerrit.sqlalchemy.org/424

[Mike Bayer]

Parse for Postgresql version w/ "beta"

Continuing with the fix that correctly handles Postgresql version string "10devel" released in 1.1.8, an additional regexp bump to handle version strings of the form "10beta1". While Postgresql now offers better ways to get this information, we are sticking w/ the regexp at least through 1.1.x for the least amount of risk to compatibility w/ older or alternate Postgresql databases.

Change-Id: I12ddb06465f7dcf80563c27632441ef5963f60d4 Fixes: #4005

→ <<cset b6d3f6079183>>

[Mike Bayer]

Parse for Postgresql version w/ "beta"

Continuing with the fix that correctly handles Postgresql version string "10devel" released in 1.1.8, an additional regexp bump to handle version strings of the form "10beta1". While Postgresql now offers better ways to get this information, we are sticking w/ the regexp at least through 1.1.x for the least amount of risk to compatibility w/ older or alternate Postgresql databases.

Change-Id: I12ddb06465f7dcf80563c27632441ef5963f60d4 Fixes: #4005 (cherry picked from commit b6d3f60791834ead92564fc58afebc2c3eb4a2ff)

→ <<cset 7bbf960a3d1f>>