Обсуждение: DROP TABLESPACE needs crash-resistance
<div dir="ltr">We are facing a problem in dropping a tablespace after crash recovery. The<br />recovery starts from the lastcheckpoint, but the tables that were created by<br />a transaction in a tablespace before the checkpoint are still lyingaround; the<br /> transaction had not finished by the time of crash.<br /><br />After recovery, when the app tries todrop the tablespace, the command fails<br />because the tablespace directory is not empty.<br /><br />Solving this problemhas become quite critical since the the platform where<br /> Postgres is being used is supposed to run unattended.The problem is currently<br />being solved by an application specific kluge, which is highly undesirable as<br/>this kluge might not work as the application evolves.<br /><br /> Has this problem been reported/discussed earlier?Any suggestions to avoid this<br />situation?<br /><br />I have a hackish idea of listing files created by yet-to-be-committedtransactions be<br />listed after every checkpoint so that the recovery code can remember to remove<br/> such files if the creating transaction's commit record is not encountered<br />until end of recovery. But thiswould require every smgrcreate() to be communicated<br />to the BGWriter, and somehow make BGWriter forget this listwhen the transaction<br /> commits.<br /><br clear="all" />Regards,<br />-- <br />gurjeet.singh<br />@ EnterpriseDB -The Enterprise Postgres Company<br /><a href="http://www.EnterpriseDB.com">http://www.EnterpriseDB.com</a><br /><br />singh.gurjeet@{gmail | yahoo }.com<br /> Twitter/Skype: singh_gurjeet<br /><br />Mail sent from my BlackLaptop device<br/></div>
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:
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).
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.
Gurjeet Singh <singh.gurjeet@gmail.com> writes:Hmm. The reason DROP TABLESPACE fails in that case, rather than just
> 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.
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).
I'm not entirely clear as to the use-case for unattended DROP TABLESPACE?
> Solving this problem has become quite critical since the the platform where
> Postgres is being used is supposed to run unattended.
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