BUG #15901: Tablespace showing as null in psql and pgadmin

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15901: Tablespace showing as null in psql and pgadmin

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15901
Logged by:          Ishan Joshi
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15901: Tablespace showing as null in psql and pgadmin

Tom Lane-2
PG Bug reporting form <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15901: Tablespace showing as null in psql and pgadmin

Ishan joshi
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 JOSHI


From: Tom Lane <[hidden email]>
Sent: Tuesday, July 9, 2019 8:09 PM
To: [hidden email]
Cc: [hidden email]
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
 
PG Bug reporting form <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15901: Tablespace showing as null in psql and pgadmin

Thomas Kellerer
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


Reply | Threaded
Open this post in threaded view
|

Re: BUG #15901: Tablespace showing as null in psql and pgadmin

David G Johnston
In reply to this post by Ishan joshi
On Tue, Jul 9, 2019 at 10:01 PM Ishan joshi <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15901: Tablespace showing as null in psql and pgadmin

Ishan joshi
Thanks David, Thomas for your response.

Thanks & Regards,
ISHAN JOSHI


From: David G. Johnston <[hidden email]>
Sent: Wednesday, July 10, 2019 11:09 AM
To: Ishan joshi
Cc: [hidden email]
Subject: Re: BUG #15901: Tablespace showing as null in psql and pgadmin
 
On Tue, Jul 9, 2019 at 10:01 PM Ishan joshi <[hidden email]> 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.