Lineage updates conflict with removed (deleted)

[aazaff]

SCENARIO

1. Map A is superseded by Map B (succeeds)
2. Map B is deleted (succeeds)
3. Map A is superseded by Map C (fails because Map A shows as already superseded by Map B)

Solution:
Deleting a superseding map should update the lineage_removed table accordingly.

[NoisyFlowers]

Are you able to delete any collections at all? I found a bug in the web client that appears to prevent all deletes (they don't even get sent to the api).

Fixing that allows collections to be deleted, and I am unable to reproduce the problem described.

[aazaff]

By bug in the web client... do you mean this one azgs/azlibrary_webclient#15? 😇

[aazaff]

Going back to the original question... I was positive that I had a specific set of entries in mind for this issue, but going through the issues in the geologic map tracking repo... I don't see anything open OR closed.

Given that past me did not do a proper job of documenting this and that you cannot reproduce the bug, we are going to conclude that the bug no longer (or possibly never) exists.

[NoisyFlowers]

I'll try to reproduce it on dev, whenever that is working again.

[aazaff]

Ugh, you were right. We should have chased this down. Here is a specific case on Prod.

There are two test collections on prod that you made a long time ago that shouldn't be displaying since they are dummies so I wanted to remove them. However, since the delete function on prod is broken until we push your latest patch, I figured I would just make them private.

No dice.

Dummy record APIX-1566429656681-697 supersedes dummy record APIX-1566429145457-402. We apparently already deleted the superseding record (-697) some time in the distant past, so all we should need to do is mark the original record (-402) as private. This fails with the following error:

{
    "code": 403,
    "msg": "Collection APIX-1566429145457-402 is already deprecated by collection APIX-1566429656681-697",
    "links": [
        {
            "href": "https://data.azgs.arizona.edu/api/v1/collections?help=json",
            "rel": "help"
        },
        {
            "href": "https://data.azgs.arizona.edu/api/v1/metadata/APIX-1566429656681-697",
            "rel": "successor-version"
        }
    ]
}

Inspection of the lineages, lineages_removed, and collections tables does indeed show that the -697 still shows as superseding -402 even though -697 is marked as removed = TRUE in the collections table. Which is wrong.

My hypothesis is that when we implemented the lineage_removed table we did not retroactively remove lineages for removed collections, the implemented logic only handled new deletes. I think this is almost certainly true as we can see on the prod database that the lineage_removed table only has TWO entries -- which I highly doubt is correct.

[aazaff]

Another real world example is ADGM-1552430342891-875 cannot be deleted because it is superseded by
ADGM-1655494459900-337, which has already been delated.

[NoisyFlowers]

Fundamentally, we are talking about collections that meet the following criteria:

1. supersede another collection
2. have been marked as removed in public.collections
3. still have a record in public.lineage that has not been moved to public.lineage_removed

To determine how many of these collections exist, I run this query:

select 
	collection_id,
	perm_id,
	superseded_by,
	supersedes,
	removed
from 
	public.collections_lineage_view cv
where
	removed = true and 
	supersedes is not null and
	0 = (select 
		 	count(*) 
		from 
		 	public.lineage_removed l 
		where 
		 	l.collection = cv.perm_id);

Looks like there are five:

To fix them, I propose using the unlinkSQL from collections.model.js, and injecting the above as a subquery in the where clause:

with deleted_rows as (
	delete from
		public.lineage
	where 
		collection in (
			select 
				perm_id
			from 
				public.collections_lineage_view cv
			where
				removed = true and 
				supersedes is not null and
				0 = (select 
						count(*) 
					from 
						public.lineage_removed l 
					where 
						l.collection = cv.perm_id
				)
		)
		returning *
)
insert into 
	public.lineage_removed
		select * from deleted_rows

I ran this on dev, and it appears to work.

Aside: This doesn't clean up identifiers->supersedes in the collection's metadata. In looking into how best to do that, I discovered that we simply don't right now. I'm not sure if that was a decision or an oversight. Since the collection is marked removed, I don't see that leaving the metadata in its previous state would cause any problem.

[aazaff]

Can this correction now be pushed to main?

[aazaff]

We will wait until the new 2023 instance is up