[Sqlalchemy-tickets] Issue #4005: Error determining the version of a Postgres server (zzzeek/sqlalchemy)

[Steven W. <iss...@bi...>]

New issue 4005: Error determining the version of a Postgres server
https://bitbucket.org/zzzeek/sqlalchemy/issues/4005/error-determining-the-version-of-a

Steven Winfield:

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

```
#!sql

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```](https://www.postgresql.org/docs/10/static/runtime-config-preset.html). 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:

```
#!python
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
```
#!sql
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](https://www.postgresql.org/support/versioning/).

Cheers.