Skip to content

Instantly share code, notes, and snippets.

@omad
Last active March 17, 2021 17:22
Show Gist options
  • Save omad/1ae3463a123f37a9acf37213bebfde86 to your computer and use it in GitHub Desktop.
Save omad/1ae3463a123f37a9acf37213bebfde86 to your computer and use it in GitHub Desktop.
Delete an Open Data Cube Product using RAW SQL
--------------------------------------
-- SQL to Delete a Data Cube Product
--------------------------------------
--
-- Use with psql from the command line:
--
-- psql -v product_name=<product-to-delete> -f delete_odc_product.sql -h <database-hostname> <dbname>
--
--
-- COUNT NUMBER OF DATASETS OF EACH TYPE (including archived)
--
-- select
-- count(*),
-- t.name
-- from dataset
-- left join dataset_type t on dataset.dataset_type_ref = t.id
-- group by t.name;
--
-- CHECK FOR LINEAGE RECORDS
--
-- Are there any datasets that are descendents of this product?
-- If so, they will need to be removed first!
set search_path = 'agdc';
select count(*)
from dataset_source
left join dataset d on dataset_source.source_dataset_ref = d.id
where d.dataset_type_ref = (select id
from dataset_type
where dataset_type.name = :'product_name');
-- Are there any lineage records which need deleting?
-- These are the lineage history of the product we're deleting.
select count(*)
from dataset_source
left join dataset d on dataset_source.dataset_ref = d.id
where d.dataset_type_ref = (select id
from dataset_type
where dataset_type.name = :'product_name');
--
-- DELETE LINEAGE RECORDS
--
WITH datasets as (SELECT id
FROM dataset
where dataset.dataset_type_ref = (select id
FROM dataset_type
WHERE name = :'product_name'))
DELETE FROM dataset_source
USING datasets
where dataset_source.dataset_ref = datasets.id;
--
-- CHECK FOR LOCATION RECORDS
--
select count(*)
from dataset_location
left join dataset d on dataset_location.dataset_ref = d.id
where d.dataset_type_ref = (select id
from dataset_type
where dataset_type.name = :'product_name');
WITH datasets as (SELECT id
FROM dataset
where dataset.dataset_type_ref = (select id
FROM dataset_type
WHERE name = :'product_name'))
select count(*)
from dataset_location, datasets
where dataset_location.dataset_ref = datasets.id;
--
-- DELETE LOCATION RECORDS
--
WITH datasets as (SELECT id
FROM dataset
where dataset.dataset_type_ref = (select id
FROM dataset_type
WHERE name = :'product_name'))
DELETE FROM dataset_location
USING datasets
where dataset_location.dataset_ref = datasets.id;
--
-- DELETE DATASET RECORDS
--
DELETE FROM dataset
where dataset.dataset_type_ref = (select id
from dataset_type
where dataset_type.name = :'product_name');
--
-- FINALLY, DELETE THE PRODUCT
--
DELETE FROM dataset_type
where dataset_type.name = :'product_name';
@whatnick
Copy link

whatnick commented Dec 16, 2019

If the product has been added in OWS this will fail since OWS maintains a foreign key to ODC:

psql:delete_odc_product.sql:103: ERROR:  update or delete on table "dataset_type" 
violates foreign key constraint "product_ranges_id_fkey" on table "product_ranges"
DETAIL:  Key (id)=(2) is still referenced from table "product_ranges".

Note the error generated by the delete attempt and delete any downstream references in OWS:

delete from wms.product_ranges where id=2;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment