Harvest source clear fails on datasets with collaborators & other db entanglements

[pdekraker-epa]

When "clearing" datasets from a harvest source that have dataset collaborators the process fails.

An error occurred: [(psycopg2.errors.ForeignKeyViolation) update or delete on table "package" violates foreign key constraint "package_member_package_id_fkey" on table "package_member" DETAIL: Key (id)=(85b0969e-63ec-4d2b-a3aa-94b1b8b29115) is still referenced from table "package_member". 
[SQL: BEGIN; UPDATE package set state = 'to_delete' WHERE id IN ( SELECT package_id FROM harvest_object WHERE harvest_source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'); 
DROP TABLE IF EXISTS package_ids_to_delete; 
CREATE TEMP TABLE package_ids_to_delete ON COMMIT DROP AS ( SELECT id FROM package WHERE state = 'to_delete'); 
DELETE FROM harvest_object_error WHERE harvest_object_id IN ( SELECT id FROM harvest_object WHERE harvest_source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'); 
DELETE FROM harvest_object_extra WHERE harvest_object_id IN ( SELECT id FROM harvest_object WHERE harvest_source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'); 
DELETE FROM harvest_object WHERE harvest_source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'; 
DELETE FROM harvest_gather_error WHERE harvest_job_id IN ( SELECT id FROM harvest_job WHERE source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'); 
DELETE FROM harvest_job WHERE source_id = 'ca073d95-4382-46bf-94f6-1041d742c08c'; 
DELETE FROM package_extra WHERE package_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM package_tag WHERE package_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM package_relationship WHERE subject_package_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM package_relationship WHERE object_package_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM member WHERE table_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM resource_view WHERE resource_id IN ( SELECT id FROM resource WHERE package_id IN ( SELECT id FROM package_ids_to_delete)); DELETE FROM resource WHERE package_id IN ( SELECT id FROM package_ids_to_delete); 
DELETE FROM package WHERE id IN ( SELECT id FROM package_ids_to_delete); 
COMMIT; ]
(Background on this error at: https://sqlalche.me/e/14/gkpj)]

In looking a the code, it appears that this process is handled via a collection of SQL statements. I recognize that this specific issue could be handled by simply adding another delete statement to remove entries from package_member, but wonder about the underlying approach. If other extensions add database tables with foreign keys referencing the package or its resources the result could be the same, and adding a delete here isn't really a solution. Iterating through the packages to delete them via package_delete is probably slower, but might be more robust.

ckanext-harvest/ckanext/harvest/logic/action/update.py

Lines 120 to 197 in b74cba2

	sql = "BEGIN;"
	sql += f"""
	UPDATE package set state = 'to_delete' WHERE id IN (
	SELECT package_id FROM harvest_object
	WHERE harvest_source_id = '{harvest_source_id}');"""
	sql += """
	DROP TABLE IF EXISTS package_ids_to_delete;
	CREATE TEMP TABLE package_ids_to_delete ON COMMIT DROP AS (
	SELECT id FROM package WHERE state = 'to_delete');
	"""
	sql += f"""
	DELETE FROM harvest_object_error WHERE harvest_object_id IN (
	SELECT id FROM harvest_object
	WHERE harvest_source_id = '{harvest_source_id}');
	DELETE FROM harvest_object_extra WHERE harvest_object_id IN (
	SELECT id FROM harvest_object
	WHERE harvest_source_id = '{harvest_source_id}');
	DELETE FROM harvest_object WHERE harvest_source_id = '{harvest_source_id}';
	DELETE FROM harvest_gather_error WHERE harvest_job_id IN (
	SELECT id FROM harvest_job WHERE source_id = '{harvest_source_id}');
	DELETE FROM harvest_job WHERE source_id = '{harvest_source_id}';
	"""
	if toolkit.check_ckan_version(max_version="2.10.99"):
	# Revision tables were dropped IN CKAN 2.10
	sql += """
	DELETE FROM package_tag_revision WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM member_revision WHERE table_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_extra_revision WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_revision WHERE id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_relationship_revision WHERE subject_package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_relationship_revision WHERE object_package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM resource_revision WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	"""
	if toolkit.check_ckan_version(max_version="2.11.99"):
	# Package_extra table were dropped IN CKAN 2.12
	sql += """
	DELETE FROM package_extra WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	"""
	sql += """
	DELETE FROM package_tag WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_relationship WHERE subject_package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM package_relationship WHERE object_package_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM member WHERE table_id IN (
	SELECT id FROM package_ids_to_delete);
	DELETE FROM resource_view WHERE resource_id IN (
	SELECT id FROM resource WHERE package_id IN (
	SELECT id FROM package_ids_to_delete));
	DELETE FROM resource WHERE package_id IN (
	SELECT id FROM package_ids_to_delete);
	"""
	sql += """
	DELETE FROM package WHERE id IN (
	SELECT id FROM package_ids_to_delete);
	"""
	sql += """
	COMMIT;
	"""