Page MenuHomeSoftware Heritage
Paste P846

clean up raw extrinsic metadata
ActivePublic

Authored by ardumont on Nov 2 2020, 5:20 PM.
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:0000000000000000000000000000000000000000' and id < 'swh:1:snp:1111111111111111111111111111111111111111'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:1111111111111111111111111111111111111111' and id < 'swh:1:snp:2222222222222222222222222222222222222222'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:2222222222222222222222222222222222222222' and id < 'swh:1:snp:3333333333333333333333333333333333333333'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:3333333333333333333333333333333333333333' and id < 'swh:1:snp:4444444444444444444444444444444444444444'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:4444444444444444444444444444444444444444' and id < 'swh:1:snp:5555555555555555555555555555555555555555'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:5555555555555555555555555555555555555555' and id < 'swh:1:snp:6666666666666666666666666666666666666666'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:6666666666666666666666666666666666666666' and id < 'swh:1:snp:7777777777777777777777777777777777777777'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:7777777777777777777777777777777777777777' and id < 'swh:1:snp:8888888888888888888888888888888888888888'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:8888888888888888888888888888888888888888' and id < 'swh:1:snp:9999999999999999999999999999999999999999'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:9999999999999999999999999999999999999999' and id < 'swh:1:snp:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' and id < 'swh:1:snp:bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' and id < 'swh:1:snp:cccccccccccccccccccccccccccccccccccccccc'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:cccccccccccccccccccccccccccccccccccccccc' and id < 'swh:1:snp:dddddddddddddddddddddddddddddddddddddddd'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:dddddddddddddddddddddddddddddddddddddddd' and id < 'swh:1:snp:eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');
delete from raw_extrinsic_metadata
where id >= 'swh:1:snp:eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' and id < 'swh:1:snp:ffffffffffffffffffffffffffffffffffffffff'
and (format = 'replicate-npm-package-json'
or format = 'pypi-project-json');

Event Timeline

seems happy enough

softwareheritage=> explain delete from raw_extrinsic_metadata
softwareheritage-> where id >= 'swh:1:snp:0000000000000000000000000000000000000000' and id < 'swh:1:snp:1111111111111111111111111111111111111111'
softwareheritage-> and (format = 'replicate-npm-package-json'
softwareheritage(>      or format = 'pypi-project-json');
                                                                           QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on raw_extrinsic_metadata  (cost=0.69..2921949.97 rows=3143296 width=6)
   ->  Index Scan using raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata  (cost=0.69..2921949.97 rows=3143296 width=6)
         Index Cond: ((id >= 'swh:1:snp:0000000000000000000000000000000000000000'::text) AND (id < 'swh:1:snp:1111111111111111111111111111111111111111'::text))
         Filter: ((format = 'replicate-npm-package-json'::text) OR (format = 'pypi-project-json'::text))
 JIT:
   Functions: 5
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(7 rows)