Re: BUG #18167: cannot create partitioned tables when default_tablespace is set

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:
>
>
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2023年10月25日周三 17:41写道：
>
> On 2023-Oct-24, PG Bug reporting form wrote:
>
> > marius(at)[local]:5434/postgres=# show default_tablespace;
> >  default_tablespace
> > --------------------
> >  tblspc1
> > (1 row)
> >
> > marius(at)[local]:5434/postgres=# create tablespace tblspc1 location
> > '/home/marius/pgcode/tblspc1';
> > CREATE TABLESPACE
> > marius(at)[local]:5434/postgres=# create database test tablespace
> tblspc1;
> > CREATE DATABASE
> > marius(at)[local]:5434/postgres=# \c test
> > You are now connected to database "test" as user "marius".
> > marius(at)[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/ <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)
>
>
>
>