Re: Why can't I drop a tablespace?

Phil Endecott wrote:
> Yesterday I had a disk-nearly-full problem, and decided to try to resolve
> it by moving one large table to a spare disk in a new tablespace:
>
> =# create tablespace tempspace location "/db_temp";
> =# alter table requests set tablespace tempspace;
>
> That didn't work; I think disk space had actually reached zero:
>
> PANIC: could not write to file "pg_wal/xlogtemp.19369": No space left on device
> STATEMENT: alter table requests set tablespace tempspace;
>
> So I shut down the database and resolved the problem in a more conventional
> way by resizing the filesystem. It is now (apparently) functioning normally.
>
> BUT: I am unable to drop the tablespace that I created:
>
> =# drop tablespace tempspace;
> ERROR: tablespace "tempspace" is not empty
>
> On inspection /db_temp does contain a few GB of data that looks
> consistent with my table "requests" that I had tried to move.
>
> postgres:/db_temp/PG_11_201809051$ ls -l 17829/
> total 2894972
> -rw------- 1 postgres postgres 32137216 Jul 8 18:35 486095
> -rw------- 1 postgres postgres 37240832 Jul 8 18:57 494286
> -rw------- 1 postgres postgres 1073741824 Jul 8 19:02 502478
> -rw------- 1 postgres postgres 1073741824 Jul 8 19:03 502478.1
> -rw------- 1 postgres postgres 747577344 Jul 8 19:03 502478.2
>
> I can't find what is using it:
>
> =# select * from pg_tables where tablespace = 'tempspace';
> (0 rows)

I've now had a look at the source for "DROP TABLESPACE" and also for
the pg_tablespace_databases function, and it seems that they both
inspect the filesystem, rather than looking in any catalog tables,
to determine whether a database has any tables in the tablespace.

Since the tablespace doesn't seem to be mentioned in pg_tables or
pg_indexes, or anywhere else that I've looked, my guess is that these
files are orphans that shouldn't be there - perhaps they should have
been deleted when the "alter table set tablespace" or the "vacuum full"
failed, but weren't. So I am tempted to delete them (or at least move
them away, initially).

Questions:

- Any advice about identifying what the tables are? I note that the
first one, 486095, is the OID of the tablespace itself plus one; does
that give a clue?

- Is my idea that these files should have been deleted but weren't
during the disk-full panic plausible?

- If I do move them away, what will Postgres do if it tries to access
them and finds they are missing? Is it well-behaved in this case?

- If I move them away, I would try to exercise the database (e.g.
do variants of "select sum(column1) from table") in some way to make
sure that it is all functioning. Any suggestions about how best to do
this?

Regards, Phil.