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@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 200607292113
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Attachments:
server_version_num.patchtext/x-patch; charset=ISO-8859-1; name=server_version_num.patchDownload
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.71
diff -u -c -r1.71 config.sgml
*** doc/src/sgml/config.sgml 27 Jul 2006 08:30:41 -0000 1.71
--- doc/src/sgml/config.sgml 29 Jul 2006 23:31:58 -0000
***************
*** 4141,4146 ****
--- 4141,4159 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-server-version-num" xreflabel="server_version_num">
+ <term><varname>server_version_num</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>server_version_num</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Reports the version number of the server as an integer. It is determined
+ by the value of <literal>PG_VERSION_NUM</> when building the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect1>
Index: src/backend/utils/misc/check_guc
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/check_guc,v
retrieving revision 1.9
diff -u -c -r1.9 check_guc
*** src/backend/utils/misc/check_guc 25 Jul 2005 22:12:33 -0000 1.9
--- src/backend/utils/misc/check_guc 29 Jul 2006 23:31:59 -0000
***************
*** 18,26 ****
## can be ignored
INTENTIONALLY_NOT_INCLUDED="autocommit debug_deadlocks exit_on_error \
is_superuser lc_collate lc_ctype lc_messages lc_monetary lc_numeric lc_time \
! pre_auth_delay role seed server_encoding server_version session_authorization \
! trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks trace_notify \
! trace_userlocks transaction_isolation transaction_read_only \
zero_damaged_pages"
### What options are listed in postgresql.conf.sample, but don't appear
--- 18,26 ----
## can be ignored
INTENTIONALLY_NOT_INCLUDED="autocommit debug_deadlocks exit_on_error \
is_superuser lc_collate lc_ctype lc_messages lc_monetary lc_numeric lc_time \
! pre_auth_delay role seed server_encoding server_version server_version_int \
! session_authorization trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks \
! trace_notify trace_userlocks transaction_isolation transaction_read_only \
zero_damaged_pages"
### What options are listed in postgresql.conf.sample, but don't appear
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.333
diff -u -c -r1.333 guc.c
*** src/backend/utils/misc/guc.c 29 Jul 2006 03:02:56 -0000 1.333
--- src/backend/utils/misc/guc.c 29 Jul 2006 23:31:59 -0000
***************
*** 220,225 ****
--- 220,226 ----
static char *regex_flavor_string;
static char *server_encoding_string;
static char *server_version_string;
+ static int server_version_num;
static char *timezone_string;
static char *timezone_abbreviations_string;
static char *XactIsoLevel_string;
***************
*** 1617,1622 ****
--- 1618,1634 ----
DEFAULT_EFFECTIVE_CACHE_SIZE, 1, INT_MAX, NULL, NULL
},
+ {
+ /* Can't be set in postgresql.conf */
+ {"server_version_num", PGC_INTERNAL, PRESET_OPTIONS,
+ gettext_noop("Shows the server version as an integer."),
+ NULL,
+ GUC_REPORT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ },
+ &server_version_num,
+ PG_VERSION_NUM, PG_VERSION_NUM, PG_VERSION_NUM, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL
Greg Sabino Mullane <greg@turnstep.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@turnstep.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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
David Fetter <david@fetter.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@fetter.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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On 7/30/06, David Fetter <david@fetter.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@enterprisedb.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@fetter.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@fetter.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@fetter.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@pervasive.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@fetter.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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Quoth david@fetter.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@fetter.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@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@fetter.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@fetter.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@momjian.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@fetter.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@turnstep.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@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +