Обсуждение: BUG #18167: cannot create partitioned tables when default_tablespace is set

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

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)