On Wed, Nov 10, 2010 at 1:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gurjeet Singh <singh.gurjeet@gmail.com> writes: > We are facing a problem in dropping a tablespace after crash recovery. The > recovery starts from the last checkpoint, but the tables that were created > by > a transaction in a tablespace before the checkpoint are still lying around; > the > transaction had not finished by the time of crash.
> After recovery, when the app tries to drop the tablespace, the command fails > because the tablespace directory is not empty.
Hmm. The reason DROP TABLESPACE fails in that case, rather than just arbitrarily rm -rf'ing the files, is fear of deleting valuable data by accident. I suppose we could have a mode that deletes the files without any manual intervention, but personally I'd regard that as a foot-gun.
That'd be a lot of help, especially when the DBA/app knows that there's nothing supposed to be leftover in that tablespace; maybe querying different pg_class from all the databases would act as a cross check.
The problem is that if there are open transactions who just created a relation in that tablespace, that record won't be visible in pg_class. Also, we don't take any kind of lock on a tablespace when opening a relation, so we cannot be sure if there are no running transactions with an open relation from that tablespace (I guess this is moot if DBA/app "knows" there's nothing supposed to be in the tablespace, but this doesn't hold for any new connections trying to create tables there). Join between pg_locks and pg_class would have been helpful, but pg_locks doesn't hold tablespace oid, and pg_class rows may not be visible yet).
> Solving this problem has become quite critical since the the platform where > Postgres is being used is supposed to run unattended.
I'm not entirely clear as to the use-case for unattended DROP TABLESPACE? That doesn't really seem like an operation you should need on a routine basis.
For every new element to be managed, the application creates all the relevant objects in a new schema, and assigns all the objects in that schema to a new tablespace. So when that element supposed to be removed, we need to drop schema and the associated tablespace.
Regardless, having Postgres leave its trash behind is not desirable in any scenario, so a solution that clears such files at the end of recovery would be much more desirable.
