Skip to main content

Fixing Location History Refresh Issues

This guide explains how to fix issues with the location history refresher process not properly updating data after new tracker reports are fetched.

Problem Description​

The system has the following components:

  1. tracker-report-fetcher: Fetches reports from trackers and stores them in the database
  2. location-history-refresher: Listens for PostgreSQL notifications to refresh the materialized views

The issue occurs when:

  • The fetcher successfully retrieves and stores reports
  • The refresher receives notifications and refreshes the location_history materialized view
  • However, the continuous aggregates (location_history_hourly and location_history_daily) are not properly refreshed
  • Data doesn't appear to be updated until manually running refresh commands

Root Causes​

  1. Database Connection Issues: If the refresher experiences database connection issues, it might miss notifications
  2. Continuous Aggregate Refresh Timing: The continuous aggregates are only refreshed periodically (hourly) and not when notifications are received
  3. Data Retention Concerns: Raw data in location_reports is only kept for 2 days, which might be too short if there are issues with the aggregation process

Solution​

We've implemented the following improvements:

  1. Enhanced Notification Handling: When a notification is received, both the materialized view and continuous aggregates are refreshed
  2. Timestamp-based Polling Fallback: A new mechanism periodically checks for unprocessed data based on timestamps
  3. Extended Data Retention: The retention period for raw data in location_reports has been increased from 2 days to 7 days

Applying the Changes​

1. Update the Location History Refresher​

The location_history_refresher.py file has been updated with the following improvements:

  • When a notification is received, both the materialized view and continuous aggregates are refreshed
  • A new check_for_missed_data() function periodically checks for unprocessed data
  • Timestamps are tracked in Redis to ensure data is not missed

To apply these changes:

  1. Stop the location-history-refresher service:

    docker-compose -f fetcher/compose.yml stop location-history-refresher

  2. The updated location_history_refresher.py file is already in place at fetcher/build/location_history_refresher.py

  3. Restart the location-history-refresher service:

    docker-compose -f fetcher/compose.yml up -d location-history-refresher

2. Update the Data Retention Policy​

The retention period for raw data in location_reports has been increased from 2 days to 7 days to ensure data is not deleted before it's properly aggregated.

To apply this change:

  1. Run the update_retention_policy.py script: 
    python scripts/update_retention_policy.py

Verifying the Changes​

After applying the changes, you can verify that they're working correctly:

  1. Check the logs of the location-history-refresher service:

    docker-compose -f fetcher/compose.yml logs -f location-history-refresher

    Look for messages like:

    • "Also refreshing continuous aggregates..."
    • "Hourly aggregate refresh complete"
    • "Daily aggregate refresh complete"

  2. Verify that new tracker reports are properly reflected in the location history:

    • Wait for new reports to be fetched
    • Check that the data appears in the location history without manual intervention

  3. Verify the updated retention policy:

    SELECT * FROM _timescaledb_config.bgw_job
    WHERE proc_name = 'policy_retention'
    AND hypertable_name = 'location_reports';

    The config column should show a 7-day interval.

Troubleshooting​

If you still experience issues:

  1. Manual Refresh: You can still manually refresh the continuous aggregates and materialized view:

    CALL refresh_continuous_aggregate('location_history_hourly', NULL, NULL);
    CALL refresh_continuous_aggregate('location_history_daily', NULL, NULL);
    REFRESH MATERIALIZED VIEW CONCURRENTLY location_history;

  2. Check Redis: Verify that Redis is working correctly:

    docker-compose -f fetcher/compose.yml exec location-history-refresher redis-cli ping

    It should respond with "PONG".

  3. Check Database Connectivity: Verify that the refresher can connect to the database:

    docker-compose -f fetcher/compose.yml logs location-history-refresher | grep "Connected to database"

  4. Restart Services: If all else fails, try restarting both services:

    docker-compose -f fetcher/compose.yml restart