Hello Alvaro, all,
I have done some research regarding this bug.
Basically, we forbid the creation of partitioned tables and indexes if a default_tablespace is specified in postgresql.conf.
In tablespace.c, the comment says:
"Don't allow specifying that when creating a partitioned table, however, since the result is confusing."
I did not see why the result is confusing.
I just disabled the checks in tablespace.c, tablecmds.c and indexcmds.c and now it works.
I modified the expected result in tests, and the tests are passing too.
See the attached patch.
Regards,
Marius Raicu
On 10/25/23 11:58, tender wang wrote:
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
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
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)
