- tablespace and directory

On Wed, Apr 18, 2012 at 6:22 PM, F. BROUARD / SQLpro <
sqlpro@club-internet.fr> wrote:

Hi there,

when creating a tablespace pointing to an existing directory, PG create a
subderictory with this pattern :

PG_???_!!!!!!!!!

where ??? is the version (by instance 9.1)

but I don't know what is !!!!!!!!!, actually 201105231

I can imagine that is a build version, but how can I obtain this number by
a SQL Query ?

Thats a system catalog version number. You can retrieve with pg_controldata

Eg:

-bash-4.1$ ./pg_controldata
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5719586032196955957
Database cluster state: in production

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Thanks !

Le 18/04/2012 15:35, Raghavendra a écrit :

On Wed, Apr 18, 2012 at 6:22 PM, F. BROUARD / SQLpro
<sqlpro@club-internet.fr <mailto:sqlpro@club-internet.fr>> wrote:

Hi there,

when creating a tablespace pointing to an existing directory, PG
create a subderictory with this pattern :

PG_???_!!!!!!!!!

where ??? is the version (by instance 9.1)

but I don't know what is !!!!!!!!!, actually 201105231

I can imagine that is a build version, but how can I obtain this
number by a SQL Query ?

Thats a system catalog version number. You can retrieve with pg_controldata

Eg:

-bash-4.1$ ./pg_controldata
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5719586032196955957
Database cluster state: in production

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

On Wed, Apr 18, 2012 at 6:22 PM, F. BROUARD / SQLpro <
sqlpro@club-internet.fr> wrote:

Hi there,

when creating a tablespace pointing to an existing directory, PG create a
subderictory with this pattern :

PG_???_!!!!!!!!!

where ??? is the version (by instance 9.1)

but I don't know what is !!!!!!!!!, actually 201105231

I can imagine that is a build version, but how can I obtain this number by
a SQL Query ?

Thanks a lot

on my system i am seeing something like:
PG_9.2_201202083

Its basically generated as:

"PG_ <PG_MAJORVERSION>_<CATALOG_VERSION_NO>"

Further you can check these values with pg_controldata as:

./pg_controldata db/data
pg_control version number: 922
Catalog version number: 201202083
Database system identifier: 5732255266599944159
....

--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb

Hi,

is there anyway to have the value of Catalog version number in a query ?
I do not see it in pg_settings...

A +

Le 18/04/2012 15:35, Raghavendra a écrit :

On Wed, Apr 18, 2012 at 6:22 PM, F. BROUARD / SQLpro
<sqlpro@club-internet.fr <mailto:sqlpro@club-internet.fr>> wrote:

Hi there,

when creating a tablespace pointing to an existing directory, PG
create a subderictory with this pattern :

PG_???_!!!!!!!!!

where ??? is the version (by instance 9.1)

but I don't know what is !!!!!!!!!, actually 201105231

I can imagine that is a build version, but how can I obtain this
number by a SQL Query ?

Thats a system catalog version number. You can retrieve with pg_controldata

Eg:

-bash-4.1$ ./pg_controldata
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5719586032196955957
Database cluster state: in production

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

AFAIK, I dont see any catalog's giving that information.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Wed, Apr 18, 2012 at 7:20 PM, F. BROUARD / SQLpro <
sqlpro@club-internet.fr> wrote: