Обсуждение: BUG #18167: cannot create partitioned tables when default_tablespace is set
BUG #18167: cannot create partitioned tables when default_tablespace is set
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 18167 Logged by: Marius Raicu Email address: mariusraicu@laposte.net PostgreSQL version: 16.0 Operating system: RedHat 8 Description: Hello all, I am encountering some problems when creating partitioned tables when default_tablespace parameter is set. I am not sure if it is a bug or maybe I don't understand the documentation correctly. In the doc, it is stated: https://www.postgresql.org/docs/16/sql-createtable.html TABLESPACE tablespace_name The tablespace_name is the name of the tablespace in which the new table is to be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary. For partitioned tables, since no storage is required for the table itself, the tablespace specified overrides default_tablespace as the default tablespace to use for any newly created partitions when no other tablespace is explicitly specified. USING INDEX TABLESPACE tablespace_name This clause allows selection of the tablespace in which the index associated with a UNIQUE, PRIMARY KEY, or EXCLUDE constraint will be created. If not specified, default_tablespace is consulted, or temp_tablespaces if the table is temporary. https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-DEFAULT-TABLESPACE default_tablespace (string) This variable specifies the default tablespace in which to create objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace. The value is either the name of a tablespace, or an empty string to specify using the default tablespace of the current database. If the value does not match the name of any existing tablespace, PostgreSQL will automatically use the default tablespace of the current database. If a nondefault tablespace is specified, the user must have CREATE privilege for it, or creation attempts will fail. This variable is not used for temporary tables; for them, temp_tablespaces is consulted instead. This variable is also not used when creating databases. By default, a new database inherits its tablespace setting from the template database it is copied from. If this parameter is set to a value other than the empty string when a partitioned table is created, the partitioned table's tablespace will be set to that value, which will be used as the default tablespace for partitions created in the future, even if default_tablespace has changed since then. See the sequence below: [marius@mylaptop ~]$ psql psql (17devel) Type "help" for help. marius@[local]:5434/postgres=# show default_tablespace; default_tablespace -------------------- (1 row) marius@[local]:5434/postgres=# create table toto(id numeric) partition by list(id); CREATE TABLE marius@[local]:5434/postgres=# drop table toto; DROP TABLE marius@[local]:5434/postgres=# \! mkdir /home/marius/pgcode/tblspc1 marius@[local]:5434/postgres=# \! ls /home/marius/pgcode bin pgdata postgresql tblspc1 marius@[local]:5434/postgres=# \q [marius@mylaptop ~]$ vi $PGDATA/postgresql.conf [marius@mylaptop ~]$ [marius@mylaptop ~]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2023-10-24 11:14:21.636 CEST [5800] LOG: redirecting log output to logging collector process 2023-10-24 11:14:21.636 CEST [5800] HINT: Future log output will appear in directory "log". done server started [marius@mylaptop ~]$ psql psql (17devel) Type "help" for help. marius@[local]:5434/postgres=# show default_tablespace; default_tablespace -------------------- tblspc1 (1 row) marius@[local]:5434/postgres=# create tablespace tblspc1 location '/home/marius/pgcode/tblspc1'; CREATE TABLESPACE marius@[local]:5434/postgres=# create database test tablespace tblspc1; CREATE DATABASE marius@[local]:5434/postgres=# \c test You are now connected to database "test" as user "marius". marius@[local]:5434/test=# create table toto(id numeric) partition by list(id); ERROR: cannot specify default tablespace for partitioned relations marius@[local]:5434/test=# create table toto(id numeric, constraint pk_id primary key(id) using index tablespace tblspc1) partition by list(id); ERROR: cannot specify default tablespace for partitioned relations marius@[local]:5434/postgres=# \c test You are now connected to database "test" as user "marius". marius@[local]:5434/test=# create table toto2(id numeric, constraint pk_id primary key(id) using index tablespace tblspc1) partition by list(id); ERROR: cannot specify default tablespace for partitioned relations marius@[local]:5434/test=# create table toto(id numeric) partition by list(id) tablespace tblspc1; ERROR: cannot specify default tablespace for partitioned relations marius@[local]:5434/test=# create table toto(id numeric) partition by list(id); ERROR: cannot specify default tablespace for partitioned relations marius@[local]:5434/test=# create table toto2(id numeric, constraint pk_id primary key(id)) partition by list(id); ERROR: cannot specify default tablespace for partitioned relations However, in another database, 'postgres' by example, which was created in the default tablespace '' (no tablespace at all), it works: marius@[local]:5434/postgres=# create table toto(id numeric) partition by list(id) tablespace tblspc1; CREATE TABLE marius@[local]:5434/postgres=# create table toto2(id numeric, constraint pk_id primary key(id) using index tablespace tblspc1) partition by list(id); CREATE TABLE I was able to reproduce this behavior on all versions starting to PG12. So, when the default _tablespace is set, you have to specify the tablespace clause to CREATE TABLE, despite the fact that the database where you try to put the table is created into a tablespace. Thanks, Marius Raicu
Re: BUG #18167: cannot create partitioned tables when default_tablespace is set
От
Alvaro Herrera
Дата:
On 2023-Oct-24, PG Bug reporting form wrote: > marius@[local]:5434/postgres=# show default_tablespace; > default_tablespace > -------------------- > tblspc1 > (1 row) > > marius@[local]:5434/postgres=# create tablespace tblspc1 location > '/home/marius/pgcode/tblspc1'; > CREATE TABLESPACE > marius@[local]:5434/postgres=# create database test tablespace tblspc1; > CREATE DATABASE > marius@[local]:5434/postgres=# \c test > You are now connected to database "test" as user "marius". > marius@[local]:5434/test=# create table toto(id numeric) partition by > list(id); > ERROR: cannot specify default tablespace for partitioned relations Oh, so the problem here is that *both* default_tablespace and the database's tablespace are set, and then a partitioned table creation fails when it doesn't specify any tablespace? That indeed sounds like a bug. I'll have a look, thanks. I'm surprised it took so long for this to be reported. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Someone said that it is at least an order of magnitude more work to do production software than a prototype. I think he is wrong by at least an order of magnitude." (Brian Kernighan)
Re: BUG #18167: cannot create partitioned tables when default_tablespace is set
От
tender wang
Дата:
Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年10月25日周三 17:41写道:
On 2023-Oct-24, PG Bug reporting form wrote:
> marius@[local]:5434/postgres=# show default_tablespace;
> default_tablespace
> --------------------
> tblspc1
> (1 row)
>
> marius@[local]:5434/postgres=# create tablespace tblspc1 location
> '/home/marius/pgcode/tblspc1';
> CREATE TABLESPACE
> marius@[local]:5434/postgres=# create database test tablespace tblspc1;
> CREATE DATABASE
> marius@[local]:5434/postgres=# \c test
> You are now connected to database "test" as user "marius".
> marius@[local]:5434/test=# create table toto(id numeric) partition by
> list(id);
> ERROR: cannot specify default tablespace for partitioned relations
Oh, so the problem here is that *both* default_tablespace and the
database's tablespace are set, and then a partitioned table creation
fails when it doesn't specify any tablespace? That indeed sounds like a
bug. I'll have a look, thanks. I'm surprised it took so long for this
to be reported.
Oh, interesting issue!
I found another two case:
First: default_tablespace not set and create part rel failed
postgres=# create tablespace tbsp3 location '/tender/pgsql/tbsp3';
CREATE TABLESPACE
postgres=# create database test3 tablespace tbsp3;
CREATE DATABASE
postgres=# \c test3
You are now connected to database "test3" as user "gpadmin".
test3=# show default_tablespace ;
default_tablespace
--------------------
(1 row)
test3=# create table part1(a int) partition by list(a) tablespace tbsp3;
ERROR: cannot specify default tablespace for partitioned relations
CREATE TABLESPACE
postgres=# create database test3 tablespace tbsp3;
CREATE DATABASE
postgres=# \c test3
You are now connected to database "test3" as user "gpadmin".
test3=# show default_tablespace ;
default_tablespace
--------------------
(1 row)
test3=# create table part1(a int) partition by list(a) tablespace tbsp3;
ERROR: cannot specify default tablespace for partitioned relations
Second: default_tablespace and database's tablespace both set, but part rel created
test3=# set default_tablespace = tbsp2;
SET
test3=# create table part1(a int) partition by list(a);
CREATE TABLE
SET
test3=# create table part1(a int) partition by list(a);
CREATE TABLE
I'm not sure if the above two cases are a bug. If the document could provide detailed explanations, that would be great.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude." (Brian Kernighan)