Message-ID: <4c4246e7824aeec46e76afc95745fa1e28ac11b4.camel@cybertec.at>
Subject: Re: Error in DROP TABLESPACE
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Fagnani Gabriele G (GDS I&TS)" <gabriele.fagnani@enel.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Wed, 07 May 2025 10:27:47 +0200
In-Reply-To: <PAXP191MB188589F40FA0F86D0DB9281DE288A@PAXP191MB1885.EURP191.PROD.OUTLOOK.COM>
References: 
	<PAXP191MB188589F40FA0F86D0DB9281DE288A@PAXP191MB1885.EURP191.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/4c4246e7824aeec46e76afc95745fa1e28ac11b4.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-05-07 at 08:16 +0000, Fagnani Gabriele G (GDS I&TS) wrote:
> INTERNAL

?

> I've run into an odd problem - I have what seems to be a "zombie" tablesp=
ace
> that PostgreSQL won't let me drop, but nothing inside it is active.
> =C2=A0
> drop tablespace ts_idx_wb2;
> ERROR:=C2=A0 tablespace "ts_idx_wb2" is not empty
> =C2=A0
> postgres=3D# \db+ ts_idx_wb2
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 List of tablespaces
> =C2=A0=C2=A0=C2=A0 Name=C2=A0=C2=A0=C2=A0 |=C2=A0 Owner=C2=A0=C2=A0 |=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Locat=
ion=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 |=C2=A0 Access privileges=C2=A0 | Options |=C2=A0 Size=C2=A0 | Description
> ------------+----------+----------------------------------+--------------=
-------+---------+--------+-------------
> ts_idx_wb2 | postgres | /pgsql/postgres/tsdata1/ts_idx_wb2 | postgres=3DC=
/postgres+|=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | 250 GB |
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 | mydatabase=3DC/postgres |=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =
|
> (1 row)
> =C2=A0
> Check on filesystem location.
> On the filesystem there are files related only to fileref 515555188:
> =C2=A0
> postgres@myserver:/pgsql/postgres/tsdata1/ts_idx_wb2/PG_12_201909212/1641=
9> ls -la
> total 261854080
> drwx------ 2 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 69632 Apr 22=
 17:45 .
> drwx------ 3 postgres postgres=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 4096 O=
ct 31=C2=A0 2024 ..
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.1
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.10
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.100
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.101
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.102
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.103
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.104
> ...
> ...
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.97
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.98
> -rw------- 1 postgres postgres 1073741824 Oct 31=C2=A0 2024 515555188.99
> =C2=A0
> SELECT pg_tablespace_databases((SELECT oid FROM pg_tablespace WHERE spcna=
me =3D 'ts_idx_wb2'));
> pg_tablespace_databases
> -------------------------
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 16419
> =C2=A0
> =C2=A0
> SELECT oid, datname FROM pg_database WHERE oid =3D 16419;
> oid=C2=A0 |=C2=A0 datname
> -------+-----------
> 16419 | mydatabase=C2=A0=C2=A0=C2=A0 <----------------
> =C2=A0
> =C2=A0
> Connected to database mydatabase:
> =C2=A0
> select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind
> from pg_class cl
> =C2=A0 join pg_namespace nsp on cl.relnamespace =3D nsp.oid and cl.relfil=
enode=3D515555188 ;
> relfilenode | schema_name | relname | relkind
> -------------+-------------+---------+---------
> (0 rows)
> =C2=A0
> To be sure I run the same query on the other databases with the same resu=
lt (0 rows)
> =C2=A0
> So there are no actual references to these files, yet the relationship
> persists sufficiently for the DROP TABLESPACE to error out.
> My guess is that the files belong to an index being moved on Oct 31 to ts=
_idx_wb2 tablespace, and during the move the
> postgres instance crashed due space shortage on wal filesystem, and this =
crash left this "zombie" files on the filesystem.
> =C2=A0
> In order to drop the tablespace, is it safe to shut =C2=A0that postmaster=
 down and manually remove the contents of this
> directory?

Your diagnosis seems to be right, and I agree with your proposed remedy.

Yours,
Laurenz Albe