Re: [Treebase-devel] Treebase database has been migrated to new storage space

[William P. <wil...@ya...>]

On Sep 24, 2010, at 10:59 AM, Jon Auman wrote:

> It took about 12 hours.  The migration started out quite fast, then slowed down as it was rebuilding the matrixelement index. Good news is that we also recovered 40 GB of disk space during the restore.  looks like the autovacuum in postgres 8.4 is not working as advertised (I'll look into it). 
> 
> The database now takes up about 140 GB, down from 180. We are on 400 GB volume, which can be expanded in future if needed.
> 
> I've reenabled login.
> 
> -Jon

Sounds great. Performance, so far, feels okay. 



One problem that has cropped up is that someone uploaded two batches of 5,000 trees -- and they can't be deleted except one-by-one, which is a royal pain. When trying to delete the two treeblocks (treeblock_id 12016 and 12113), I get some sort of null pointer exception. So instead, can you delete this manually?  I uploaded two blocks to a test submission in treebaseprod, and the below scripts worked -- so they should work okay for you too. In the "DELETE FROM taxonlabel" you may get zero lines affected, but that doesn't seem to be a problem. 

Can you go ahead and give this a try? In the event that you get some sort of error, you can rollback. 

thanks,

Bill


SELECT phylotree_id FROM phylotree WHERE treeblock_id IN (12016, 12113);
-- this should result in slightly less than 10,000 trees. If not, please abort.


-- Below are two sets of identical update/delete statements, which I suppose could 
-- be fused into one, if "treeblock_id = 12016" is replaced with "treeblock_id IN (12016, 12113)" 
-- but I have not tested that.

begin work;

-- Here we delete the trees in "treeblock_id = 12016"
-- --------------------------------------------------

UPDATE phylotree SET rootnode_id = DEFAULT WHERE treeblock_id = 12016 ;

DELETE FROM phylotreenode WHERE phylotree_id IN (
   SELECT phylotree_id FROM phylotree WHERE treeblock_id = 12016 
) ;

DELETE FROM sub_taxonlabel WHERE taxonlabel_id IN (
   SELECT DISTINCT taxonlabel_id 
   FROM taxonlabelset_taxonlabel JOIN taxonlabelset USING (taxonlabelset_id) 
   JOIN treeblock USING (taxonlabelset_id) 
   WHERE treeblock_id = 12016 
) ;

DELETE FROM taxonlabelset_taxonlabel WHERE taxonlabelset_id IN (
   SELECT taxonlabelset_id FROM treeblock WHERE treeblock_id = 12016 
) ;

-- delete taxonlabel records after having deleted the taxonlabelset_taxonlabel records
DELETE FROM taxonlabel WHERE taxonlabel_id IN (
   SELECT taxonlabel_id 
   FROM taxonlabel LEFT JOIN taxonlabelset_taxonlabel USING (taxonlabel_id) 
   WHERE study_id = (SELECT DISTINCT study_id FROM phylotree  WHERE treeblock_id = 12016) 
   AND taxonlabelset_id IS NULL
) ;

UPDATE treeblock SET taxonlabelset_id = DEFAULT WHERE treeblock_id = 12016 ;

-- Allows us to delete from taxonlabelset even after fk in treeblock 
-- has been reset to NULL 
DELETE FROM taxonlabelset WHERE taxonlabelset_id IN (
   SELECT DISTINCT taxonlabelset_id 
   FROM taxonlabelset LEFT JOIN taxonlabelset_taxonlabel tltl USING (taxonlabelset_id) 
   WHERE study_id = (
      SELECT DISTINCT study_id FROM phylotree  
      WHERE treeblock_id = 12016
      ) 
   AND tltl.taxonlabel_id IS NULL
) ;

DELETE FROM phylotree WHERE treeblock_id = 12016 ;

DELETE FROM sub_treeblock WHERE treeblock_id = 12016 ;

DELETE FROM treeblock WHERE treeblock_id = 12016 ;


-- Here we delete the trees in "treeblock_id = 12113"
-- --------------------------------------------------

UPDATE phylotree SET rootnode_id = DEFAULT WHERE treeblock_id = 12113 ;

DELETE FROM phylotreenode WHERE phylotree_id IN (
   SELECT phylotree_id FROM phylotree WHERE treeblock_id = 12113 
) ;

DELETE FROM sub_taxonlabel WHERE taxonlabel_id IN (
   SELECT DISTINCT taxonlabel_id 
   FROM taxonlabelset_taxonlabel JOIN taxonlabelset USING (taxonlabelset_id) 
   JOIN treeblock USING (taxonlabelset_id) 
   WHERE treeblock_id = 12113 
) ;

DELETE FROM taxonlabelset_taxonlabel WHERE taxonlabelset_id IN (
   SELECT taxonlabelset_id FROM treeblock WHERE treeblock_id = 12113 
) ;

-- delete taxonlabel records after having deleted the taxonlabelset_taxonlabel records
DELETE FROM taxonlabel WHERE taxonlabel_id IN (
   SELECT taxonlabel_id 
   FROM taxonlabel LEFT JOIN taxonlabelset_taxonlabel USING (taxonlabel_id) 
   WHERE study_id = (SELECT DISTINCT study_id FROM phylotree  WHERE treeblock_id = 12113) 
   AND taxonlabelset_id IS NULL
) ;

UPDATE treeblock SET taxonlabelset_id = DEFAULT WHERE treeblock_id = 12113 ;

-- Allows us to delete from taxonlabelset even after fk in treeblock 
-- has been reset to NULL 
DELETE FROM taxonlabelset WHERE taxonlabelset_id IN (
   SELECT DISTINCT taxonlabelset_id 
   FROM taxonlabelset LEFT JOIN taxonlabelset_taxonlabel tltl USING (taxonlabelset_id) 
   WHERE study_id = (
      SELECT DISTINCT study_id FROM phylotree  
      WHERE treeblock_id = 12113
      ) 
   AND tltl.taxonlabel_id IS NULL
) ;

DELETE FROM phylotree WHERE treeblock_id = 12113 ;

DELETE FROM sub_treeblock WHERE treeblock_id = 12113 ;

DELETE FROM treeblock WHERE treeblock_id = 12113 ;

commit;