Thread: Unable To Drop Tablespace

Unable To Drop Tablespace

From
Pavan Pusuluri
Date:
Hi there

We are trying to drop a table space on RDS Postgres . We have removed the objects etc, but it still won't drop.

I have checked and there's no reference anywhere to this tablespace but it complains it's not empty.

I checked if it is a default for a database, revoked all privileges on the tablespace.

We dropped the database but underpinning tablespace remained but when I query to see if any reference i get no hits.

"Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid where t.spcname='mytablespace'

I dont find any objects referencing. Kindly let me know if anything else needs to be checked?


Regards
Pavan

Re: Unable To Drop Tablespace

From
Ian Lawrence Barwick
Date:
2021年2月5日(金) 3:52 Pavan Pusuluri <pavan.pusuluri@gmail.com>:
Hi there

We are trying to drop a table space on RDS Postgres . We have removed the objects etc, but it still won't drop.

I have checked and there's no reference anywhere to this tablespace but it complains it's not empty.

I checked if it is a default for a database, revoked all privileges on the tablespace.

We dropped the database but underpinning tablespace remained but when I query to see if any reference i get no hits.

"Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid where t.spcname='mytablespace'

I dont find any objects referencing. Kindly let me know if anything else needs to be checked?

There's a handy function "pg_tablespace_databases()" to check which databases
might still have objects in a database. There are a couple of useful queries demonstrating
usage here:


Regards

Ian Barwick


--

Re: Unable To Drop Tablespace

From
Pavan Pusuluri
Date:
Thank you very much Ian. Will check it out.

Regards


On Thu, Feb 4, 2021, 5:43 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
2021年2月5日(金) 3:52 Pavan Pusuluri <pavan.pusuluri@gmail.com>:
Hi there

We are trying to drop a table space on RDS Postgres . We have removed the objects etc, but it still won't drop.

I have checked and there's no reference anywhere to this tablespace but it complains it's not empty.

I checked if it is a default for a database, revoked all privileges on the tablespace.

We dropped the database but underpinning tablespace remained but when I query to see if any reference i get no hits.

"Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid where t.spcname='mytablespace'

I dont find any objects referencing. Kindly let me know if anything else needs to be checked?

There's a handy function "pg_tablespace_databases()" to check which databases
might still have objects in a database. There are a couple of useful queries demonstrating
usage here:


Regards

Ian Barwick


--

Re: Unable To Drop Tablespace

From
Thomas Munro
Date:
On Fri, Feb 5, 2021 at 12:43 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
> 2021年2月5日(金) 3:52 Pavan Pusuluri <pavan.pusuluri@gmail.com>:
>> We are trying to drop a table space on RDS Postgres . We have removed the objects etc, but it still won't drop.
>>
>> I have checked and there's no reference anywhere to this tablespace but it complains it's not empty.
>>
>> I checked if it is a default for a database, revoked all privileges on the tablespace.
>>
>> We dropped the database but underpinning tablespace remained but when I query to see if any reference i get no hits.
>>
>> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid where
t.spcname='mytablespace'
>>
>> I dont find any objects referencing. Kindly let me know if anything else needs to be checked?
>
>
> There's a handy function "pg_tablespace_databases()" to check which databases
> might still have objects in a database. There are a couple of useful queries demonstrating
> usage here:
>
>   https://pgpedia.info/p/pg_tablespace_databases.html

It's also possible for there to be stray files in there, in some crash
scenarios where PostgreSQL doesn't currently clean up relation files
that it ideally should.  The one with the widest window AFAIK is where
you crash after creating a table but before committing[1].  You'd need
a directory listing to investigate that.

[1] https://www.postgresql.org/message-id/flat/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com