Redshift Observatory

System Table Tracker

System view pg_catalog.svv_vacuum_progress version 1.0.60140 / 2023-11-19

View Text

SELECT CAST(rtrim(CAST(t.name AS text)) AS varchar(255)) AS table_name,
       CAST(CASE
              WHEN v.status ~~* CAST('%Finished%' AS text)
                THEN CAST('Complete' AS text)
              WHEN v.status ~~ CAST('Skipped%' AS text)
                THEN rtrim(CAST(v.status AS text))
              WHEN x.xid IS NULL
               AND (v.status !~~* CAST('%Finished%' AS text)
                AND v.status !~~ CAST('Skipped%' AS text))
                THEN CAST('Failed' AS text)
              ELSE rtrim(CAST(q.querytxt AS text))
            END AS varchar(4000)) AS status,
       CAST(CASE
              WHEN v.status ~~* CAST('%Finished%' AS text)
                OR v.status ~~ CAST('Skipped%' AS text)
                OR x.xid IS NULL
                OR e.est_remaining IS NULL
                THEN CAST(NULL AS text)
              WHEN CAST(e.est_remaining AS text) < CAST(0 AS text)
               AND NOT(v.status ~~* CAST('%Finished%' AS text)
                    OR v.status ~~ CAST('Skipped%' AS text)
                    OR x.xid IS NULL
                    OR e.est_remaining IS NULL)
                THEN CAST('0m 0s' AS text)
              ELSE ((CAST(CAST(date_part(CAST('minute' AS text),
                                         e.est_remaining) AS varchar) AS text) + CAST('m ' AS text)) + CAST(CAST(date_part(CAST('second' AS text),
                                                                                                                           e.est_remaining) - (date_part(CAST('minute' AS text),
                                                                                                                                                         e.est_remaining) * CAST(60 AS double precision)) AS varchar) AS text)) + CAST('s' AS text)
            END AS varchar(32)) AS time_remaining_estimate
FROM stl_vacuum AS v
     LEFT JOIN stv_xact AS x ON v.xid = x.xid,
     stl_query AS q,
     stv_tbl_perm AS t,
     (SELECT max(derived_table16.time_remaining) AS est_remaining
      FROM (SELECT d.slice,
                   sum(d.num_blocks_replaced) AS blocks_replaced,
                   sum(q.endtime - q.starttime) AS etime,
                   CASE
                     WHEN sum(d.num_blocks_replaced) = 0
                       OR max(d.total_block_io_estimate) = 0
                       THEN CAST(NULL AS interval)
                     ELSE (sum(q.endtime - q.starttime) / CAST(CAST(sum(d.num_blocks_replaced) AS real) / CAST(max(d.total_block_io_estimate) AS real) AS double precision)) - sum(q.endtime - q.starttime)
                   END AS time_remaining
            FROM stl_query AS q, stl_vacuum AS v, stl_vacuum_detail AS d
            WHERE q.xid = v.xid
              AND v.xid = (SELECT max(stl_vacuum.xid) AS max
                           FROM stl_vacuum)
              AND v.status !~~* CAST('%Finished%' AS text)
              AND q.query = d.query
            GROUP BY d.slice) AS derived_table16) AS e
WHERE q.xid = v.xid
  AND v.xid = (SELECT max(stl_vacuum.xid) AS max
               FROM stl_vacuum)
  AND v.table_id = t.id
  AND t.slice = 0
ORDER BY q.query DESC,
         v.eventtime DESC
LIMIT 1

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