Crazy Migration

foo.sql

CREATE TRIGGER migrate_products AFTER UPDATE OF migrated ON products FOR EACH ROW WHEN (OLD.migrated IS NULL AND NEW.migrated IS true) EXECUTE PROCEDURE populate_referenced_entities();

    CREATE OR REPLACE FUNCTION batch_set_migrated() RETURNS INTEGER LANGUAGE plpgsql AS $$
    DECLARE batched_count INTEGER = 1;
    BEGIN
        WITH unmigrated_products AS (
            SELECT id
            FROM products
            WHERE migrated IS NULL
            LIMIT 50
            FOR UPDATE NOWAIT
        ),
        migrated_products AS (
            UPDATE products
            SET migrated = true
            FROM unmigrated_products
            WHERE unmigrated_products.id = products.id
            RETURNING products.id
        ) SELECT COUNT(1) INTO batched_count FROM migrated_products;
        RETURN batched_count;
    END$$;
    
    DO LANGUAGE plpgsql $$
    DECLARE counter INTEGER = 1;
    BEGIN
        WHILE counter > 0 LOOP
            SELECT INTO counter batch_set_migrated();
        END LOOP;
    END$$;

    DROP TRIGGER migrate_products ON products;