Thread: Re: [HACKERS] [COMMITTERS] pgsql: Add psql variables showing server version and psql version.

[ redirecting to -hackers for wider comment ]

Simon Riggs <simon@2ndquadrant.com> writes:
> On 5 September 2017 at 07:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Add psql variables showing server version and psql version.
>> 
>> We already had a psql variable VERSION that shows the verbose form of
>> psql's own version.  Add VERSION_NAME to show the short form (e.g.,
>> "11devel") and VERSION_NUM to show the numeric form (e.g., 110000).
>> Also add SERVER_VERSION_NAME and SERVER_VERSION_NUM to show the short and
>> numeric forms of the server's version.  (We'd probably add SERVER_VERSION
>> with the verbose string if it were readily available; but adding another
>> network round trip to get it seems too expensive.)
>> 
>> The numeric forms, in particular, are expected to be useful for scripting
>> purposes, now that psql can do conditional tests.

> This is good.
> Please can we backpatch these are far as they will go (easily)?
> There is very little risk in doing so and significant benefits in
> being able to rely on scripts that know about versions.

Hm.  I think it would be a fine idea to push this change into v10,
so that all psql versions that have \if would have these variables.
However, I'm less enthused about adding them to the 9.x branches.
Given the lack of \if, I'm not seeing a use-case that would justify
taking any compatibility risk for.

Opinions anyone?
        regards, tom lane

On Tue, Sep 5, 2017 at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hm.  I think it would be a fine idea to push this change into v10,
> so that all psql versions that have \if would have these variables.
> However, I'm less enthused about adding them to the 9.x branches.
> Given the lack of \if, I'm not seeing a use-case that would justify
> taking any compatibility risk for.
>
> Opinions anyone?

As I see it, the risks of back-patching are:

1. Different minor versions will have different behavior, and that
tends to create more problems than it solves.

2. There's a small probability that somebody's script could be relying
on these variables to be initially unset.

I think back-patching these changes into stable branches is a slippery
slope that we'd be best advised not to go down.  I don't feel as
strongly about v10.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Sep 5, 2017 at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hm.  I think it would be a fine idea to push this change into v10,
>> so that all psql versions that have \if would have these variables.
>> However, I'm less enthused about adding them to the 9.x branches.
>> Given the lack of \if, I'm not seeing a use-case that would justify
>> taking any compatibility risk for.
>> 
>> Opinions anyone?

> As I see it, the risks of back-patching are:

> 1. Different minor versions will have different behavior, and that
> tends to create more problems than it solves.

Yeah.  Whatever use-case you think might exist for these variables in,
say, 9.6 is greatly weakened by the fact that releases through 9.6.5
don't have them.

However, since v10 is still in beta I don't think that argument
applies to it.
        regards, tom lane

>> This is good. Please can we backpatch these are far as they will go 
>> (easily)? There is very little risk in doing so and significant 
>> benefits in being able to rely on scripts that know about versions.
>
> Hm.  I think it would be a fine idea to push this change into v10,
> so that all psql versions that have \if would have these variables.
> However, I'm less enthused about adding them to the 9.x branches.
> Given the lack of \if, I'm not seeing a use-case that would justify
> taking any compatibility risk for.
>
> Opinions anyone?

I think that it is harmless and useful for v10, and mostly useless for 
previous versions.

ISTM that the hack looking like:
  \if false \echo BAD VERSION \q \endif

Allow to test a prior 10 version and stop. However testing whether a 
variable is defined is not included yet, so differenciating between 10 and 
11 would not be easy... thus having 10 => version available would be 
significantly helpful for scripting.

-- 
Fabien.

On 5 September 2017 at 09:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Sep 5, 2017 at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Hm.  I think it would be a fine idea to push this change into v10,
>>> so that all psql versions that have \if would have these variables.
>>> However, I'm less enthused about adding them to the 9.x branches.
>>> Given the lack of \if, I'm not seeing a use-case that would justify
>>> taking any compatibility risk for.
>>>
>>> Opinions anyone?
>
>> As I see it, the risks of back-patching are:
>
>> 1. Different minor versions will have different behavior, and that
>> tends to create more problems than it solves.
>
> Yeah.  Whatever use-case you think might exist for these variables in,
> say, 9.6 is greatly weakened by the fact that releases through 9.6.5
> don't have them.

Makes sense

> However, since v10 is still in beta I don't think that argument
> applies to it.

OK


Does raise the further question of how psql behaves when we connect to
a pre-10 server, so we have SERVER_VERSION_NUM but yet it is not set.
How does this
\if SERVER_VERSION_NUM < x
behave if unset? Presumably it fails, even though the version *is* less than x
Do we need some macro or suggested special handling?

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

Simon Riggs <simon@2ndquadrant.com> writes:
> Does raise the further question of how psql behaves when we connect to
> a pre-10 server, so we have SERVER_VERSION_NUM but yet it is not set.

Huh?  The variable will be set in any case.  It should be correct for any
server version we might find in the wild --- so far as I can tell from the
commit history, every version supporting FE/BE protocol version 3 has sent
server_version at connection start.  With a pre-7.4 server, it looks like
the variables would be set to "0.0.0" and "0" respectively.
        regards, tom lane

I wrote:
> Huh?  The variable will be set in any case.  It should be correct for any
> server version we might find in the wild --- so far as I can tell from the
> commit history, every version supporting FE/BE protocol version 3 has sent
> server_version at connection start.  With a pre-7.4 server, it looks like
> the variables would be set to "0.0.0" and "0" respectively.

Scratch that: experimentation says it works fine with older servers too.
The oldest one I have in captivity reports

SERVER_VERSION_NAME = '7.0.3'
SERVER_VERSION_NUM = '70003'

Looking more closely, I see that when using protocol version 2, libpq
will issue a "select version()" command at connection start to get
this info.
        regards, tom lane

Hello Simon,

> Does raise the further question of how psql behaves when we connect to
> a pre-10 server, so we have SERVER_VERSION_NUM but yet it is not set.
> How does this
> \if SERVER_VERSION_NUM < x

The if does not have expressions (yet), it just handles TRUE/ON/1 and 
FALSE/0/OFF.

> Do we need some macro or suggested special handling?

If "SERVER_VERSION_NUM" is available in 10, then:
  -- exit if version < 10 (\if is ignored and \q is executed)  \if false \echo "prior 10" \q \endif
  -- then test version through a server side expression, will work  SELECT :SERVER_VERSION_NUM < 110000 AS prior_11
\gset \if :prior_11    -- version 10  \else    -- version 11 or more  \endif
 

-- 
Fabien.

>> Huh?  The variable will be set in any case.  It should be correct for any
>> server version we might find in the wild --- so far as I can tell from the
>> commit history, every version supporting FE/BE protocol version 3 has sent
>> server_version at connection start.  With a pre-7.4 server, it looks like
>> the variables would be set to "0.0.0" and "0" respectively.
>
> Scratch that: experimentation says it works fine with older servers too.
> The oldest one I have in captivity reports

Ok, be it means a recent psql connecting to an old server. It does not 
work with an old psql.

> SERVER_VERSION_NAME = '7.0.3'
> SERVER_VERSION_NUM = '70003'
>
> Looking more closely, I see that when using protocol version 2, libpq
> will issue a "select version()" command at connection start to get
> this info.

Then it could be used for free to set SERVER_VERSION if it can be 
extracted from libpq somehow?!

-- 
Fabien.

On 5 September 2017 at 11:58, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
>
> Hello Simon,
>
>> Does raise the further question of how psql behaves when we connect to
>> a pre-10 server, so we have SERVER_VERSION_NUM but yet it is not set.
>> How does this
>> \if SERVER_VERSION_NUM < x
>
>
> The if does not have expressions (yet), it just handles TRUE/ON/1 and
> FALSE/0/OFF.
>
>> Do we need some macro or suggested special handling?
>
>
> If "SERVER_VERSION_NUM" is available in 10, then:
>
>   -- exit if version < 10 (\if is ignored and \q is executed)
>   \if false \echo "prior 10" \q \endif
>
>   -- then test version through a server side expression, will work
>   SELECT :SERVER_VERSION_NUM < 110000 AS prior_11 \gset
>   \if :prior_11
>     -- version 10
>   \else
>     -- version 11 or more
>   \endif


Based upon input from Tom and Fabien, I propose this additional doc patch.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Simon Riggs <simon@2ndquadrant.com> writes:
> Based upon input from Tom and Fabien, I propose this additional doc patch.

I do not think any of this is appropriate, particularly not the reference
to 7.0.3.
        regards, tom lane

On 6 September 2017 at 06:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>> Based upon input from Tom and Fabien, I propose this additional doc patch.
>
> I do not think any of this is appropriate, particularly not the reference
> to 7.0.3.

OK, no problem.

SERVER_VERSION_NUM is a great new feature. I think these points need
further changes

* An example of the intended use of SERVER_VERSION_NUM in psql
* Clarification that this will work for current AND past server versions
* Clarification to avoid confusion between VERSION and SERVER_VERSION

Thanks

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

Simon Riggs <simon@2ndquadrant.com> writes:
> * Clarification that this will work for current AND past server versions

The short answer is it works.  I do not think we need a longer answer.

(The existing comment in libpq says that the "select version()" method
works in any server version that supports protocol v2, which means that
we will derive a correct server version ID for any server that modern
libpq is capable of connecting to at all.  I've not gone back to re-verify
what I wrote in 2003, but I see no reason to doubt it.  We'd have been
considerably more fussed about whether PQserverVersion worked correctly
for ancient server versions back then than we need be today.)

> * Clarification to avoid confusion between VERSION and SERVER_VERSION

In what way are these variables not adequately specified already?
        regards, tom lane

Hello,

>> * Clarification that this will work for current AND past server versions
>
> The short answer is it works.  I do not think we need a longer answer.

To have something operational you have to know quite a bit of psql
details (:-substitutions, backslash command logic, gset, if, quit...).

Thus short, simple but meaningful examples which show how to do something 
useful with all that in the documentation may help people take advantage 
of these new features.

Given my experience with "\d*", I'm not sure I would assume that a new 
psql feature would work with older servers.

-- 
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Thus short, simple but meaningful examples which show how to do something 
> useful with all that in the documentation may help people take advantage 
> of these new features.

I don't have an objection to providing an example.  I wasn't terribly
impressed with Simon's version, but maybe we can do better.  I think
it should be concrete, not partly abstract; and likely it needs to be
with \if not the variable proper.

> Given my experience with "\d*", I'm not sure I would assume that a new 
> psql feature would work with older servers.

I don't recall anyone questioning whether PQserverVersion() would work
with older servers.  Being able to tell what version the server is is
sort of the point, no?  If there were a restriction on what it would
work with, I would agree that that needs to be documented.  But I
don't think "yes, it really works" is a helpful use of documentation
space.
        regards, tom lane

>> Thus short, simple but meaningful examples which show how to do something
>> useful with all that in the documentation may help people take advantage
>> of these new features.
>
> I don't have an objection to providing an example.  I wasn't terribly
> impressed with Simon's version, but maybe we can do better.

That was just a quick idea to show how they could be used, probably it can 
be improved.

> I think it should be concrete, not partly abstract; and likely it needs 
> to be with \if not the variable proper.

ISTM that currently there is no "not". Maybe I do not understand your 
sentence.

>> Given my experience with "\d*", I'm not sure I would assume that a new
>> psql feature would work with older servers.
>
> I don't recall anyone questioning whether PQserverVersion() would work
> with older servers.  Being able to tell what version the server is is
> sort of the point, no?  If there were a restriction on what it would
> work with, I would agree that that needs to be documented.  But I
> don't think "yes, it really works" is a helpful use of documentation
> space.

Sure.

I can use psql without knowing that there is a libpq underneath, that it 
contains a PQserverVersion function implemented since pg7, and that the 
SERVER_VERSION_* variables introduced in pg10 or pg11 rely on that thus it 
should work with pg7/8/9 as well. It is not obvious, as a new feature 
could depend on any combination of server side functions, protocol 
extension, client library functions or client code...

-- 
Fabien.