From 71ccfc4f70e76eeb36a973d07bd97e0ed8a22e73 Mon Sep 17 00:00:00 2001 From: Andreas Gerler Date: Thu, 30 Jan 2020 14:40:45 +0100 Subject: [PATCH] update vertica-snippets --- .../sql-interactive-mode/.dir-locals.el | 0 .../snippets/sql-interactive-mode/busy-hours | 0 .../sql-interactive-mode/check_rle_overridden | 0 .../sql-interactive-mode/config-changes | 0 .../sql-interactive-mode/current_epoch | 0 .../snippets/sql-interactive-mode/errors | 0 .../external_table_details | 0 .../sql-interactive-mode/format_numbers | 0 .../sql-interactive-mode/get-partitions | 0 .../sql-interactive-mode/get-projections | 0 .../snippets/sql-interactive-mode/grants | 0 .../last-statement-timing | 0 .../sql-interactive-mode/last-vertica-updates | 12 ++++++ .../sql-interactive-mode/licence-history | 0 .../sql-interactive-mode/list-backups | 0 .../list-tables-row-count-size | 0 .../snippets/sql-interactive-mode/nodes | 0 .../snippets/sql-interactive-mode/pool_queues | 0 .../sql-interactive-mode/projection_storage | 0 .../snippets/sql-interactive-mode/pset_footer | 0 .../query-acquisition-queue | 0 .../sql-interactive-mode/query-event-issues | 0 .../query-long-acquisitions | 0 .../sql-interactive-mode/query-requests | 0 .../query-requests-expensive | 41 +++++++++++++++++++ .../query-requests-overview | 0 .../snippets/sql-interactive-mode/query-times | 0 .../rebalance_projection_status | 19 +++++++++ .../sql-interactive-mode/rebalance_status | 4 +- .../sql-interactive-mode/rebalance_times | 14 +++++++ .../sql-interactive-mode/resource-pool-move | 0 .../sql-interactive-mode/resource_pool_status | 0 .../resource_pool_status2 | 14 +++++++ .../sql-interactive-mode/resource_pools | 0 .../snippets/sql-interactive-mode/resources | 0 .../snippets/sql-interactive-mode/ros_count | 0 .../segmentation-islands1 | 15 +++++++ .../segmentation-islands2 | 30 ++++++++++++++ .../snippets/sql-interactive-mode/sessions | 0 .../sql-interactive-mode/storage-containers | 0 .../sql-interactive-mode/storage-locations | 0 .../snippets/sql-interactive-mode/table_locks | 0 .../sql-interactive-mode/user_sessions | 0 .../sql-interactive-mode/vertica_users | 0 .../snippets/sql-mode/.yas-parents | 0 .../vertica-snippets-autoloads.el | 0 .../vertica-snippets-pkg.el | 2 +- .../vertica-snippets.el | 0 48 files changed, 148 insertions(+), 3 deletions(-) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/.dir-locals.el (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/busy-hours (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/check_rle_overridden (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/config-changes (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/current_epoch (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/errors (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/external_table_details (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/format_numbers (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/get-partitions (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/get-projections (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/grants (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/last-statement-timing (100%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-vertica-updates rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/licence-history (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/list-backups (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/list-tables-row-count-size (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/nodes (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/pool_queues (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/projection_storage (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/pset_footer (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-acquisition-queue (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-event-issues (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-long-acquisitions (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-requests (100%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-expensive rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-requests-overview (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/query-times (100%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_projection_status rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/rebalance_status (91%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_times rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/resource-pool-move (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/resource_pool_status (100%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status2 rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/resource_pools (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/resources (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/ros_count (100%) create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands1 create mode 100644 elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands2 rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/sessions (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/storage-containers (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/storage-locations (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/table_locks (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/user_sessions (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-interactive-mode/vertica_users (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/snippets/sql-mode/.yas-parents (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/vertica-snippets-autoloads.el (100%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/vertica-snippets-pkg.el (83%) rename elpa/{vertica-snippets-20191205.1016 => vertica-snippets-20191213.1109}/vertica-snippets.el (100%) diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/.dir-locals.el b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/.dir-locals.el similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/.dir-locals.el rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/.dir-locals.el diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/busy-hours b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/busy-hours similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/busy-hours rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/busy-hours diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/check_rle_overridden b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/check_rle_overridden similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/check_rle_overridden rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/check_rle_overridden diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/config-changes b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/config-changes similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/config-changes rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/config-changes diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/current_epoch b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/current_epoch similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/current_epoch rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/current_epoch diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/errors b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/errors similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/errors rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/errors diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/external_table_details b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/external_table_details similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/external_table_details rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/external_table_details diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/format_numbers b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/format_numbers similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/format_numbers rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/format_numbers diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/get-partitions b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/get-partitions similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/get-partitions rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/get-partitions diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/get-projections b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/get-projections similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/get-projections rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/get-projections diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/grants b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/grants similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/grants rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/grants diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/last-statement-timing b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-statement-timing similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/last-statement-timing rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-statement-timing diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-vertica-updates b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-vertica-updates new file mode 100644 index 00000000..1246c545 --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/last-vertica-updates @@ -0,0 +1,12 @@ +# -*- mode: snippet -*- +# name: last-vertica-updates +# key: lvu_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# expand-env: ((yas/indent-line 'auto) (yas/wrap-around-region 't)) +# -- +SELECT time, + new_version + FROM dc_upgrades + LIMIT 1 OVER (PARTITION BY new_version + ORDER BY TIME DESC); \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/licence-history b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/licence-history similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/licence-history rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/licence-history diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/list-backups b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/list-backups similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/list-backups rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/list-backups diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/list-tables-row-count-size b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/list-tables-row-count-size similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/list-tables-row-count-size rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/list-tables-row-count-size diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/nodes b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/nodes similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/nodes rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/nodes diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/pool_queues b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/pool_queues similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/pool_queues rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/pool_queues diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/projection_storage b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/projection_storage similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/projection_storage rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/projection_storage diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/pset_footer b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/pset_footer similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/pset_footer rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/pset_footer diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-acquisition-queue b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-acquisition-queue similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-acquisition-queue rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-acquisition-queue diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-event-issues b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-event-issues similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-event-issues rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-event-issues diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-long-acquisitions b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-long-acquisitions similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-long-acquisitions rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-long-acquisitions diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-requests b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-requests rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-expensive b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-expensive new file mode 100644 index 00000000..f78f3bde --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-expensive @@ -0,0 +1,41 @@ +# -*- mode: snippet -*- +# name: query-requests-expensive +# key: qre_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# +# expensive queries, based on threads, memory, duration. +# Also includes the last transaction_id to find the query. +# and the range of the times it's ran from and to. +# targets just JOIN and GROUP BY SPILL events +# +# -- +SELECT REGEXP_REPLACE(REGEXP_REPLACE(substr(qr.request, 1, 500), '\d'), '\''[\S\s]?\''', '''?''' ) AS query, + MAX(qr.transaction_id) AS last_trans_id, + AVG(qr.request_duration_ms)/1000/60 AS avg_duration_mins, + AVG(ra.thread_count) AS avg_threads, + TO_CHAR(AVG(memory_inuse_kb)/1024, '999,999') AS avg_mem_mb, + (SUM(qr.request_duration_ms) * COUNT(1))/1000 AS total_duration_secs, + MAX(eee.event_type) AS event_type, + TRUNC(MIN(qr.start_timestamp), 'MI') AS first_run, + TRUNC(MAX(qr.start_timestamp), 'MI') AS last_run, + (SUM(qr.request_duration_ms) * COUNT(1)) * SUM(ra.thread_count) AS total_cost, + COUNT(1) AS num_runs + FROM v_monitor.query_requests qr + JOIN (SELECT transaction_id, statement_id, event_type + FROM v_internal.dc_execution_engine_events + WHERE node_name = (SELECT MIN(node_name) + FROM v_catalog.nodes) + ) eee USING (transaction_id, statement_id) + LEFT JOIN (SELECT transaction_id, statement_id, thread_count, memory_inuse_kb, duration_ms + FROM v_monitor.resource_acquisitions + WHERE node_name = (SELECT MIN(node_name) + FROM v_catalog.nodes) + ) ra USING (transaction_id, statement_id) + WHERE start_timestamp::date > CURRENT_DATE() -1 + AND NOT qr.is_executing + AND eee.event_type IN ('GROUP_BY_SPILLED', 'JOIN_SPILLED') + GROUP BY 1 +HAVING COUNT(*) > 3 + ORDER BY total_cost DESC + LIMIT 40;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-requests-overview b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-overview similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-requests-overview rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-requests-overview diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-times b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-times similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/query-times rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/query-times diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_projection_status b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_projection_status new file mode 100644 index 00000000..f188a37d --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_projection_status @@ -0,0 +1,19 @@ +# -*- mode: snippet -*- +# name: rebalance_projection_status +# key: rps_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# -- +SELECT projection_name, rebalance_method, separated_percent, transferred_percent + FROM v_monitor.rebalance_projection_status + WHERE rebalance_method = 'ELASTIC_CLUSTER' + AND ( + separated_percent <> 0 + AND separated_percent <> 100 + ) + OR ( + transferred_percent <> 0 + AND transferred_percent <> 100 + ) + AND is_latest + ORDER BY 1;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/rebalance_status b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_status similarity index 91% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/rebalance_status rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_status index 9c014ca2..72447e21 100644 --- a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/rebalance_status +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_status @@ -1,6 +1,6 @@ # -*- mode: snippet -*- # name: rebalance_status -# key: rebalance_status_ +# key: rs_ # group: vertica # condition: (if (string-equal sql-product 'vertica)t) # -- @@ -18,7 +18,7 @@ FROM AND transferred_percent <> 100) THEN 'In Progress' ELSE 'Queued' END AS status - FROM rebalance_projection_status + FROM v_monitor.rebalance_projection_status WHERE is_latest) AS tab GROUP BY 1, 2 diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_times b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_times new file mode 100644 index 00000000..2ca131a0 --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/rebalance_times @@ -0,0 +1,14 @@ +# -*- mode: snippet -*- +# name: rebalance_times +# key: rt_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# -- +SELECT dc_rebalanced_projections.node_name, + projection_schema, + projection_name, + start_time, + time -start_time duration + FROM v_internal.dc_rebalanced_projections + ORDER BY 5 DESC + LIMIT 100;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource-pool-move b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource-pool-move similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource-pool-move rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource-pool-move diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource_pool_status b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource_pool_status rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status2 b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status2 new file mode 100644 index 00000000..4d56047c --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pool_status2 @@ -0,0 +1,14 @@ +# -*- mode: snippet -*- +# name: resource_pool_status2 +# key: rps2_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# -- +SELECT sysdate AS CURRENT_TIME, + node_name, + pool_name, + TO_CHAR( memory_inuse_kb / 1024, '999g999g999g999') AS memory_inuse_mb, + TO_CHAR( general_memory_borrowed_kb / 1024, '999g999g999g999') AS general_memory_borrowed_mb, + running_query_count + FROM resource_pool_status +ORDER BY 1,3,2;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource_pools b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pools similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resource_pools rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resource_pools diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resources b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resources similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/resources rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/resources diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/ros_count b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/ros_count similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/ros_count rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/ros_count diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands1 b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands1 new file mode 100644 index 00000000..0de93a36 --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands1 @@ -0,0 +1,15 @@ +# -*- mode: snippet -*- +# name: segmentation-islands1 +# key: si1_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# +# https://www.vertica.com/blog/beware-segmentation-islands/ +# +# -- +SELECT REGEXP_REPLACE(SUBSTR(segment_expression, 1, 500), '\w+\.'), + COUNT(*) + FROM projections + WHERE projection_name NOT ILIKE '%b1' + GROUP BY 1 + ORDER BY 2 DESC;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands2 b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands2 new file mode 100644 index 00000000..c165e1c2 --- /dev/null +++ b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/segmentation-islands2 @@ -0,0 +1,30 @@ +# -*- mode: snippet -*- +# name: segmentation-islands2 +# key: si2_ +# group: vertica +# condition: (if (string-equal sql-product 'vertica)t) +# +# https://www.vertica.com/blog/beware-segmentation-islands/ +# +# -- +SELECT projection_schema || '.' || p.projection_name, REGEXP_REPLACE(SUBSTR(p.segment_expression, 1, 500), '\w+\.'), + pc.projection_column_name + FROM projections p + JOIN projection_columns pc USING (projection_id) + WHERE REGEXP_REPLACE(SUBSTR(p.segment_expression, 1, 500), '\w+\.') + IN (SELECT REGEXP_REPLACE(SUBSTR (p.segment_expression, 1, 500), '\w+\.') + FROM projections p + WHERE p.projection_name NOT ILIKE '%b1' + AND is_segmented + GROUP BY 1 + HAVING COUNT(*) = 1 ) + AND p.projection_name NOT ILIKE '%b1' + AND pc.projection_column_name IN ( + SELECT hash_expr FROM (SELECT replace(substr(regexp_replace(substr(p.segment_expression, 1, 500), '\w+\.'), 6), ')', '') AS hash_expr, COUNT(*) + FROM projections p + WHERE p.projection_name NOT ILIKE '%b1' + AND is_segmented + GROUP BY 1 + ORDER BY 2 DESC + LIMIT 20 ) AS foo ) + ORDER BY 1, 2, 3;$0 \ No newline at end of file diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/sessions b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/sessions similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/sessions rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/sessions diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/storage-containers b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/storage-containers similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/storage-containers rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/storage-containers diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/storage-locations b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/storage-locations similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/storage-locations rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/storage-locations diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/table_locks b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/table_locks similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/table_locks rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/table_locks diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/user_sessions b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/user_sessions similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/user_sessions rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/user_sessions diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/vertica_users b/elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/vertica_users similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-interactive-mode/vertica_users rename to elpa/vertica-snippets-20191213.1109/snippets/sql-interactive-mode/vertica_users diff --git a/elpa/vertica-snippets-20191205.1016/snippets/sql-mode/.yas-parents b/elpa/vertica-snippets-20191213.1109/snippets/sql-mode/.yas-parents similarity index 100% rename from elpa/vertica-snippets-20191205.1016/snippets/sql-mode/.yas-parents rename to elpa/vertica-snippets-20191213.1109/snippets/sql-mode/.yas-parents diff --git a/elpa/vertica-snippets-20191205.1016/vertica-snippets-autoloads.el b/elpa/vertica-snippets-20191213.1109/vertica-snippets-autoloads.el similarity index 100% rename from elpa/vertica-snippets-20191205.1016/vertica-snippets-autoloads.el rename to elpa/vertica-snippets-20191213.1109/vertica-snippets-autoloads.el diff --git a/elpa/vertica-snippets-20191205.1016/vertica-snippets-pkg.el b/elpa/vertica-snippets-20191213.1109/vertica-snippets-pkg.el similarity index 83% rename from elpa/vertica-snippets-20191205.1016/vertica-snippets-pkg.el rename to elpa/vertica-snippets-20191213.1109/vertica-snippets-pkg.el index 0a713c75..99e0b887 100644 --- a/elpa/vertica-snippets-20191205.1016/vertica-snippets-pkg.el +++ b/elpa/vertica-snippets-20191213.1109/vertica-snippets-pkg.el @@ -1,4 +1,4 @@ -(define-package "vertica-snippets" "20191205.1016" "Yasnippets for Vertica" +(define-package "vertica-snippets" "20191213.1109" "Yasnippets for Vertica" '((yasnippet "0.6.1")) :keywords '("convenience" "snippets") diff --git a/elpa/vertica-snippets-20191205.1016/vertica-snippets.el b/elpa/vertica-snippets-20191213.1109/vertica-snippets.el similarity index 100% rename from elpa/vertica-snippets-20191205.1016/vertica-snippets.el rename to elpa/vertica-snippets-20191213.1109/vertica-snippets.el -- 2.47.3