handle tablespaces for partitioned tables during ALTER DATABASE - Mailing list pgsql-bugs

Summary:

Normally, you are not allowed to `ALTER DATABASE ... SET TABLESPACE` when the
target tablespace is in use by a table in the database.  However, this check
doesn't seem to apply to partitioned tables, and that can lead to unexpected
behavior.

PostgreSQL version:

Compiled by source with git checked out at `refs/tags/REL_12_2`.

```sql
SELECT version();
```

```
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
```

OS: CentOS 7

Repro:

1. Ensure that databases `d` and `e` are not in use.
1. Ensure that tablespaces `fast` and `faster` are not in use.
1. Ensure that directories `/data/fast` and `/data/faster` for tablespaces are
   empty and have correct permissions
1. On one shell, run

   ```sh
   watch psql -c "\
     SELECT relname, reltablespace\
     FROM pg_class\
     WHERE relname LIKE 'scores_%';\
     " -d d
   ```

1. On a second shell in `psql`, run the following commands at your leisure,
   observing the `watch` in the first shell:

   ```sql
   CREATE DATABASE d;
   CREATE DATABASE e;
   \c d
   CREATE TABLE scores
       (rank int, age int, score int)
       PARTITION BY RANGE (rank);
   CREATE TABLE scores_rank_0_to_100
       PARTITION OF scores FOR VALUES FROM (0) TO (100)
       PARTITION BY RANGE (age);
   CREATE TABLE scores_rank_100_to_200
       PARTITION OF scores FOR VALUES FROM (100) TO (200)
       PARTITION BY RANGE (age);
   -- I want rank 0 to 100 lookups to be fast.
   CREATE TABLESPACE fast LOCATION '/data/fast';
   ALTER TABLE scores_rank_0_to_100
       SET TABLESPACE fast;
   -- Actually, I want the fast SSD to be default.
   \c e
   ALTER DATABASE d
       TABLESPACE fast;
   \c d
   -- Let's load in data.
   CREATE TABLE scores_rank_0_to_100_age_20s
       PARTITION OF scores_rank_0_to_100 FOR VALUES FROM (20) TO (30);
   INSERT INTO scores_rank_0_to_100_age_20s
       VALUES (0, 22, 64819);
   -- INSERT ...
   -- I need a new age category now.
   CREATE TABLE scores_rank_0_to_100_age_30s
       PARTITION OF scores_rank_0_to_100 FOR VALUES FROM (30) TO (40);
   INSERT INTO scores_rank_0_to_100_age_30s
       VALUES (51, 30, 9564);
   -- ...
   -- I want to use a faster disk.
   CREATE TABLESPACE faster LOCATION '/data/faster';
   \c e
   ALTER DATABASE d
       TABLESPACE faster;
   \c d
   -- (I verify that data has moved to /data/faster.)
   -- ...
   -- I need a new age category now.
   CREATE TABLE scores_rank_0_to_100_age_40s
       PARTITION OF scores_rank_0_to_100 FOR VALUES FROM (40) TO (50);
   INSERT INTO scores_rank_0_to_100_age_40s
       VALUES (89, 41, 654);
   -- ...
   -- How come my data for the new table is going to the old location
   -- /data/fast?
   ```

The `watch` `SELECT` should end up producing output

```
           relname            | reltablespace 
------------------------------+---------------
 scores_rank_0_to_100         |         16512
 scores_rank_0_to_100_age_20s |             0
 scores_rank_0_to_100_age_30s |             0
 scores_rank_0_to_100_age_40s |         16512
 scores_rank_100_to_200       |             0
(5 rows)
```

after all the commands are run.  I believe that the bug begins on the first
`ALTER DATABASE`.  If `scores_rank_0_to_100` were a physical table (i.e. had a
`relfilenode`), the `ALTER DATABASE` would have caught that and errored out,
and that would have been good.  With my plausible set of steps, I demonstrate
that the `ALTER DATABASE ... SET TABLESPACE` on non-physical tables (e.g.
partitioned tables) should go through the tablespace check as well.

Thanks,
Jason