Remove refresh view scheduled task and add as postgres cron job

[nullpointer0x00]

Summary

Remove the application-level scheduling of materialized view refreshes and move these tasks to be scheduled directly within postgres with a flyway DB upgrade.

Problem Definition

Currently, the application is responsible for refreshing materialized views on a daily schedule using the following code:

@Scheduled(cron = "0 0 2 * * ?") // Every day at 2 AM
fun refreshMaterializedViews() {
    logger.info("Refreshing fee-based views")
    TxHistoryDataViews.refreshViews()
}

This approach is unnecessarily tying the scheduling of database-related tasks to the application logic. The materialized views should be managed at the database level using PostgreSQL's built-in scheduling capabilities.

Scheduling the REFRESH MATERIALIZED VIEW jobs directly in PostgreSQL also ensures the task is consistently executed, regardless of the application’s state (e.g., downtime or restarts).

Proposal

• Remove the @Scheduled function refreshMaterializedViews from the application code.
• Implement a Flyway DB migration that schedules the following tasks within PostgreSQL:

  CREATE OR REPLACE FUNCTION refresh_tx_history_materialized_views() RETURNS VOID LANGUAGE plpgsql AS $$
  BEGIN
      PERFORM REFRESH MATERIALIZED VIEW CONCURRENTLY tx_history_chart_data_hourly;
      PERFORM REFRESH MATERIALIZED VIEW CONCURRENTLY tx_type_data_hourly;
      PERFORM REFRESH MATERIALIZED VIEW CONCURRENTLY fee_type_data_hourly;
  END $$;
  
  -- Schedule the function to run every day at 2 AM
  CREATE EXTENSION IF NOT EXISTS pg_cron;
  SELECT cron.schedule('0 2 * * *', 'SELECT refresh_tx_history_materialized_views();');

• Ensure the new jobs are properly scheduled in PostgreSQL upon database migration.

---

For Admin Use

• Not duplicate issue
• Appropriate labels applied
• Appropriate contributors tagged
• Contributor assigned/self-assigned