Обсуждение: BUG #15901: Tablespace showing as null in psql and pgadmin
The following bug has been logged on the website: Bug reference: 15901 Logged by: Ishan Joshi Email address: ishanjoshi@live.com PostgreSQL version: 11.2 Operating system: Red Hat Enterprise Linux Server release 7.3 Description: Hi Team, As I have created separate tablespace (Not using default tablespace pg_default or pg_global) for my new database. I have tried to create tables with and without tablespace parameter and both these tables while checking in pg_table, tablespace column showing as null. I tried to extract from other tables but getting tablespace as null only. Example: -- Create tablespace CREATE TABLESPACE testts OWNER postgres LOCATION '/users/pgtablespaces/testts'; --Create database CREATE DATABASE testdb WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'en_US.UTF8' TABLESPACE = testts CONNECTION LIMIT = -1 TEMPLATE = template0; Create table test1c (Id integer, name varchar(10)) tablespace testts; Create table test2c (Id integer, name varchar(10)) ; Output: select schemaname,tablename,tablespace from pg_tables where tablename in('test1c','test2c'); testschema | test1c | public | test2c | public | test1c | It seems to be bug here as I tried to check the same in pgAdmin tool which is also having the same result. Thanks
PG Bug reporting form <noreply@postgresql.org> writes: > As I have created separate tablespace (Not using default tablespace > pg_default or pg_global) for my new database. I have tried to create tables > with and without tablespace parameter and both these tables while checking > in pg_table, tablespace column showing as null. I tried to extract from > other tables but getting tablespace as null only. The case you showed isn't a bug: creating a table that's specified to be in the database's default tablespace is treated the same as not specifying any tablespace. This is because if you change the database's default tablespace later, such a table will be moved along with every other table that's in the database's default tablespace. regards, tom lane
Hi Tom,
Thanks for clarification but this should be part of functionality. It should show the non default tablespace if any. This is like tightly coupled as postgres do not want to user to use other than default tablespace.
In my application, tablespace is requires to collect information for table. I have tried the other ways to extract it but I am failed to extract it.
can you please help , How we can extract the details of User defined tablespace for the table created under it.
Thanks & Regards,
ISHAN JOSHIFrom: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, July 9, 2019 8:09 PM
To: ishanjoshi@live.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
Sent: Tuesday, July 9, 2019 8:09 PM
To: ishanjoshi@live.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
PG Bug reporting form <noreply@postgresql.org> writes:
> As I have created separate tablespace (Not using default tablespace
> pg_default or pg_global) for my new database. I have tried to create tables
> with and without tablespace parameter and both these tables while checking
> in pg_table, tablespace column showing as null. I tried to extract from
> other tables but getting tablespace as null only.
The case you showed isn't a bug: creating a table that's specified to be
in the database's default tablespace is treated the same as not specifying
any tablespace. This is because if you change the database's default
tablespace later, such a table will be moved along with every other table
that's in the database's default tablespace.
regards, tom lane
> As I have created separate tablespace (Not using default tablespace
> pg_default or pg_global) for my new database. I have tried to create tables
> with and without tablespace parameter and both these tables while checking
> in pg_table, tablespace column showing as null. I tried to extract from
> other tables but getting tablespace as null only.
The case you showed isn't a bug: creating a table that's specified to be
in the database's default tablespace is treated the same as not specifying
any tablespace. This is because if you change the database's default
tablespace later, such a table will be moved along with every other table
that's in the database's default tablespace.
regards, tom lane
Ishan joshi schrieb am 10.07.2019 um 07:01: > It should show the non default tablespace if any. It does - it *only* shows the non-default tablespace. > How we can extract the details of User defined tablespace for the table created under it. You got an answer to that question on Stackoverflow: https://stackoverflow.com/questions/56936396 Thomas
On Tue, Jul 9, 2019 at 10:01 PM Ishan joshi <ishanjoshi@live.com> wrote:
Thanks for clarification but this should be part of functionality. It should show the non default tablespace if any. This is like tightly coupled as postgres do not want to user to use other than default tablespace.
PostgreSQL doesn't care one way or the other, and maybe it could have been done differently, but at this point it isn't worth changing.
In my application, tablespace is requires to collect information for table. I have tried the other ways to extract it but I am failed to extract it.
As demonstrated elsewhere there is a relatively simple solution to the problem available (which you've only really vaguely defined as "information"). Yes, catalog queries can be difficult to put together but for the most part learning curve trumps spending significant developer time improving secondary functionality that can be made to work.
can you please help , How we can extract the details of User defined tablespace for the table created under it.
The system information function: pg_tablespace_location(
tablespace_oid
) provides the only piece of information not present on pg_tablespace - location.Though if you need to know the location of the pg_default tablespace you will need to "show data_directory" (or the functional equivalent) since the two are the same by definition and "don't repeat yourself" is again in play (though this seems more likely amenable to change since its a user-oriented function and not a fundamental property of the system catalogs).
For the hierarchy (default) stuff as Thomas points out the StackOverflow posts covers the catalog query needed to query this model.
David J.
Thanks David, Thomas for your response.
Thanks & Regards,![](http://gfx2.hotmail.com/mail/w3/ltr/emoticons/computer.gif)
ISHAN JOSHI![](http://gfx2.hotmail.com/mail/w3/ltr/emoticons/computer.gif)
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, July 10, 2019 11:09 AM
To: Ishan joshi
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
Sent: Wednesday, July 10, 2019 11:09 AM
To: Ishan joshi
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
On Tue, Jul 9, 2019 at 10:01 PM Ishan joshi <ishanjoshi@live.com> wrote:
Thanks for clarification but this should be part of functionality. It should show the non default tablespace if any. This is like tightly coupled as postgres do not want to user to use other than default tablespace.
PostgreSQL doesn't care one way or the other, and maybe it could have been done differently, but at this point it isn't worth changing.
In my application, tablespace is requires to collect information for table. I have tried the other ways to extract it but I am failed to extract it.
As demonstrated elsewhere there is a relatively simple solution to the problem available (which you've only really vaguely defined as "information"). Yes, catalog queries can be difficult to put together but for the most part learning curve trumps spending significant developer time improving secondary functionality that can be made to work.
can you please help , How we can extract the details of User defined tablespace for the table created under it.
The system information function: pg_tablespace_location(
tablespace_oid
) provides the only piece of information not present on pg_tablespace - location.Though if you need to know the location of the pg_default tablespace you will need to "show data_directory" (or the functional equivalent) since the two are the same by definition and "don't repeat yourself" is again in play (though this seems more likely amenable to change since its a user-oriented function and not a fundamental property of the system catalogs).
For the hierarchy (default) stuff as Thomas points out the StackOverflow posts covers the catalog query needed to query this model.
David J.