Re: New variable server_version_num

Today on IRC David Fetter and some others were discussing version
numbers and we realized that although libpq now provides the version of
Postgres as a number, this is still a wheel that is being reinvented by
apps many times over, as it is not available any other way. Hence, a
small patch to provide a new variable "server_version_num", which is
almost the same as "server_version" but uses the handy PG_VERSION_NUM
which allows apps to do things like if ($version >= 80200) without
having to parse apart the value of server_version themselves.

--
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 200607292113
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Greg Sabino Mullane <greg(at)turnstep(dot)com> writes:
> small patch to provide a new variable "server_version_num", which is
> almost the same as "server_version" but uses the handy PG_VERSION_NUM
> which allows apps to do things like if ($version >= 80200) without
> having to parse apart the value of server_version themselves.

This seems pretty useless, as it will be many years before any app that
actually tries to deal with back server versions could rely on the
variable existing.

The correct solution is for client-side libraries to provide the
feature. libpq already does (PQserverVersion()) ... and it works
for any server version from about 6.4 forward ...

regards, tom lane

On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
> Greg Sabino Mullane <greg(at)turnstep(dot)com> writes:
> > small patch to provide a new variable "server_version_num", which
> > is almost the same as "server_version" but uses the handy
> > PG_VERSION_NUM which allows apps to do things like if ($version >=
> > 80200) without having to parse apart the value of server_version
> > themselves.
>
> This seems pretty useless, as it will be many years before any app
> that actually tries to deal with back server versions could rely on
> the variable existing.

In my case, its non-existence is a guarantee that the server version
number isn't high enough :)

> The correct solution is for client-side libraries to provide the
> feature.

Not if the app is written in SQL, as the bootstrap, regression test,
etc. code for modules frequently is.

> libpq already does (PQserverVersion()) ... and it works for any
> server version from about 6.4 forward ...

See above for why it's good also to have it surfaced to SQL :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
> On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
>> The correct solution is for client-side libraries to provide the
>> feature.

> Not if the app is written in SQL, as the bootstrap, regression test,
> etc. code for modules frequently is.

SQL doesn't really have any conditional ability strong enough to deal
with existence or non-existence of features. What are you hoping to
do, a CASE expression? Both arms of the CASE still have to parse,
so I remain unconvinced that there are real world uses.

regards, tom lane

On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
> >> The correct solution is for client-side libraries to provide the
> >> feature.
>
> > Not if the app is written in SQL, as the bootstrap, regression
> > test, etc. code for modules frequently is.
>
> SQL doesn't really have any conditional ability strong enough to
> deal with existence or non-existence of features. What are you
> hoping to do, a CASE expression? Both arms of the CASE still have
> to parse, so I remain unconvinced that there are real world uses.

Failure to parse means the transaction bails out, which is just what I
want in my case, as it disallows people attempting to run the
programs--they're for DBI-Link--on too early a version of PostgreSQL.
As there are some subtleties to the implementation, I need something
that quickly returns boolean or fails entirely when it detects same.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On 7/30/06, David Fetter <david(at)fetter(dot)org> wrote:
> Failure to parse means the transaction bails out, which is just what I
> want in my case, as it disallows people attempting to run the
> programs--they're for DBI-Link--on too early a version of PostgreSQL.
> As there are some subtleties to the implementation, I need something
> that quickly returns boolean or fails entirely when it detects same.

From an application development standpoint, it would be nice to have a
strictly numeric version returning function for checking server
compatibility.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

On Sun, Jul 30, 2006 at 12:17:57PM -0400, Jonah H. Harris wrote:
> On 7/30/06, David Fetter <david(at)fetter(dot)org> wrote:
> >Failure to parse means the transaction bails out, which is just
> >what I want in my case, as it disallows people attempting to run
> >the programs--they're for DBI-Link--on too early a version of
> >PostgreSQL. As there are some subtleties to the implementation, I
> >need something that quickly returns boolean or fails entirely when
> >it detects same.
>
> From an application development standpoint, it would be nice to have
> a strictly numeric version returning function for checking server
> compatibility.

It sure would :)

Cheers,
D (whose boolean function is the output of a numeric comparison
between the required server version and the one at hand)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
> >> The correct solution is for client-side libraries to provide the
> >> feature.
>
> > Not if the app is written in SQL, as the bootstrap, regression test,
> > etc. code for modules frequently is.
>
> SQL doesn't really have any conditional ability strong enough to deal
> with existence or non-existence of features. What are you hoping to
> do, a CASE expression? Both arms of the CASE still have to parse,
> so I remain unconvinced that there are real world uses.

There's also plpgsql, which afaik has no way to get the version number
(other than slogging though the output of version()).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Tue, Aug 01, 2006 at 12:37:48PM -0500, Jim C. Nasby wrote:
> On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote:
> > David Fetter <david(at)fetter(dot)org> writes:
> > > On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
> > >> The correct solution is for client-side libraries to provide
> > >> the feature.
> >
> > > Not if the app is written in SQL, as the bootstrap, regression
> > > test, etc. code for modules frequently is.
> >
> > SQL doesn't really have any conditional ability strong enough to
> > deal with existence or non-existence of features. What are you
> > hoping to do, a CASE expression? Both arms of the CASE still have
> > to parse, so I remain unconvinced that there are real world uses.

CREATE OR REPLACE FUNCTION version_new_enough(
in_version INTEGER
)
RETURNS BOOLEAN
LANGUAGE sql
AS $$
SELECT
COALESCE(
s.setting::INTEGER, /* Cast setting to integer if it's there */
$1 - 1 /* Otherwise, guarantee a lower number than the input */
) >= $1
FROM
(SELECT 'server_version_num'::text AS name) AS foo
LEFT JOIN
pg_catalog.pg_settings s
ON (foo.name = s.name)
$$;

> There's also plpgsql, which afaik has no way to get the version
> number (other than slogging though the output of version()).

Right. String-mashing is great when you have to do it, but this patch
sets it up so you don't have to. :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

Quoth david(at)fetter(dot)org (David Fetter):
> On Tue, Aug 01, 2006 at 12:37:48PM -0500, Jim C. Nasby wrote:
>> On Sun, Jul 30, 2006 at 11:27:33AM -0400, Tom Lane wrote:
>> > David Fetter <david(at)fetter(dot)org> writes:
>> > > On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
>> > >> The correct solution is for client-side libraries to provide
>> > >> the feature.
>> >
>> > > Not if the app is written in SQL, as the bootstrap, regression
>> > > test, etc. code for modules frequently is.
>> >
>> > SQL doesn't really have any conditional ability strong enough to
>> > deal with existence or non-existence of features. What are you
>> > hoping to do, a CASE expression? Both arms of the CASE still have
>> > to parse, so I remain unconvinced that there are real world uses.
>
> CREATE OR REPLACE FUNCTION version_new_enough(
> in_version INTEGER
> )
> RETURNS BOOLEAN
> LANGUAGE sql
> AS $$
> SELECT
> COALESCE(
> s.setting::INTEGER, /* Cast setting to integer if it's there */
> $1 - 1 /* Otherwise, guarantee a lower number than the input */
> ) >= $1
> FROM
> (SELECT 'server_version_num'::text AS name) AS foo
> LEFT JOIN
> pg_catalog.pg_settings s
> ON (foo.name = s.name)
> $$;
>
>> There's also plpgsql, which afaik has no way to get the version
>> number (other than slogging though the output of version()).
>
> Right. String-mashing is great when you have to do it, but this patch
> sets it up so you don't have to. :)

There's *some* data to be gotten from
select setting from pg_catalog.pg_settings where name = 'server_version';

Seems to me that value isn't without its uses...

cbbrowne(at)dba2:pgsql-HEAD/doc/src/sgml> for port in 5432 5533 5532 5882; do
for> psql -p $port -h localhost -d template1 -c "select '$port',
setting from pg_catalog.pg_settings where name like 'server_version';"
for> done
?column? | setting
----------+---------
5432 | 7.4.13
(1 row)

?column? | setting
----------+---------
5533 | 7.4.10
(1 row)

?column? | setting
----------+---------
5532 | 8.0.5
(1 row)

?column? | setting
----------+----------
5882 | 8.2devel
(1 row)

If I wanted to, it oughtn't be difficult to "string smash" those
settings into something very nearly useful...
--
"cbbrowne","@","gmail.com"
http://linuxfinances.info/info/rdbms.html
">in your opinion which is the best programming tools ?
The human brain and a keyboard." -- Nathan Wagner

On Tue, Aug 01, 2006 at 04:25:00PM -0400, Christopher Browne wrote:
> ?column? | setting
> ----------+---------
> 5432 | 7.4.13
> (1 row)
>
> ?column? | setting
> ----------+---------
> 5533 | 7.4.10
> (1 row)
>
> ?column? | setting
> ----------+---------
> 5532 | 8.0.5
> (1 row)
>
> ?column? | setting
> ----------+----------
> 5882 | 8.2devel
> (1 row)
>
> If I wanted to, it oughtn't be difficult to "string smash" those
> settings into something very nearly useful...

It may or may not be difficult, depending on your definition of
'difficult,' but it's very easy and reproducible to get something that
can be cast to integer and compared that way. The existence of
version-number-comparison libraries like version.pm points to the idea
that it is, in fact, difficult to compare versions in general.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote:
> Today on IRC David Fetter and some others were discussing version
> numbers and we realized that although libpq now provides the version
> of Postgres as a number, this is still a wheel that is being
> reinvented by apps many times over, as it is not available any other
> way. Hence, a small patch to provide a new variable
> "server_version_num", which is almost the same as "server_version"
> but uses the handy PG_VERSION_NUM which allows apps to do things
> like if ($version >= 80200) without having to parse apart the value
> of server_version themselves.

What's the status on applying this patch?

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter wrote:
> On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote:
> > Today on IRC David Fetter and some others were discussing version
> > numbers and we realized that although libpq now provides the version
> > of Postgres as a number, this is still a wheel that is being
> > reinvented by apps many times over, as it is not available any other
> > way. Hence, a small patch to provide a new variable
> > "server_version_num", which is almost the same as "server_version"
> > but uses the handy PG_VERSION_NUM which allows apps to do things
> > like if ($version >= 80200) without having to parse apart the value
> > of server_version themselves.
>
> What's the status on applying this patch?

It is still in my mailbox. I am thinking it should be added.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote:
> Today on IRC David Fetter and some others were discussing version
> numbers and we realized that although libpq now provides the version of
> Postgres as a number, this is still a wheel that is being reinvented by
> apps many times over, as it is not available any other way. Hence, a
> small patch to provide a new variable "server_version_num", which is
> almost the same as "server_version" but uses the handy PG_VERSION_NUM
> which allows apps to do things like if ($version >= 80200) without
> having to parse apart the value of server_version themselves.

Here's an SQL function which does the same thing. I've had it tested
back through 7.4x, and it should work back to the 7.3 series, although
I haven't tested it there. Thanks to Andrew of Supernews for the
short version :)

SELECT
sum(
substring(
split_part(
current_setting(
'server_version'
),
'.'
,
i
)
FROM
'^[[:digit:]]+'
)::integer * 10^(6-i*2) ) AS server_version_integer
FROM (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
) AS s(i);

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

Patch applied. Thanks.

---------------------------------------------------------------------------

Greg Sabino Mullane wrote:
-- Start of PGP signed section.
> Today on IRC David Fetter and some others were discussing version
> numbers and we realized that although libpq now provides the version of
> Postgres as a number, this is still a wheel that is being reinvented by
> apps many times over, as it is not available any other way. Hence, a
> small patch to provide a new variable "server_version_num", which is
> almost the same as "server_version" but uses the handy PG_VERSION_NUM
> which allows apps to do things like if ($version >= 80200) without
> having to parse apart the value of server_version themselves.
>
> --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 200607292113
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
>
>

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +