Thread: Tablespace column value null on select * from pg_tables
Hi,
Server Version 9.5
I found this old thread on something similar to the results I'm getting: https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com
But in my case, I have a database that's in a user-defined tablespace (data2) and all the tables/indexes there are also in data2 and I want to do a select into a table the results of all the tables / tablespaces they are in that database...when doing this:
SELECT distinct tablespace
FROM pg_tables;
I get 2 rows: null and pg_global (I think to expect null for pg_default, but if the table is in a user-defined tablespace, should we expect it to show it, in my case, data2?)
or
SELECT distinct tablespace
FROM pg_indexes
I get 3 rows: null, pg_global and pg_default
and this: SELECT * FROM pg_tablespace;
I get 3 rows: pg_default, pg_global and data2.
Using pgadmin, getting properties for the DB / tables, it shows data2.
What I want to do is move all the tables / indexes from data2 to pg_default (we added more space to the pg_default mount.)
Now, I did a pg_dump/restore for one database which took a long time and we now know the process for that, so on the next database we have we want to do it where we use the following commands:
ALTER DATABASE mydatabase SET TABLESPACE pg_default;
alter table all in tablespace data2 set tablespace pg_default;
But, what I'm trying to accomplish here is, aside from checking the filesystem, like df- h, to see it was moved or properties on each table (too many) I just want to run a query that will insert into a table all the tables and their tablespace names and when the above two commands (3rd will be moving indexes) run the query again and verify everything has moved from data2 to pg_default.
Thanks for your help in advance.
Alex
Sent with ProtonMail Secure Email.
On 7/15/19 11:35 AM, Alex Williams wrote: > Hi, > > Server Version 9.5 > > I found this old thread on something similar to the results I'm getting: > https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com > > But in my case, I have a database that's in a user-defined tablespace > (data2) and all the tables/indexes there are also in data2 and I want to > do a select into a table the results of all the tables / tablespaces > they are in that database...when doing this: > > > SELECT distinct tablespace > FROM pg_tables; > > I get 2 rows: null and pg_global (I think to expect null for pg_default, > but if the table is in a user-defined tablespace, should we expect it to > show it, in my case, data2?) Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE new_tablespace This makes the tablespace the default for the database and the default shows up as null in pg_tables: https://www.postgresql.org/docs/9.5/view-pg-tables.html tablespace name pg_tablespace.spcname Name of tablespace containing table (null if default for database) > > or > > SELECT distinct tablespace > FROM pg_indexes > > I get 3 rows: null, pg_global and pg_default > > and this: SELECT * FROM pg_tablespace; > > I get 3 rows: pg_default, pg_global and data2. > > Using pgadmin, getting properties for the DB / tables, it shows data2. > > What I want to do is move all the tables / indexes from data2 to > pg_default (we added more space to the pg_default mount.) > > Now, I did a pg_dump/restore for one database which took a long time and > we now know the process for that, so on the next database we have we > want to do it where we use the following commands: > > ALTER DATABASE mydatabase SET TABLESPACE pg_default; > alter table all in tablespace data2 set tablespace pg_default; > > But, what I'm trying to accomplish here is, aside from checking the > filesystem, like df- h, to see it was moved or properties on each table > (too many) I just want to run a query that will insert into a table all > the tables and their tablespace names and when the above two commands > (3rd will be moving indexes) run the query again and verify everything > has moved from data2 to pg_default. > > Thanks for your help in advance. > > Alex > > > Sent with ProtonMail <https://protonmail.com> Secure Email. > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, "Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE new_tablespaceThis makes the tablespace the default for the database and the default shows up as null in pg_tables: https://www.postgresql.org/docs/9.5/view-pg-tables.htmltablespace name pg_tablespace.spcname Name of tablespace containingtable (null if default for database)" Thanks, but I didn't do that. I have an existing database that's on data2 and haven't ran any command yet to change the dbtablespace. When the db was created two years ago, it went directly to data2 along with any table/indexes to data2. Thesecond command is the command I want to run but haven't ran it yet since I want to get the tablespaces for the tableson the db inserted into a table prior, so I can make sure all the tables in data2 go into pg_default by running thequery again and seeing what tablespace they are in (at this point, it should probably be null for the tablespace namesignifying it's pg_default.) PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties), but thequeries I've used from various sources like stackoverflow don't provide the correct named tablespace. Thanks, Alex Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Monday, July 15, 2019 3:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 7/15/19 11:35 AM, Alex Williams wrote: > > > Hi, > > Server Version 9.5 > > I found this old thread on something similar to the results I'm getting: > > https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com > > But in my case, I have a database that's in a user-defined tablespace > > (data2) and all the tables/indexes there are also in data2 and I want to > > do a select into a table the results of all the tables / tablespaces > > they are in that database...when doing this: > > SELECT distinct tablespace > > FROM pg_tables; > > I get 2 rows: null and pg_global (I think to expect null for pg_default, > > but if the table is in a user-defined tablespace, should we expect it to > > show it, in my case, data2?) > > Not if you did: > > CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] > > ALTER DATABASE name SET TABLESPACE new_tablespace > > This makes the tablespace the default for the database and the default > shows up as null in pg_tables: > > https://www.postgresql.org/docs/9.5/view-pg-tables.html > > tablespace name pg_tablespace.spcname Name of tablespace containing > table (null if default for database) > > > or > > SELECT distinct tablespace > > FROM pg_indexes > > I get 3 rows: null, pg_global and pg_default > > and this: SELECT * FROM pg_tablespace; > > I get 3 rows: pg_default, pg_global and data2. > > Using pgadmin, getting properties for the DB / tables, it shows data2. > > What I want to do is move all the tables / indexes from data2 to > > pg_default (we added more space to the pg_default mount.) > > Now, I did a pg_dump/restore for one database which took a long time and > > we now know the process for that, so on the next database we have we > > want to do it where we use the following commands: > > ALTER DATABASE mydatabase SET TABLESPACE pg_default; > > > alter table all in tablespace data2 set tablespace pg_default; > > But, what I'm trying to accomplish here is, aside from checking the > > filesystem, like df- h, to see it was moved or properties on each table > > (too many) I just want to run a query that will insert into a table all > > the tables and their tablespace names and when the above two commands > > (3rd will be moving indexes) run the query again and verify everything > > has moved from data2 to pg_default. > > Thanks for your help in advance. > > Alex > > Sent with ProtonMail https://protonmail.com Secure Email. > > -- > > Adrian Klaver > adrian.klaver@aklaver.com
Alex Williams schrieb am 15.07.2019 um 20:35: > But in my case, I have a database that's in a user-defined tablespace > (data2) and all the tables/indexes there are also in data2 and I want > to do a select into a table the results of all the tables / > tablespaces they are in that database...when doing this: >> SELECT distinct tablespace FROM pg_tables; > > I get 2 rows: null and pg_global (I think to expect null for > pg_default, but if the table is in a user-defined tablespace, should > we expect it to show it, in my case, data2?) If data2 is the default tablespace of the database, then this is expected. The tablespace column is null in pg_tables if the table is located in the default tablespace of the database. See here: https://www.postgresql.org/message-id/flat/15901-e5cfe2dd7298a3a4%40postgresql.org And the answer on SO: https://stackoverflow.com/a/56950950 Thomas
On 7/15/19 12:53 PM, Alex Williams wrote: > Hi Adrian, > > "Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE new_tablespaceThis makes the tablespace the default for the database and the default shows up as null in pg_tables: https://www.postgresql.org/docs/9.5/view-pg-tables.htmltablespace name pg_tablespace.spcname Name of tablespace containingtable (null if default for database)" > > Thanks, but I didn't do that. I have an existing database that's on data2 and haven't ran any command yet to change thedb tablespace. When the db was created two years ago, it So someone else ran the command the end result is the same, data2 is the default tablespace for the db so you get NULL in the tablespace column in pg_tables. went directly to data2 along with any table/indexes to data2. The second command is the command I want to run but haven't ran it yet since I want to get the tablespaces for the tables on the db inserted into a table prior, so I can make sure all the tables in data2 go into pg_default by running the query again and seeing what tablespace they are in (at this point, it should probably be null for the tablespace name signifying it's pg_default.) That is where you are getting confused, there are two defaults in play; pg_default and the db default. pg_default: https://www.postgresql.org/docs/9.5/manage-ag-tablespaces.html "Two tablespaces are automatically created when the database cluster is initialized. The pg_global tablespace is used for shared system catalogs. The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE)." db default: From same link above. "The tablespace associated with a database is used to store the system catalogs of that database. Furthermore, it is the default tablespace used for tables, indexes, and temporary files created within the database, if no TABLESPACE clause is given and no other selection is specified by default_tablespace or temp_tablespaces (as appropriate). If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from." In either case that tablespace becomes the default for the db and shows up as NULL in pg_tables. > > PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties), butthe queries I've used from various sources like stackoverflow don't provide the correct named tablespace. It is probably doing something like: SELECT datname, spcname FROM pg_database AS pd JOIN pg_tablespace AS pt ON pd.dattablespace = pt.oid; > > Thanks, > > Alex > > > Sent with ProtonMail Secure Email. > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Thomas and Adrian,
I'm sorry on my part, you both are correct, thanks again for your help.
What I did today that worked to move everything from data2 to pg_default was:
1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;
ERROR: some relations of database "mydatabase" are already in tablespace "pg_default"
HINT: You must move them back to the database's default tablespace before using this command.
2. Ran this to get the objects not in data2:
SELECT t.relname, t.reltablespace, sp.spcname
FROM pg_class t LEFT JOIN
pg_tablespace sp ON sp.oid = t.reltablespace where spcname is not null
3. Ran this on those objects not on data2 (the current default TS)
alter index public.my_index set tablespace data2;
4. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;
Viola, no issues, and the tables and indexes are now on pg_default tablespace.
Thanks again to both of you!
Alex
(Just a note: The name of the actual DB / objects manually moved were renamed for this public post)
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Monday, July 15, 2019 8:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 7/15/19 12:53 PM, Alex Williams wrote:
>
> > Hi Adrian,
> > "Not if you did: CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ] ALTER DATABASE name SET TABLESPACE
new_tablespaceThis makes the tablespace the default for the database and the default shows up as null in pg_tables:
https://www.postgresql.org/docs/9.5/view-pg-tables.htmltablespace name pg_tablespace.spcname Name of tablespace
containingtable (null if default for database)"
> > Thanks, but I didn't do that. I have an existing database that's on data2 and haven't ran any command yet to change
thedb tablespace. When the db was created two years ago, it
>
> So someone else ran the command the end result is the same, data2 is the
> default tablespace for the db so you get NULL in the tablespace column
> in pg_tables.
>
> went directly to data2 along with any table/indexes to data2. The second
> command is the command I want to run but haven't ran it yet since I want
> to get the tablespaces for the tables on the db inserted into a table
> prior, so I can make sure all the tables in data2 go into pg_default by
> running the query again and seeing what tablespace they are in (at this
> point, it should probably be null for the tablespace name signifying
> it's pg_default.)
>
> That is where you are getting confused, there are two defaults in play;
> pg_default and the db default.
>
> pg_default:
>
> https://www.postgresql.org/docs/9.5/manage-ag-tablespaces.html
> "Two tablespaces are automatically created when the database cluster is
> initialized. The pg_global tablespace is used for shared system
> catalogs. The pg_default tablespace is the default tablespace of the
> template1 and template0 databases (and, therefore, will be the default
> tablespace for other databases as well, unless overridden by a
> TABLESPACE clause in CREATE DATABASE)."
>
> db default:
>
> From same link above.
>
> "The tablespace associated with a database is used to store the system
> catalogs of that database. Furthermore, it is the default tablespace
> used for tables, indexes, and temporary files created within the
> database, if no TABLESPACE clause is given and no other selection is
> specified by default_tablespace or temp_tablespaces (as appropriate). If
> a database is created without specifying a tablespace for it, it uses
> the same tablespace as the template database it is copied from."
>
> In either case that tablespace becomes the default for the db and shows
> up as NULL in pg_tables.
>
> > PgAdmin seems has the proper query to get the db and table tablespace names (right click table/select properties),
butthe queries I've used from various sources like stackoverflow don't provide the correct named tablespace.
>
> It is probably doing something like:
>
> SELECT
> datname, spcname
> FROM
> pg_database AS pd
> JOIN
> pg_tablespace AS pt
> ON
> pd.dattablespace = pt.oid;
>
> > Thanks,
> > Alex
> > Sent with ProtonMail Secure Email.
>
> --
>
> Adrian Klaver
> adrian.klaver@aklaver.com
On 2019-07-16 19:20:24 +0000, Alex Williams wrote:
> 1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;
>
> ERROR: some relations of database "mydatabase" are already in tablespace "pg_default"
>
> HINT: You must move them back to the database's default tablespace before using this command.
What's the reason for this error? Wouldn't it be simpler to leave
relations alone which are already in the correct tablespace?
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment
"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2019-07-16 19:20:24 +0000, Alex Williams wrote:
>> 1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;
>> ERROR: some relations of database "mydatabase" are already in tablespace "pg_default"
>> HINT: You must move them back to the database's default tablespace before using this command.
> What's the reason for this error? Wouldn't it be simpler to leave
> relations alone which are already in the correct tablespace?
It'd be simpler, it'd also be wrong.
The difficulty here is that ALTER DATABASE executes from a different
database, so it can't examine or change pg_class.reltablespace, so
it can't fix values that it would make inconsistent.
So imagine that we have a database in tablespace X, containing one
table T1 in tablespace Y. All the other entries in that database's
reltablespace column are zero signifying "it's in the database's default
tablespace". The one reference to tablespace X is in the shared catalog
pg_database, where we can see and change it during ALTER DATABASE.
Now let's do ALTER DATABASE ... SET TABLESPACE Y, and suppose that the
error check is not there. We move everything in X into Y, and we update
pg_database.dattablespace, but we don't change the reltablespace column.
Things are not actually broken at this point; T1's reltablespace says
Y and indeed that's where it is.
Now let's do ALTER DATABASE ... SET TABLESPACE Z. From outside the
database, all we can see is that all its files are physically in Y,
so we move them all to Z. Including T1. But its reltablespace entry
still says Y, so now things *are* broken.
(Another small issue here is that table relfilenodes are only
guaranteed unique per tablespace per database, so that there is
a hazard of OID collision between T1 and some other table being
moved into tablespace Y. We could fix that, but only if we
can change pg_class.relfilenode...)
So this can't be fixed without having a way to access and change
catalogs from a different database, which is a large can of worms
we don't want to open for such a small feature.
regards, tom lane
On 7/28/19 1:24 PM, Peter J. Holzer wrote: > On 2019-07-16 19:20:24 +0000, Alex Williams wrote: >> 1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default; >> >> ERROR: some relations of database "mydatabase" are already in tablespace "pg_default" >> >> HINT: You must move them back to the database's default tablespace before using this command. > > What's the reason for this error? Wouldn't it be simpler to leave > relations alone which are already in the correct tablespace? ~/src/backend/commands/dbcommands.c /* * Check for existence of files in the target directory, i.e., objects of * this database that are already in the target tablespace. We can't * allow the move in such a case, because we would need to change those * relations' pg_class.reltablespace entries to zero, and we don't have * access to the DB's pg_class to do so. */ > > hp > -- Adrian Klaver adrian.klaver@aklaver.com