Re: [HACKERS] pg_tables bug?

[Gaetano Mendola]

On Sat, Dec 19, 2015, 01:50 Andrew Dunstan  wrote:

>
>
>
>
> On 12/18/2015 05:18 PM, Gaetano Mendola wrote:
> > From documentation about "CREATE DATABASE name WITH TABLESAPCE =
> > tablespace_name":
> >
> > tablespace_name
> > The name of the tablespace that will be associated with the new
> > database, or DEFAULT to
> > use the template database's tablespace. This tablespace will be the
> > default tablespace used
> > for objects created in this database. See CREATE TABLESPACE for more
> > information.
> >
> > I'm sure that my tables are created in the name space but those are
> > not reported either in
> > pg_tables, either in pg_dump or by \d.
>
> 1. Please don't top-post on the PostgreSQL lists. See
> 
>
> 2. The system is working as designed and as documented - see the
> comments in the docs on pg_tables. If nothing is shown for the table's
> tablespace then it will be in the default tablespace for the database.
> That's what you're seeing. You appear to be assuming incorrectly that it
> means that the table will be in the system's default tablespace.
>

I did a reply using a not correctly setup client sorry for it. I'm not new
to this list. I understood now how it works. Having many database and many
tablespace is a nightmare this way. I will make my own view to fix the
annoyance.

>

Re: [HACKERS] pg_tables bug?

[Andrew Dunstan]

On 12/18/2015 05:18 PM, Gaetano Mendola wrote:
From documentation about "CREATE DATABASE name WITH TABLESAPCE = 
tablespace_name":


tablespace_name
The name of the tablespace that will be associated with the new 
database, or DEFAULT to
use the template database's tablespace. This tablespace will be the 
default tablespace used
for objects created in this database. See CREATE TABLESPACE for more 
information.


I'm sure that my tables are created in the name space but those are 
not reported either in

pg_tables, either in pg_dump or by \d.


1. Please don't top-post on the PostgreSQL lists. See 



2. The system is working as designed and as documented - see the 
comments in the docs on pg_tables. If nothing is shown for the table's 
tablespace then it will be in the default tablespace for the database. 
That's what you're seeing. You appear to be assuming incorrectly that it 
means that the table will be in the system's default tablespace.



cheers

andrew


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

Re: [HACKERS] pg_tables bug?

[Gaetano Mendola]

On Thu, 17 Dec 2015 at 15:36 Tom Lane  wrote:

> Gaetano Mendola  writes:
> > I'm playing around with tablespace (postgresq 9.4) and I found out what I
> > believe is a bug in pg_tables.
> > Basically if you create a database in a table space X and then you
> create a
> > table on the database the table is created correctly on the tablespace X
> (
> > I did a check on the filesystem) however if you do a select on pg_tables
> > the column tablespace for that table is empty and even worst if you dump
> > the DB there is no reporting about the the database or table being on
> that
> > tablespace.
> > Even \d doesn't report that the table is in the tablespace X.
>
> An empty entry in that column means that the table is in the default
> tablespace for the database.  Which it sounds like is what you have
> here.  I think it's operating as designed, though you might quibble
> with the decision that showing default tablespaces explicitly would
> have been clutter.
>

Now it's clear thank you.

Re: [HACKERS] pg_tables bug?

[Gaetano Mendola]

>From documentation about "CREATE DATABASE name WITH TABLESAPCE =
tablespace_name":

tablespace_name
The name of the tablespace that will be associated with the new database,
or DEFAULT to
use the template database's tablespace. This tablespace will be the default
tablespace used
for objects created in this database. See CREATE TABLESPACE for more
information.

I'm sure that my tables are created in the name space but those are not
reported either in
pg_tables, either in pg_dump or by \d.

Look as this:

kalman@kalman-VirtualBox:~$ mkdir tablespace_XXX
kalman@kalman-VirtualBox:~$ sudo chown postgres.postgres tablespace_XXX
kalman@kalman-VirtualBox:~$ psql template1
psql (9.4.5)
Type "help" for help.

template1=# create tablespace XXX LOCATION '/home/kalman/tablespace_XXX';
CREATE TABLESPACE
template1=# create database db_test with tablespace = XXX;
CREATE DATABASE
template1=# \q

kalman@kalman-VirtualBox:~$ psql db_test
psql (9.4.5)
Type "help" for help.

db_test=# create table t_test ( a integer, b numeric);
CREATE TABLE
db_test=# \d+ t_test
Table "public.t_test"
 Column |  Type   | Modifiers | Storage | Stats target | Description
+-+---+-+--+-
 a  | integer |   | plain   |  |
 b  | numeric |   | main|  |

db_test=# select * from pg_tables where tablename = 't_test';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules |
hastriggers
+---++++--+-
 public | t_test| kalman || f  | f|
f
(1 row)

db_test=# select oid from pg_database where datname = 'db_test';
  oid
---
 80335

db_test=# select relfilenode from pg_class where relname = 't_test';
 relfilenode
-
   80336
(1 row)

Unfortunately contrary to what postgres is showing me the table test is in
/home/kalman/tablespace_:

root@kalman-VirtualBox:~# file
/home/kalman/tablespace_XXX/PG_9.4_201409291/80335/80336
/home/kalman/tablespace_XXX/PG_9.4_201409291/80335/80336: empty

as you can see the CREATE DATABASE documentation is honored but the system
is failing to give me the right tablespace location for that table.


Regards





On Thu, 17 Dec 2015 at 15:36 Tom Lane  wrote:

> Gaetano Mendola  writes:
> > I'm playing around with tablespace (postgresq 9.4) and I found out what I
> > believe is a bug in pg_tables.
> > Basically if you create a database in a table space X and then you
> create a
> > table on the database the table is created correctly on the tablespace X
> (
> > I did a check on the filesystem) however if you do a select on pg_tables
> > the column tablespace for that table is empty and even worst if you dump
> > the DB there is no reporting about the the database or table being on
> that
> > tablespace.
> > Even \d doesn't report that the table is in the tablespace X.
>
> An empty entry in that column means that the table is in the default
> tablespace for the database.  Which it sounds like is what you have
> here.  I think it's operating as designed, though you might quibble
> with the decision that showing default tablespaces explicitly would
> have been clutter.
>
> regards, tom lane
>

Re: [HACKERS] pg_tables bug?

[Tom Lane]

Gaetano Mendola  writes:
> I'm playing around with tablespace (postgresq 9.4) and I found out what I
> believe is a bug in pg_tables.
> Basically if you create a database in a table space X and then you create a
> table on the database the table is created correctly on the tablespace X (
> I did a check on the filesystem) however if you do a select on pg_tables
> the column tablespace for that table is empty and even worst if you dump
> the DB there is no reporting about the the database or table being on that
> tablespace.
> Even \d doesn't report that the table is in the tablespace X.

An empty entry in that column means that the table is in the default
tablespace for the database.  Which it sounds like is what you have
here.  I think it's operating as designed, though you might quibble
with the decision that showing default tablespaces explicitly would
have been clutter.

regards, tom lane


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

Re: [HACKERS] pg_tables bug?

[Michael Paquier]

On Thu, Dec 17, 2015 at 4:54 PM, Gaetano Mendola  wrote:
> I'm playing around with tablespace (postgresq 9.4) and I found out what I
> believe is a bug in pg_tables.
> Basically if you create a database in a table space X and then you create a
> table on the database the table is created correctly on the tablespace X ( I
> did a check on the filesystem) however if you do a select on pg_tables the
> column tablespace for that table is empty and even worst if you dump the DB
> there is no reporting about the the database or table being on that
> tablespace.
> Even \d doesn't report that the table is in the tablespace X.

Are you sure you created the table in a tablespace? See for example:
=# create tablespace popo location '/to/tbspace/path';
CREATE TABLESPACE
=# create table aa (a int) tablespace popo;
CREATE TABLE
=# \d aa
  Table "public.aa"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
Tablespace: "popo"
=# select tablename, tablespace from pg_tables where tablename = 'aa';
 tablename | tablespace
---+
 aa| popo
(1 row)

Regards,
-- 
Michael


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

[HACKERS] pg_tables bug?

[Gaetano Mendola]

I'm playing around with tablespace (postgresq 9.4) and I found out what I
believe is a bug in pg_tables.
Basically if you create a database in a table space X and then you create a
table on the database the table is created correctly on the tablespace X (
I did a check on the filesystem) however if you do a select on pg_tables
the column tablespace for that table is empty and even worst if you dump
the DB there is no reporting about the the database or table being on that
tablespace.
Even \d doesn't report that the table is in the tablespace X.

Regards