My tentative proposal is to delete all table entries for which no metadata has been found.
The invariant will be: if an origin/revision has metadata, there will be an entry in the table(s); if not, the origin/revision will not appear.

The more I think at the other use case (keeping track of what has already been indexed and hat hasn't, for efficiency purposes), the more I get convinced that the solution to that problem should be found elsewhere, e.g.:

• if we change an existing indexer, we need to query all revisions where the relevant metadata file paths can be found, and schedule reindexing of those. This is something that can be built on top of the provenance map
• adding a new indexer would result in the same query (and hence solution), using the metadata file paths of the new indexer

So I'm for just DELETE-ing the "empty" lines, once the current full pass is complete. Oh, and of course update the indexers accordingly :-)

What is the provenance map?

In T1549#28882, @vlorentz wrote:

What is the provenance map?

The service keeping track of where-we-have-seen-what, of which we have a POC developed by grouss ATM. Not yet in production, but it will be at some point.

Sounds like a solution for T1528 :)

Yes :-)
so, do we agree that the right fix for this task is just to get rid of empty-ish rows? or are there other arguments that we haven't considered yet?

and the NULL option

I don't understand your comment. What are the remaining arguments for using NULL instead of just deleting rows?

To know that we went over the origin and did not find anything, without additional tables.

But I agree that's a pretty weak argument

As discussed on IRC, even after cleaning up origin_intrinsic_metadata, the DELETE on revision_metadata fails with:

softwareheritage-indexer=# DELETE FROM revision_metadata
softwareheritage-indexer-# WHERE translated_metadata = '{"@context": "https://doi.org/10.5063/schema/codemeta-2.0"}'::json
b ;

ERROR:  update or delete on table "revision_metadata" violates foreign key constraint "origin_intrinsic_metadata_revision_
metadata_fkey" on table "origin_intrinsic_metadata"
DETAIL:  Key (id, indexer_configuration_id)=(\x142cdce9678d71340f8fc5398d07bf69e35d3271, 110502138) is still referenced fr
om table "origin_intrinsic_metadata".

The following fix for the above (suggested by @vlorentz ) is now running:

update revision_metadata
set translated_metadata = origin_intrinsic_metadata.metadata
from origin_intrinsic_metadata
where revision_metadata.id=origin_intrinsic_metadata.from_revision and revision_metadata.translated_metadata='{"@context": "https://doi.org/10.5063/schema/codemeta-2.0"}' and origin_intrinsic_metadata.metadata != '{"@context": "https://doi.org/10.5063/schema/codemeta-2.0"}';

The update completed, but a first attempt at the second DELETE failed with a deadlock (?!):

softwareheritage-indexer=# DELETE FROM revision_metadata
WHERE translated_metadata = '{"@context": "https://doi.org/10.5063/schema/codemeta-2.0"}'::jsonb ;                                                                                            ERROR:  deadlock detected
DETAIL:  Process 10966 waits for ShareLock on transaction 197265813; blocked by process 11754.
Process 11754 waits for ShareLock on transaction 197264487; blocked by process 10966.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (1380733,15) in relation "revision_metadata"
Time: 170864,091 ms (02:50,864)

Update: it's repeatable, and it looks like the deadlock is with a running indexer.

D1218

Once this is landed and deployed, ordering your DELETEs by revision_metadata.id will acquire locks in the same order as the idx_storage, solving the deadlock issue.

In T1549#29103, @vlorentz wrote:

D1218

Once this is landed and deployed, ordering your DELETEs by revision_metadata.id will acquire locks in the same order as the idx_storage, solving the deadlock issue.

Thanks, accepted.

(At the same time deploying it would require restarting the indexers, so we're gonna wait for a deployment anyway before being able to complete the cleanup.)

@vlorentz: lather, rinse, repeat.

softwareheritage-indexer=# DELETE FROM revision_intrinsic_metadata
WHERE metadata = '{"@context": "https://doi.org/10.5063/schema/codemeta-2.0"}'::jsonb ;
ERROR:  deadlock detected
DETAIL:  Process 23900 waits for ShareLock on transaction 212164862; blocked by process 20175.
Process 20175 waits for ShareLock on transaction 212164381; blocked by process 23900.
HINT:  See server log for query details.
CONTEXT:  while deleting tuple (772424,55) in relation "revision_intrinsic_metadata"
Time: 33048,828 ms (00:33,049)

(just happened, after indexers have been restarted including D1218)