trunk/sql/zz_dst_files_fix.sql

drop index dst_i1 ;
drop index dst_i2 ;
drop index dst_i3 ;
drop table dst_files_ids ;
drop table dst_files ;


select hostid,shareid,num,abstime(date),size,inc_size into dst_files from backups where date > abstime('2005-10-24')::int order by abstime,hostid,num;

delete from archive_backup where backup_id in (select id from backups where date > abstime('2005-10-24')::int) ;
delete from backups where date > abstime('2005-10-24')::int;
vacuum analyze backups;

create index dst_i1 on dst_files(shareid) ;
create index dst_i2 on dst_files(num) ;
vacuum analyze dst_files ;
create index dst_i3 on files(backupnum) ;
vacuum analyze files ;

select id into dst_files_ids from files inner join dst_files on dst_files.shareid = files.shareid and dst_files.num = files.backupnum ;
create unique index dst_i4 on dst_files_ids(id) ;
vacuum analyze dst_files_ids ;

delete from files where id in (select id from dst_files_ids) ;

vacuum full analyze verbose files ;

drop table dst_files_ids ;
drop table dst_files ;

drop index dst_i3 ;
1	drop index dst_i1 ;
2	drop index dst_i2 ;
3	drop index dst_i3 ;
4	drop table dst_files_ids ;
5	drop table dst_files ;
6
7
8	select hostid,shareid,num,abstime(date),size,inc_size into dst_files from backups where date > abstime('2005-10-24')::int order by abstime,hostid,num;
9
10	delete from archive_backup where backup_id in (select id from backups where date > abstime('2005-10-24')::int) ;
11	delete from backups where date > abstime('2005-10-24')::int;
12	vacuum analyze backups;
13
14	create index dst_i1 on dst_files(shareid) ;
15	create index dst_i2 on dst_files(num) ;
16	vacuum analyze dst_files ;
17	create index dst_i3 on files(backupnum) ;
18	vacuum analyze files ;
19
20	select id into dst_files_ids from files inner join dst_files on dst_files.shareid = files.shareid and dst_files.num = files.backupnum ;
21	create unique index dst_i4 on dst_files_ids(id) ;
22	vacuum analyze dst_files_ids ;
23
24	delete from files where id in (select id from dst_files_ids) ;
25
26	vacuum full analyze verbose files ;
27
28	drop table dst_files_ids ;
29	drop table dst_files ;
30
31	drop index dst_i3 ;