Tablespace Default Behavior

More Info Edit: Also, the database is in the my_space tablespace. The
location for the my_space tablespace is a different folder than the $PGDATA
folder.

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5952917.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

harpagornis <shenlong@runbox.com> writes:

When I create a table, like this:
CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space;
and then I do:
select * from pg_tables where schemaname ='myschema';
the tablespace is blank for the new table. When I look in PgAdmin, the
tablespace shows up as "pg_default." What do I need to change to create
the table in the desired tablespace?

As you mention in your followup, the database's default tablespace is
'my_space', so the table is actually in the correct tablespace.

For implementation reasons, we force tables that are placed in the
database's default tablespace to be recorded as "stored in database's
tablespace", which shows up as blank in pg_tables. That means that if you
change the database's tablespace the table will move along with the
rest of the database. You might consider that either a feature or a
bug depending on your use-case, but that's how it works ...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

That is what I suspected, and thank you for the explanation. I think it is
misleading and a bug in PgAdmin for explicitly listing the tablespace as
pg_default.

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5952929.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/29/2017 09:18 PM, harpagornis wrote:

That is what I suspected, and thank you for the explanation. I think it is
misleading and a bug in PgAdmin for explicitly listing the tablespace as
pg_default.

Listing it where?

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5952929.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In PgAdmin, when I right-click the table, in the Properties pane on the right
side of the screen, the tablespace is listed as "pg_default"

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5953028.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/30/2017 07:35 AM, harpagornis wrote:

In PgAdmin, when I right-click the table, in the Properties pane on the right
side of the screen, the tablespace is listed as "pg_default"

What we know. Correct me if I am wrong:

1) In postgresql.conf you have:
default_tablespace = ''

2) You created a table in the database:

CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE
my_space;

FYI, the TABLESPACE is redundant as the table would created in my_space
anyway as it is the default for the database

3) When you queried pg_tables, the tablespace field is NULL for the
table. Which would be correct:
https://www.postgresql.org/docs/9.6/static/catalogs.html
tablespace name pg_tablespace.spcname Name of tablespace containing
table (null if default for database)

4) pgAdmin shows the tablespace as being pg_default for the table.

What we do not know:

1) Postgres version

2) pgAdmin version

3) select spcname from pg_database join pg_tablespace on
pg_database.dattablespace=pg_tablespace.oid where datname= your_db_name;

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5953028.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Postgres version is 9.4

PgAdmin version is 1.0

The value returned by the suggested query is correctly reported as
'my_space'.

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5953046.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/30/2017 08:36 AM, harpagornis wrote:

Postgres version is 9.4

PgAdmin version is 1.0

Alright seems this is a known issue:

https://redmine.postgresql.org/issues/2069

You will need a Postgres coummunity account:

https://www.postgresql.org/account/signup/

to see the issue though.

Though, according to the issue it is fixed in pgAdmin4 1.2. Current
stable release is 1.3, so I would upgrade your pgAdmin.

The value returned by the suggested query is correctly reported as
'my_space'.

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5953046.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Nope, I installed v4.1.3 and it is the same, pg_default appears as the
tablespace. Even when I use the drop down list in the right pane of PgAdmin
to select the my_space tablespace, and then click the Save button, it does
not change.

--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5953081.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/30/2017 10:02 AM, harpagornis wrote:

Nope, I installed v4.1.3 and it is the same, pg_default appears as the
tablespace. Even when I use the drop down list in the right pane of PgAdmin
to select the my_space tablespace, and then click the Save button, it does
not change.

pgAdmin is a separate project from the Postgres server so I would
suggest adding that information to the existing issue in their issue
tracker:

https://redmine.postgresql.org/issues/2069

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general