令人费解的分区表与表空间

今天一位朋友找到我，说无法在某个表空间下面创建分区表，自己在复现的过程中也被折磨的晕头转向，直接进入主题，一起看看这个令人费解的现象。

现象

看一下报错现象，先创建一个表空间和用户

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# create tablespace myspace location '/home/postgres/14_tablespace';
CREATE TABLESPACE
postgres=# create database mydb tablespace myspace ;
CREATE DATABASE
postgres=# create user u1;
CREATE ROLE
postgres=# alter user u1 set default_tablespace to myspace;
ALTER ROLE
postgres=# select usename,useconfig from pg_user where usename = 'u1';
 usename |          useconfig           
---------+------------------------------
 u1      | {default_tablespace=myspace}
(1 row)

mydb=> \l+ mydb
                                                List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description 
------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
 mydb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 8537 kB | myspace    | 
(1 row)

然后使用u1用户创建一个分区表，可以看到直接就报错了

postgres=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> CREATE TABLE ptab01 (
mydb(>     id         int not null,
mydb(>     tm         timestamptz not null
mydb(> ) PARTITION BY RANGE (tm);
ERROR:  cannot specify default tablespace for partitioned relations
mydb=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
ERROR:  relation "ptab01" does not exist

提示 cannot specify default tablespace for partitioned relations，可以看到，普通用户无法在该表空间下创建分区表。当然这里通过授权语句grant ALL on TABLESPACE myspace to u1也不行，因为报错本身就没有涉及到权限这一层。

那么创建一个普通的表试试

mydb=> CREATE TABLE ptab01 (
    id         int not null,
    tm         timestamptz not null
);
CREATE TABLE

mydb=> \d+ ptab01 
                                                  Table "public.ptab01"
 Column |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer                  |           | not null |         | plain   |             |              | 
 tm     | timestamp with time zone |           | not null |         | plain   |             |              | 
Access method: heap

mydb=> select pg_relation_filepath('ptab01');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/52139/PG_14_202107181/52140/52144
(1 row)

[postgres@xiongcc 52140]$ ls -lrth | grep 52144
-rw------- 1 postgres postgres  32K Feb 23 16:58 52144_fsm
-rw------- 1 postgres postgres 8.0K Feb 23 16:58 52144_vm
-rw------- 1 postgres postgres  56M Feb 23 17:03 52144

可以看到普通的表是可以正常创建的。那么难道是普通用户不能在这个表空间上面创建分区表吗？换个姿势看看。

mydb=# alter user u1 reset all;
ALTER ROLE
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> CREATE TABLE ptab01 (
mydb(>     id         int not null,
mydb(>     tm         timestamptz not null
mydb(> ) PARTITION BY RANGE (tm);
CREATE TABLE
mydb=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
mydb=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
mydb=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
mydb=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
mydb=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
mydb=> select pg_relation_filepath('ptab01_202001');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/52139/PG_14_202107181/52140/52171
(1 row)
     
mydb=> \dt+ ptab01
                                         List of relations
 Schema |  Name  |       Type        | Owner | Persistence | Access method |  Size   | Description 
--------+--------+-------------------+-------+-------------+---------------+---------+-------------
 public | ptab01 | partitioned table | u1    | permanent   |               | 0 bytes | 
(1 row)

DBA惊呆了，这种方式居然又可以了...是可以创建分区表的。当然还有其他另外的迂回实现方式，比如不指定默认表空间，创建之后（默认创建在了pg_default下），再通过alter table的方式修改分区表到指定的表空间，但是这样就涉及到了数据的拷贝，对于大表是很繁重的一件事。

不难发现，关键差异在于是否针对用户设置了default_tablespace。

类似的，假如会话级设置了default_tablespace也会报错

mydb=> \c mydb postgres 
You are now connected to database "mydb" as user "postgres".
mydb=# alter user u1 reset all;
ALTER ROLE
mydb=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> CREATE TABLE ptab01 (
mydb(>     id         int not null,
mydb(>     tm         timestamptz not null
mydb(> ) PARTITION BY RANGE (tm);
CREATE TABLE
mydb=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
mydb=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
mydb=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
mydb=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
mydb=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
mydb=> drop table ptab01;
DROP TABLE
mydb=> 
mydb=> set default_tablespace to myspace;
SET
mydb=> CREATE TABLE ptab01 (
mydb(>     id         int not null,
mydb(>     tm         timestamptz not null
mydb(> ) PARTITION BY RANGE (tm);
ERROR:  cannot specify default tablespace for partitioned relations
mydb=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
ERROR:  relation "ptab01" does not exist
mydb=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
ERROR:  relation "ptab01" does not exist

但是令人难以理解的是，假如是默认库又可以。

postgres=# grant all on schema public to u1;
GRANT
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> set default_tablespace to myspace;
SET
postgres=> CREATE TABLE ptab01 (
postgres(>     id         int not null,
postgres(>     tm         timestamptz not null
postgres(> ) PARTITION BY RANGE (tm);
CREATE TABLE
postgres=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
postgres=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
postgres=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
postgres=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
postgres=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
postgres=> select pg_relation_filepath('ptab01_202001');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/52280/PG_14_202107181/13890/52565
(1 row)

另外值得注意的是，超级用户类似操作也一样会报错。

那么问题就回到了default_tablespace和mydb所在的表空间上面了。

分析

那么为什么会有这样的"怪异"的现象呢？根据错误搜索了一下，可惜的是代码里没有过多的注解

    /*
     * Select tablespace to use.  If not specified, use default tablespace
     * (which may in turn default to database's default).
     */
    if (stmt->tableSpace)
    {
        tablespaceId = get_tablespace_oid(stmt->tableSpace, false);
        if (partitioned && tablespaceId == MyDatabaseTableSpace)
            ereport(ERROR,
                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                     errmsg("cannot specify default tablespace for partitioned relations")));
    }

    /*
     * 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")));
    }

这个 MyDatabaseTableSpace 是 InvalidOid。

/* note: currently this is not valid in backend processes */
#endif

BackendId    MyBackendId = InvalidBackendId;

BackendId    ParallelLeaderBackendId = InvalidBackendId;

Oid            MyDatabaseId = InvalidOid;

Oid            MyDatabaseTableSpace = InvalidOid;

    /*
     * 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;
    }

可以看到，代码里直接写死了。并且做了说明，结果有点让人费解 since the result is confusing，代码短时间分析不出来啥，先看下官网对default_tablespace的说明

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.

如果在创建分区表时，这个参数被设置为一个非空字符串的值，那么分区表的表空间将被设置为这个值，它将被用作未来创建的分区的默认表空间，即使default_tablespace在那之后发生了变化。

通过一阵搜索，找到一封类似的邮件，https://www.postgresql.org/message-id/flat/BY5PR18MB3170E372542F34694E630B12F10C0@BY5PR18MB3170.namprd18.prod.outlook.com，这里面提及到了这么一段话

ll my application partitioned table DDL works OK on PG 11.1 on CentOS Linux release 7.3.1611 (Core) but none on 12.1...

I'd be surprised if something so fundamental is a bug but just in case, I thought I'd submit it....

在PG 11.1里正常，但是在12.1里面就会报错，并且可能是12版本的 "退步" So this seems like a regression in v12.

Ignoring the setting seems to be what the documentation says we do: https://www.postgresql.org/docs/12/sql-createtable.html " 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." So this seems like a regression in v12.

You're right that this is new behavior in v12; it seems to have been introduced by commit 87259588d, which quoth

大意是在12的版本里引入了这个问题，那么原模原样操作试一下11的版本

mydb=> select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# create tablespace myspace location '/home/postgres/11_tablespace';
CREATE TABLESPACE
postgres=# create database mydb tablespace myspace ;
CREATE DATABASE
postgres=# create user u1;
CREATE ROLE
postgres=# alter user u1 set default_tablespace to myspace;
ALTER ROLE
postgres=# select usename,useconfig from pg_user where usename = 'u1';
 usename |          useconfig           
---------+------------------------------
 u1      | {default_tablespace=myspace}
(1 row)

postgres=# \c mydb u1
You are now connected to database "mydb" as user "u1".
mydb=> CREATE TABLE ptab01 (
mydb(>     id         int not null,
mydb(>     tm         timestamptz not null
mydb(> ) PARTITION BY RANGE (tm);
CREATE TABLE
mydb=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
mydb=> create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
mydb=> create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
mydb=> create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
mydb=> create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
mydb=> select pg_relation_filepath('ptab01_202001');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16532/PG_11_201809051/16533/16538
(1 row)

果然11的版本是正常的。

另外在分析的过程中，create table语法的一段话引起了我的兴趣

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.

tablespace_name是创建新表的表空间的名称。如果没有指定，将查询default_tablespace，如果表是临时的，则查询temp_tablespaces。对于分区表，由于表本身不需要存储，当没有明确指定其他表空间时，指定的表空间将取代default_tablespace，成为任何新创建分区的默认表空间。

让我们试验一下，先试试11的版本

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# CREATE TABLE ptab01 (
postgres(#     id         int not null,
postgres(#     tm         timestamptz not null
postgres(# ) PARTITION BY RANGE (tm) tablespace myspace;
CREATE TABLE
postgres=# create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
postgres=# create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
postgres=# create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
postgres=# create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
postgres=# create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
postgres=# select pg_relation_filepath('ptab01_202001');
 pg_relation_filepath 
----------------------
 base/13285/16556
(1 row)

postgres=# create table t1 (id int) tablespace myspace;
CREATE TABLE
postgres=# select pg_relation_filepath('t1');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16532/PG_11_201809051/13285/16571
(1 row)

postgres=> drop table ptab01;
DROP TABLE
postgres=> CREATE TABLE ptab01 (                                                                           
    id         int not null,
    tm         timestamptz not null
) PARTITION BY RANGE (tm) tablespace myspace;
CREATE TABLE
postgres=> create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01') tablespace myspace;
CREATE TABLE
postgres=> select pg_relation_filepath('ptab01_202001');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/16532/PG_11_201809051/13285/16669
(1 row)

纳尼，分区表怎么在base下面？？还需要自己指定才行。

再看看14的版本

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# CREATE TABLE ptab01 (
    id         int not null,
    tm         timestamptz not null
) PARTITION BY RANGE (tm) tablespace myspace;
CREATE TABLE
postgres=# create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
CREATE TABLE
postgres=# create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
CREATE TABLE
postgres=# create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
CREATE TABLE
postgres=# create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
CREATE TABLE
postgres=# create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
CREATE TABLE
postgres=# select pg_relation_filepath('ptab01_202001');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/52280/PG_14_202107181/13890/52365
(1 row)

DBA表示他喵的惊呆了！两个版本的表现完全不一致。

在11的版本里面分区表父表指定的表空间完全没用，需要自己指定子表，普通表生效。而14的版本里面，分区表又生效了。

小结

在12的版本里面，对分区表主要是对性能进行了极大的优化，但是参照今天这个例子，可以看到在提升的过程中也引入了一些其他的坑，就好比vacuum_cleanup_index_scale_factor参数一样，在14及之后的版本就去掉了。在v13的版本中，引入了autovacuum_vacuum_insert_threshold这个参数，为了防止大量insert操作后，导致的冻结炸弹，因为以前纯insert操作是不会触发vacuum的，只会触发analyze，但是vacuum_cleanup_index_scale_factor和autovacuum_vacuum_insert_threshold又水火不容，所以就去掉了。

可以看到，官方在引入新特性的过程中，可能会带来一些额外的坑，就跟我们自己写代码一样，修复了一个bug，可能又引入一个其他的bug，导致最后越修越多....