Re: Why can't I drop a tablespace?

Laurenz Albe wrote:
> On Fri, 2021-07-09 at 20:04 +0100, Phil Endecott wrote:
>> =# 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:
>
> These files don't get cleaned up after a crash, so they may well be
> leftovers you can remove.

Thanks Laurenz. I was looking at the source for "alter table set
tablespace" yesterday trying to work out what is supposed to happen.
There is a comment at tablecmds.c line 3989: "Thanks to the magic of
MVCC, an error anywhere along the way rolls back the whole operation;
we don't have to do anything special to clean up." But I guess that
creating an entirely new file on a different filesystem is an
exception to that.

> Use \dt+ and \di+ to determine if the table or any of its indexes
> actually resides in the new tablespace. Don't forget the TOAST table.

"select distinct(reltablespace) from pg_class" gives only pg_global and
null. Is that including TOAST tables etc.?

> If they are all still in the original tablespace as they should be
> on account of the transactional guarantees, go ahead and manually
> remove the files.

My plan is to wait for a couple of days to see if there are any other
opinions here, and then do that.

Thanks again,

Phil.