PostgreSQL分区表创建失败问题分析

问题概述

创建分区表时，当前会话的default_tablespace或建表语句里指定的tablespace，和当前数据库的tablespace相同时，分区表创建报错

“cannot specify default tablespace for partitioned relations”。

问题测试

1、不设置默认表空间，创建对象时不显式指定表空间，对象创建在数据库所在表空间中，且分区表可以正常创建

postgres=# create database test_tbs tablespace tbs1;
CREATE DATABASE
postgres=# \c test_tbs
You are now connected to database "test_tbs" as user "postgres".
test_tbs=# show default_tablespace;
 default_tablespace
--------------------

(1 row)
test_tbs=# create table test(id int,name varchar) partition by list (id);
CREATE TABLE
test_tbs=# create table test1(id int,name varchar);
CREATE TABLE
test_tbs=# select pg_relation_filepath('test'::regclass);
 pg_relation_filepath
----------------------

(1 row)
test_tbs=# select pg_relation_filepath('test1'::regclass);
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/32953/PG_15_202209061/32978/32982
(1 row)
test_tbs=# \l+ test_tbs
                                                                 List of databases
   Name   |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges |  Size   | Tablespace | Description
----------+----------+----------+-------------+-------------+------------+-----------------+-------------------+---------+------------+-------------
 test_tbs | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |            | libc            |                   | 7573 kB | tbs1       |
(1 row)

2、设置database默认表空间，创建对象时不显式指定表空间，对象创建在数据库所在表空间中，但分区表创建失败

postgres=# create database test_tbs tablespace tbs1;
CREATE DATABASE
postgres=# alter database test_tbs set default_tablespace to 'tbs1';
ALTER DATABASE
postgres=# \c test_tbs
You are now connected to database "test_tbs" as user "postgres".
test_tbs=# show default_tablespace;
 default_tablespace
--------------------
 tbs1
(1 row)
test_tbs=# create table test(id int,name varchar) partition by list (id);
ERROR:  cannot specify default tablespace for partitioned relations
test_tbs=# create table test1(id int,name varchar);
CREATE TABLE
test_tbs=# select pg_relation_filepath('test'::regclass);
ERROR:  relation "test" does not exist
LINE 1: select pg_relation_filepath('test'::regclass);
                                    ^
test_tbs=# select pg_relation_filepath('test1'::regclass);
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/32953/PG_15_202209061/32987/32988
(1 row)

3、设置当前会话默认表空间，创建对象时不显式指定表空间，对象创建在数据库所在表空间中，但分区表创建失败

postgres=# create database test_tbs tablespace tbs1;
CREATE DATABASE
postgres=# \c test_tbs
You are now connected to database "test_tbs" as user "postgres".
test_tbs=# show default_tablespace;
 default_tablespace
--------------------

(1 row)
test_tbs=# set default_tablespace to 'tbs1';
SET
test_tbs=# show default_tablespace;
 default_tablespace
--------------------
 tbs1
(1 row)
test_tbs=# create table test(id int,name varchar) partition by list (id);
ERROR:  cannot specify default tablespace for partitioned relations
test_tbs=# create table test1(id int,name varchar);
CREATE TABLE
test_tbs=# select pg_relation_filepath('test'::regclass);
ERROR:  relation "test" does not exist
LINE 1: select pg_relation_filepath('test'::regclass);
                                    ^
test_tbs=# select pg_relation_filepath('test1'::regclass);
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/32953/PG_15_202209061/32993/32994
(1 row)

4、创建对象时显式指定表空间，分区表创建失败

[postgres@mydb1a pg16_2]$ psql -p 5430
psql (16.2)
Type "help" for help.

postgres=# create table test_tbs(id int,name varchar) partition by list (id) tablespace pg_default;
ERROR:  cannot specify default tablespace for partitioned relations
postgres=# select version();
                                   version
-----------------------------------------------------------------------------
 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)

原理分析

根据报错搜索代码

首先看backend/commands/tablecmds.c

/*
 * Select tablespace to use: an explicitly indicated one, or (in the case
 * of a partitioned table) the parent's, if it has one.
 */
if (stmt->tablespacename)//显式指定了表空间，是分区表，且指定的表空间和数据库表空间相同时，报错
{
        tablespaceId = get_tablespace_oid(stmt->tablespacename, false);

        if (partitioned && tablespaceId == MyDatabaseTableSpace)
                ereport(ERROR,
                                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                 errmsg("cannot specify default tablespace for partitioned relations")));
}
else if (stmt->partbound)
{
        /*
         * For partitions, when no other tablespace is specified, we default
         * the tablespace to the parent partitioned table's.
         */
        Assert(list_length(inheritOids) == 1);
        tablespaceId = get_rel_tablespace(linitial_oid(inheritOids));
}
else
        tablespaceId = InvalidOid;

/* still nothing? use the default */
if (!OidIsValid(tablespaceId))//未显式指定表空间，获取默认表空间
        tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence,
                                                                                partitioned);

再看backend/commands/tablespace.c

GetDefaultTablespace(char relpersistence, bool partitioned)
{
        Oid                     result;

        /* The temp-table case is handled elsewhere */
        if (relpersistence == RELPERSISTENCE_TEMP)
        {
                PrepareTempTablespaces();
                return GetNextTempTableSpace();
        }

        /* Fast path for default_tablespace == "" */
        if (default_tablespace == NULL || default_tablespace[0] == '\0')//未设置默认表空间，返回无效OID
                return InvalidOid;

        /*
         * It is tempting to cache this lookup for more speed, but then we would
         * fail to detect the case where the tablespace was dropped since the GUC
         * variable was set.  Note also that we don't complain if the value fails
         * to refer to an existing tablespace; we just silently return InvalidOid,
         * causing the new object to be created in the database's tablespace.
         */
        result = get_tablespace_oid(default_tablespace, true);

        /*
         * Allow explicit specification of database's default tablespace in
         * default_tablespace without triggering permissions checks.  Don't allow
         * specifying that when creating a partitioned table, however, since the
         * result is confusing.
         */
        if (result == MyDatabaseTableSpace)//默认表空间和数据库表空间相同时，是分区表，报错
        {
                if (partitioned)
                        ereport(ERROR,
                                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                         errmsg("cannot specify default tablespace for partitioned relations")));
                result = InvalidOid;
        }
        return result;

问题总结及解决办法

• default_tablespace和database的表空间相同时，创建分区表报错

• 创建分区表的SQL中显式指定的表空间和database的表空间相同时，创建分区表报错

• 分区表在父表上创建索引同样存在该现象

• 测试最新版本，pg16.2版本中存在该现象

• 解决办法
  1）创建分区表时不设置会话的默认表空间或设置默认表空间为空
  2）且建表时不显式指定表空间

  例如：创建database时指定了表空间tbs1，在该database中创建对象时无需显式指定表空间，对象会创建在表空间tbs1下

  create database test_tbs tablespace tbs1;
  create table test(id int,name varchar) partition by list (id);
  

参考

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

令人费解的分区表与表空间 - 墨天轮 (modb.pro)