Redshift Observatory

System Table Tracker

System view pg_catalog.svl_restore_alter_table_progress version 1.0.74503 / 2024-09-16

schema name column data type
pg_catalog svl_restore_alter_table_progress message varchar(350)
pg_catalog svl_restore_alter_table_progress progress varchar(41)
pg_catalog svl_restore_alter_table_progress tbl int4

View Text

((SELECT conversion_completed_tables.tbl,
         CAST(CAST(round(conversion_completed_tables.progress, 2) AS text) || CAST('%' AS text) AS varchar) AS progress,
         CAST(CASE
                WHEN conversion_completed_tables.task_type = 1
                  THEN CAST('Restored to distkey successfully.' AS text)
                WHEN conversion_completed_tables.task_type = 2
                  THEN CAST('Rebalanced disteven table successfully.' AS text)
                ELSE CAST(NULL AS text)
              END AS varchar) AS message
  FROM (SELECT stl_xrestore_alter_distkey.pid,
               stl_xrestore_alter_distkey.tbl,
               100.0 AS progress,
               stl_xrestore_alter_distkey.task_type
        FROM stl_xrestore_alter_distkey
        WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Completed%' AS text)) AS conversion_completed_tables

  UNION

  SELECT aborted_tbl.tbl,
         'ABORTED' AS progress,
         aborted_tbl.message
  FROM (SELECT DISTINCT stl_xrestore_alter_distkey.tbl,
                        stl_xrestore_alter_distkey.message
        FROM stl_xrestore_alter_distkey
        WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Abort:%' AS text)) AS aborted_tbl)

 UNION

 SELECT tbl_in_progress.tbl,
        CAST(CAST(round(tbl_in_progress.progress, 2) AS text) || CAST('%' AS text) AS varchar) AS progress,
        CAST(CASE
               WHEN tbl_in_progress.task_type = 1
                 THEN CAST('Alter diststyle in progress.' AS text)
               WHEN tbl_in_progress.task_type = 2
                 THEN CAST('Rebalance disteven in progress.' AS text)
               ELSE CAST(NULL AS text)
             END AS varchar) AS message
 FROM (SELECT alt_dist.tbl,
              CASE
                WHEN alt_dist.src_rows = 0
                  THEN 100.0
                ELSE CAST(100 * alt_dist.dest_rows AS numeric) / (CAST(alt_dist.src_rows AS numeric) * 1.0)
              END AS progress,
              last_event.task_type
       FROM (SELECT alter_dist.tbl,
                    max(alter_dist.eventtime) AS last_eventtime,
                    auto_worker.task_type
             FROM (SELECT stl2.tbl,
                          stl2.pid,
                          stl2.task_type
                   FROM (SELECT stl.tbl,
                                max(stl.event_time) AS last_eventtime
                         FROM stl_xrestore_alter_distkey AS stl
                         GROUP BY stl.tbl) AS table_max_eventtime
                        INNER JOIN stl_xrestore_alter_distkey AS stl2 ON stl2.tbl = table_max_eventtime.tbl
                                                                     AND stl2.event_time = table_max_eventtime.last_eventtime
                        INNER JOIN stv_xrestore_alter_queue_state AS stv ON table_max_eventtime.tbl = stv.tbl
                                                                        AND table_max_eventtime.tbl = stl2.tbl
                   WHERE stv.status ~~ CAST('%Applying%' AS text)
                     AND stl2.message !~ similar_escape(CAST('%(Completed|Abort:)%' AS text),
                                                        CAST(NULL AS text))) AS auto_worker
                  INNER JOIN stl_alter_dist_sort AS alter_dist ON auto_worker.tbl = alter_dist.tbl
                                                              AND auto_worker.pid = alter_dist.pid
             WHERE NOT alter_dist.tbl IN (SELECT conversion_completed_tables.tbl
                                          FROM (SELECT stl_xrestore_alter_distkey.pid,
                                                       stl_xrestore_alter_distkey.tbl,
                                                       100.0 AS progress,
                                                       stl_xrestore_alter_distkey.task_type
                                                FROM stl_xrestore_alter_distkey
                                                WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Completed%' AS text)) AS conversion_completed_tables)
               AND alter_dist.event !~~ CAST('%Start%' AS text)
               AND alter_dist.is_autoworker_altered = 1
             GROUP BY alter_dist.tbl, auto_worker.task_type) AS last_event
            INNER JOIN stl_alter_dist_sort AS alt_dist ON last_event.last_eventtime = alt_dist.eventtime
                                                      AND last_event.tbl = alt_dist.tbl) AS tbl_in_progress)

UNION

SELECT tbl_pending.tbl,
       CAST(CAST(round(tbl_pending.progress, 2) AS text) || CAST('%' AS text) AS varchar) AS progress,
       CAST(CASE
              WHEN tbl_pending.action ~~ CAST('%DistkeyTableImportedAsDistEven%' AS text)
                THEN CAST('Table waiting for alter diststyle conversion.' AS text)
              WHEN tbl_pending.action ~~ CAST('%DistevenTableMarkedForRedistribution%' AS text)
                THEN CAST('Table waiting for disteven rebalancing.' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar) AS message
FROM (SELECT all_tbl.tbl,
             0.0 AS progress,
             all_tbl.action
      FROM stl_xrestore_cluster_level_distkey_tables AS all_tbl
      WHERE (all_tbl.action ~~ CAST('%DistkeyTableImportedAsDistEven%' AS text)
          OR all_tbl.action ~~ CAST('%DistevenTableMarkedForRedistribution%' AS text))
        AND NOT all_tbl.tbl IN ((SELECT conversion_completed_tables.tbl
                                 FROM (SELECT stl_xrestore_alter_distkey.pid,
                                              stl_xrestore_alter_distkey.tbl,
                                              100.0 AS progress,
                                              stl_xrestore_alter_distkey.task_type
                                       FROM stl_xrestore_alter_distkey
                                       WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Completed%' AS text)) AS conversion_completed_tables

                                 UNION

                                 SELECT tbl_in_progress.tbl
                                 FROM (SELECT alt_dist.tbl,
                                              CASE
                                                WHEN alt_dist.src_rows = 0
                                                  THEN 100.0
                                                ELSE CAST(100 * alt_dist.dest_rows AS numeric) / (CAST(alt_dist.src_rows AS numeric) * 1.0)
                                              END AS progress,
                                              last_event.task_type
                                       FROM (SELECT alter_dist.tbl,
                                                    max(alter_dist.eventtime) AS last_eventtime,
                                                    auto_worker.task_type
                                             FROM (SELECT stl2.tbl,
                                                          stl2.pid,
                                                          stl2.task_type
                                                   FROM (SELECT stl.tbl,
                                                                max(stl.event_time) AS last_eventtime
                                                         FROM stl_xrestore_alter_distkey AS stl
                                                         GROUP BY stl.tbl) AS table_max_eventtime
                                                        INNER JOIN stl_xrestore_alter_distkey AS stl2 ON stl2.tbl = table_max_eventtime.tbl
                                                                                                     AND stl2.event_time = table_max_eventtime.last_eventtime
                                                        INNER JOIN stv_xrestore_alter_queue_state AS stv ON table_max_eventtime.tbl = stv.tbl
                                                                                                        AND table_max_eventtime.tbl = stl2.tbl
                                                   WHERE stv.status ~~ CAST('%Applying%' AS text)
                                                     AND stl2.message !~ similar_escape(CAST('%(Completed|Abort:)%' AS text),
                                                                                        CAST(NULL AS text))) AS auto_worker
                                                  INNER JOIN stl_alter_dist_sort AS alter_dist ON auto_worker.tbl = alter_dist.tbl
                                                                                              AND auto_worker.pid = alter_dist.pid
                                             WHERE NOT alter_dist.tbl IN (SELECT conversion_completed_tables.tbl
                                                                          FROM (SELECT stl_xrestore_alter_distkey.pid,
                                                                                       stl_xrestore_alter_distkey.tbl,
                                                                                       100.0 AS progress,
                                                                                       stl_xrestore_alter_distkey.task_type
                                                                                FROM stl_xrestore_alter_distkey
                                                                                WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Completed%' AS text)) AS conversion_completed_tables)
                                               AND alter_dist.event !~~ CAST('%Start%' AS text)
                                               AND alter_dist.is_autoworker_altered = 1
                                             GROUP BY alter_dist.tbl, auto_worker.task_type) AS last_event
                                            INNER JOIN stl_alter_dist_sort AS alt_dist ON last_event.last_eventtime = alt_dist.eventtime
                                                                                      AND last_event.tbl = alt_dist.tbl) AS tbl_in_progress)

                                UNION

                                SELECT aborted_tbl.tbl
                                FROM (SELECT DISTINCT stl_xrestore_alter_distkey.tbl,
                                                      stl_xrestore_alter_distkey.message
                                      FROM stl_xrestore_alter_distkey
                                      WHERE stl_xrestore_alter_distkey.message ~~ CAST('%Abort:%' AS text)) AS aborted_tbl)) AS tbl_pending


Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers