Release Descriptions contains orphaned records

[miketheman]

Something about our Release Descriptions tables is causing orphaned records.

This was discovered in #14788 when fixing the backref to back_populates relationship flags.

This SQL query will expose the orphaned records.

select count(d.id) from release_descriptions d
left join releases r on d.id = r.description_id
where r.description_id is null;

As of this writing, the count is 621643, and had increased by two in ~4 minutes. I had thought that the changes over there would prevent records from being orphaned, so something is still causing these records to not be removed.

Since the Descriptions model doesn't maintain a foreign key to it's parent Release, rather the parent Release retains a reference to the current Description, it's not super simple to back-trace these records. We can probably use the raw text column to find similar records, but that's no guarantee since many Releases use a pretty similar Description, but it's one idea.

[miketheman]

The count today is 666275. There's been changes to the db models since this was observed.

If the number remains stable, then we can entertain a simple DELETE for these records. Otherwise we need to find out how release_description rows are being orphaned.

[di]

Looks like it's still in flux:

warehouse=> select count(d.id) from release_descriptions d
left join releases r on d.id = r.description_id
where r.description_id is null;
 count
--------
 666374
(1 row)