DROP TABLESPACE needs crash-resistance

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.

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.

regards, tom lane

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.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device