Script to recover MARC_BIB records without ACTUAL or DELETED version

The script identifies MARC_BIB records with no ACTUAL or DELETED version and sets the latest generation to ACTUAL.

SET SEARCH_PATH = '{tenantId}_mod_source_record_storage';

WITH
invalid_records AS (
  SELECT external_id, MAX(generation) AS max_generation
  FROM records_lb
  GROUP BY external_id
  HAVING COUNT(*) FILTER (WHERE state IN ('ACTUAL', 'DELETED')) = 0
),
max_generation_invalid_records AS (
  SELECT r.id
  FROM records_lb r
  JOIN invalid_records ir ON r.external_id = ir.external_id 
       AND r.generation = ir.max_generation
  WHERE r.record_type = 'MARC_BIB'
)

UPDATE records_lb
SET state = 'ACTUAL'
WHERE id IN (SELECT id FROM max_generation_invalid_records);

For 8 million records, the execution time is around 20 seconds