Hologres:Dynamic table refresh

Using hologres.hg_dynamic_table_refresh_activity

The hologres.hg_dynamic_table_refresh_activity system table shows running refresh tasks — including full and incremental refreshes — along with their resource consumption. For field descriptions, see hologres.hg_dynamic_table_refresh_activity system table.

This system table is supported only in Hologres V3.0, and V4.0.8 and later.

-- View currently running refresh tasks
SELECT
    pid,
    query_id,
    refresh_mode,
    'RUNNING' AS status,
    refresh_start,
    extract(epoch FROM duration) AS duration,                            -- milliseconds
    serverless_queue_time_ms::bigint / 1000 AS serverless_queue_time_sec,
    serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_sec,
    serverless_allocated_cores
FROM hologres.hg_dynamic_table_refresh_activity
WHERE datname = '${database}'
  AND table_write = quote_ident('${schema}') || '.' || quote_ident('${tableName}')
ORDER BY refresh_start DESC
LIMIT 2000;

Using hg_stat_activity

The hg_stat_activity system view also shows running refresh tasks. The display differs by refresh mode:

• Full refresh: An INSERT statement appears.

• Incremental refresh: A Refresh task appears.

Using monitoring metrics

Check metrics such as QPS, RPS (records per second), and latency to confirm execution status. A Command Type of refresh indicates a dynamic table refresh task. For more information, see Hologres console monitoring metrics.

If the refresh task runs on Serverless Computing resources, you can also check its status in the Serverless Computing metrics.

Using hologres.hg_dynamic_table_refresh_history

The hologres.hg_dynamic_table_refresh_history system table records the history of all refresh tasks — full, incremental, and manual — for the past month. For field descriptions, see hologres.hg_dynamic_table_refresh_history system table.

Records are retained for one month. Data older than one month cannot be queried.

Table owners can view only their own refresh history. Users with the superuser role can view all refresh records.

Example 1: View incremental refresh records from the past day

SELECT
    query_id,
    refresh_mode,
    status,
    refresh_start,
    duration,
    refresh_latency / 1000 AS refresh_latency_second,
    serverless_allocated_cores,
    queue_time_ms::bigint / 1000 AS queue_time_second,
    serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
FROM hologres.hg_dynamic_table_refresh_history
WHERE refresh_start >= CURRENT_DATE - INTERVAL '1 day'
  AND dynamic_table_name = '<dynamic_table>'
  AND refresh_mode = 'incremental'
ORDER BY refresh_start DESC
LIMIT 100;

Example 2: View all refresh records in the instance from the past day

SELECT
    query_id,
    refresh_mode,
    status,
    refresh_start,
    duration,
    refresh_latency / 1000 AS refresh_latency_second,
    serverless_allocated_cores,
    queue_time_ms::bigint / 1000 AS queue_time_second,
    serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
FROM hologres.hg_dynamic_table_refresh_history
WHERE refresh_start >= CURRENT_DATE - INTERVAL '1 day';

Example 3: View refresh records for a specific table from the past day

SELECT
    query_id,
    refresh_mode,
    status,
    refresh_start,
    duration,
    refresh_latency / 1000 AS refresh_latency_second,
    serverless_allocated_cores,
    queue_time_ms::bigint / 1000 AS queue_time_second,
    serverless_resource_used_time_ms::bigint / 1000 AS serverless_resource_used_time_second
FROM hologres.hg_dynamic_table_refresh_history
WHERE schema_name = '<schema_name>'
  AND dynamic_table_name = '<dynamic_table>'
  AND refresh_start >= CURRENT_DATE - INTERVAL '1 day';

For full-refresh dynamic tables created with the legacy 3.0 syntax, hologres.hg_dynamic_table_refresh_history may not accurately reflect the success or failure status — a failed refresh may appear as Success. To retrieve the true refresh history for these tables: 1. Get the cron_job_name from hologres.hg_dynamic_table_properties. 2. Query cron job execution records using that name.

-- Step 1: Get the cron_job_name
SELECT property_value AS cron_job_name
FROM hologres.hg_dynamic_table_properties
WHERE dynamic_table_name = '<dt_name>'
  AND property_key = 'cron_job_name';

-- Step 2: Query cron job execution records
SELECT *
FROM hologres.hg_user_cron_tasks
WHERE jobname = '<cron_job_name>'
ORDER BY start_time DESC;

Using slow query logs

Refresh tasks also appear in slow query logs with Command Type set to refresh. For more information, see Get and analyze slow query logs.

Cancel a running refresh task

Query the query_job_id of the running task from hologres.hg_dynamic_table_refresh_log, then cancel it using hologres.hg_internal_cancel_query_job.

-- Step 1: Get the query_job_id
SELECT query_job_id
FROM hologres.hg_dynamic_table_refresh_log('<dt_name>')
WHERE status = 'Running';

-- Step 2: Cancel the task
SELECT hologres.hg_internal_cancel_query_job('<query_job_id>');

Only a superuser can cancel a refresh task using hologres.hg_internal_cancel_query_job.

Pause auto-refresh for a table

To stop all subsequent refresh tasks at the table level, disable auto-refresh:

ALTER DYNAMIC TABLE [IF EXISTS] [<schema>.]<table_name> SET (auto_refresh_enable = false);

Cancel a running refresh task

If a refresh task is taking too long or appears stuck, cancel it using pg_cancel_backend.

-- Cancel the refresh task by its process ID (pid)
SELECT pg_cancel_backend(<pid>);

Get the pid from hologres.hg_dynamic_table_refresh_activity or hg_stat_activity. For more information, see View running refresh tasks.

To cancel refresh tasks in batches, use the same method as for regular queries. For more information, see Stop a query.

Pause auto-refresh for a table

To stop all subsequent refresh tasks at the table level:

ALTER DYNAMIC TABLE [IF EXISTS] [<schema>.]<table_name> SET (auto_refresh_enable = false);