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.

On 7/10/21 10:43 AM, Phil Endecott wrote:

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;

[snip]

- 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?

I'd shut down postgres, rename those files, and then analyzing the relevant
tables after startup.  Probably also run amcheck against the relevant
tables' indices.

https://access.crunchydata.com/documentation/amcheck-next/1.5/#interface

--
Angular momentum makes the world go 'round.

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.

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.

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.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

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.

"Phil Endecott" <spam_from_pgsql_list@chezphil.org> writes:

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.

No, but PANIC'ing during commit is :-(. Ordinarily the files created by a
failed transaction would be removed during transaction cleanup, but we
did not reach that code. So these were left behind, but the table's
original files in the original tablespace should be undamaged.

regards, tom lane

Tom Lane wrote:

"Phil Endecott" <spam_from_pgsql_list@chezphil.org> writes:

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.

No, but PANIC'ing during commit is :-(. Ordinarily the files created by a
failed transaction would be removed during transaction cleanup, but we
did not reach that code. So these were left behind, but the table's
original files in the original tablespace should be undamaged.

OK, I've removed them - fingers crossed!

Thanks to everyone who replied for your advice.

Regards, Phil.