1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4 5-- This file defines DDL functions for adding and manipulating hypertables. 6 7-- Converts a regular postgres table to a hypertable. 8-- 9-- relation - The OID of the table to be converted 10-- time_column_name - Name of the column that contains time for a given record 11-- partitioning_column - Name of the column to partition data by 12-- number_partitions - (Optional) Number of partitions for data 13-- associated_schema_name - (Optional) Schema for internal hypertable tables 14-- associated_table_prefix - (Optional) Prefix for internal hypertable table names 15-- chunk_time_interval - (Optional) Initial time interval for a chunk 16-- create_default_indexes - (Optional) Whether or not to create the default indexes 17-- if_not_exists - (Optional) Do not fail if table is already a hypertable 18-- partitioning_func - (Optional) The partitioning function to use for spatial partitioning 19-- migrate_data - (Optional) Set to true to migrate any existing data in the table to chunks 20-- chunk_target_size - (Optional) The target size for chunks (e.g., '1000MB', 'estimate', or 'off') 21-- chunk_sizing_func - (Optional) A function to calculate the chunk time interval for new chunks 22-- time_partitioning_func - (Optional) The partitioning function to use for "time" partitioning 23-- replication_factor - (Optional) A value of 1 or greater makes this hypertable distributed 24-- data_nodes - (Optional) The specific data nodes to distribute this hypertable across 25CREATE OR REPLACE FUNCTION create_hypertable( 26 relation REGCLASS, 27 time_column_name NAME, 28 partitioning_column NAME = NULL, 29 number_partitions INTEGER = NULL, 30 associated_schema_name NAME = NULL, 31 associated_table_prefix NAME = NULL, 32 chunk_time_interval ANYELEMENT = NULL::bigint, 33 create_default_indexes BOOLEAN = TRUE, 34 if_not_exists BOOLEAN = FALSE, 35 partitioning_func REGPROC = NULL, 36 migrate_data BOOLEAN = FALSE, 37 chunk_target_size TEXT = NULL, 38 chunk_sizing_func REGPROC = '_timescaledb_internal.calculate_chunk_interval'::regproc, 39 time_partitioning_func REGPROC = NULL, 40 replication_factor INTEGER = NULL, 41 data_nodes NAME[] = NULL 42) RETURNS TABLE(hypertable_id INT, schema_name NAME, table_name NAME, created BOOL) AS '@MODULE_PATHNAME@', 'ts_hypertable_create' LANGUAGE C VOLATILE; 43 44-- Same functionality as create_hypertable, only must have a replication factor > 0 (defaults to 1) 45CREATE OR REPLACE FUNCTION create_distributed_hypertable( 46 relation REGCLASS, 47 time_column_name NAME, 48 partitioning_column NAME = NULL, 49 number_partitions INTEGER = NULL, 50 associated_schema_name NAME = NULL, 51 associated_table_prefix NAME = NULL, 52 chunk_time_interval ANYELEMENT = NULL::bigint, 53 create_default_indexes BOOLEAN = TRUE, 54 if_not_exists BOOLEAN = FALSE, 55 partitioning_func REGPROC = NULL, 56 migrate_data BOOLEAN = FALSE, 57 chunk_target_size TEXT = NULL, 58 chunk_sizing_func REGPROC = '_timescaledb_internal.calculate_chunk_interval'::regproc, 59 time_partitioning_func REGPROC = NULL, 60 replication_factor INTEGER = 1, 61 data_nodes NAME[] = NULL 62) RETURNS TABLE(hypertable_id INT, schema_name NAME, table_name NAME, created BOOL) AS '@MODULE_PATHNAME@', 'ts_hypertable_distributed_create' LANGUAGE C VOLATILE; 63 64-- Set adaptive chunking. To disable, set chunk_target_size => 'off'. 65CREATE OR REPLACE FUNCTION set_adaptive_chunking( 66 hypertable REGCLASS, 67 chunk_target_size TEXT, 68 INOUT chunk_sizing_func REGPROC = '_timescaledb_internal.calculate_chunk_interval'::regproc, 69 OUT chunk_target_size BIGINT 70) RETURNS RECORD AS '@MODULE_PATHNAME@', 'ts_chunk_adaptive_set' LANGUAGE C VOLATILE; 71 72-- Update chunk_time_interval for a hypertable. 73-- 74-- hypertable - The OID of the table corresponding to a hypertable whose time 75-- interval should be updated 76-- chunk_time_interval - The new time interval. For hypertables with integral 77-- time columns, this must be an integral type. For hypertables with a 78-- TIMESTAMP/TIMESTAMPTZ/DATE type, it can be integral which is treated as 79-- microseconds, or an INTERVAL type. 80CREATE OR REPLACE FUNCTION set_chunk_time_interval( 81 hypertable REGCLASS, 82 chunk_time_interval ANYELEMENT, 83 dimension_name NAME = NULL 84) RETURNS VOID AS '@MODULE_PATHNAME@', 'ts_dimension_set_interval' LANGUAGE C VOLATILE; 85 86CREATE OR REPLACE FUNCTION set_number_partitions( 87 hypertable REGCLASS, 88 number_partitions INTEGER, 89 dimension_name NAME = NULL 90) RETURNS VOID AS '@MODULE_PATHNAME@', 'ts_dimension_set_num_slices' LANGUAGE C VOLATILE; 91 92-- Drop chunks older than the given timestamp for the specific 93-- hypertable or continuous aggregate. 94CREATE OR REPLACE FUNCTION drop_chunks( 95 relation REGCLASS, 96 older_than "any" = NULL, 97 newer_than "any" = NULL, 98 verbose BOOLEAN = FALSE 99) RETURNS SETOF TEXT AS '@MODULE_PATHNAME@', 'ts_chunk_drop_chunks' 100LANGUAGE C VOLATILE PARALLEL UNSAFE; 101 102-- show chunks older than or newer than a specific time. 103-- `relation` must be a valid hypertable or continuous aggregate. 104CREATE OR REPLACE FUNCTION show_chunks( 105 relation REGCLASS, 106 older_than "any" = NULL, 107 newer_than "any" = NULL 108) RETURNS SETOF REGCLASS AS '@MODULE_PATHNAME@', 'ts_chunk_show_chunks' 109LANGUAGE C STABLE PARALLEL SAFE; 110 111-- Add a dimension (of partitioning) to a hypertable 112-- 113-- hypertable - OID of the table to add a dimension to 114-- column_name - NAME of the column to use in partitioning for this dimension 115-- number_partitions - Number of partitions, for non-time dimensions 116-- interval_length - Size of intervals for time dimensions (can be integral or INTERVAL) 117-- partitioning_func - Function used to partition the column 118-- if_not_exists - If set, and the dimension already exists, generate a notice instead of an error 119CREATE OR REPLACE FUNCTION add_dimension( 120 hypertable REGCLASS, 121 column_name NAME, 122 number_partitions INTEGER = NULL, 123 chunk_time_interval ANYELEMENT = NULL::BIGINT, 124 partitioning_func REGPROC = NULL, 125 if_not_exists BOOLEAN = FALSE 126) RETURNS TABLE(dimension_id INT, schema_name NAME, table_name NAME, column_name NAME, created BOOL) 127AS '@MODULE_PATHNAME@', 'ts_dimension_add' LANGUAGE C VOLATILE; 128 129CREATE OR REPLACE FUNCTION attach_tablespace( 130 tablespace NAME, 131 hypertable REGCLASS, 132 if_not_attached BOOLEAN = false 133) RETURNS VOID 134AS '@MODULE_PATHNAME@', 'ts_tablespace_attach' LANGUAGE C VOLATILE; 135 136CREATE OR REPLACE FUNCTION detach_tablespace( 137 tablespace NAME, 138 hypertable REGCLASS = NULL, 139 if_attached BOOLEAN = false 140) RETURNS INTEGER 141AS '@MODULE_PATHNAME@', 'ts_tablespace_detach' LANGUAGE C VOLATILE; 142 143CREATE OR REPLACE FUNCTION detach_tablespaces(hypertable REGCLASS) RETURNS INTEGER 144AS '@MODULE_PATHNAME@', 'ts_tablespace_detach_all_from_hypertable' LANGUAGE C VOLATILE; 145 146CREATE OR REPLACE FUNCTION show_tablespaces(hypertable REGCLASS) RETURNS SETOF NAME 147AS '@MODULE_PATHNAME@', 'ts_tablespace_show' LANGUAGE C VOLATILE STRICT; 148 149-- Add a data node to a TimescaleDB distributed database. 150CREATE OR REPLACE FUNCTION add_data_node( 151 node_name NAME, 152 host TEXT, 153 database NAME = NULL, 154 port INTEGER = NULL, 155 if_not_exists BOOLEAN = FALSE, 156 bootstrap BOOLEAN = TRUE, 157 password TEXT = NULL 158) RETURNS TABLE(node_name NAME, host TEXT, port INTEGER, database NAME, 159 node_created BOOL, database_created BOOL, extension_created BOOL) 160AS '@MODULE_PATHNAME@', 'ts_data_node_add' LANGUAGE C VOLATILE; 161 162-- Delete a data node from a distributed database 163CREATE OR REPLACE FUNCTION delete_data_node( 164 node_name NAME, 165 if_exists BOOLEAN = FALSE, 166 force BOOLEAN = FALSE, 167 repartition BOOLEAN = TRUE 168) RETURNS BOOLEAN AS '@MODULE_PATHNAME@', 'ts_data_node_delete' LANGUAGE C VOLATILE; 169 170-- Attach a data node to a distributed hypertable 171CREATE OR REPLACE FUNCTION attach_data_node( 172 node_name NAME, 173 hypertable REGCLASS, 174 if_not_attached BOOLEAN = FALSE, 175 repartition BOOLEAN = TRUE 176) RETURNS TABLE(hypertable_id INTEGER, node_hypertable_id INTEGER, node_name NAME) 177AS '@MODULE_PATHNAME@', 'ts_data_node_attach' LANGUAGE C VOLATILE; 178 179-- Detach a data node from a distributed hypertable. NULL hypertable means it will detach from all distributed hypertables 180CREATE OR REPLACE FUNCTION detach_data_node( 181 node_name NAME, 182 hypertable REGCLASS = NULL, 183 if_attached BOOLEAN = FALSE, 184 force BOOLEAN = FALSE, 185 repartition BOOLEAN = TRUE 186) RETURNS INTEGER 187AS '@MODULE_PATHNAME@', 'ts_data_node_detach' LANGUAGE C VOLATILE; 188 189-- Execute query on a specified list of data nodes. By default node_list is NULL, which means 190-- to execute the query on every data node 191CREATE OR REPLACE PROCEDURE distributed_exec( 192 query TEXT, 193 node_list name[] = NULL, 194 transactional BOOLEAN = TRUE) 195AS '@MODULE_PATHNAME@', 'ts_distributed_exec' LANGUAGE C; 196 197-- Execute pg_create_restore_point() on each data node 198CREATE OR REPLACE FUNCTION create_distributed_restore_point( 199 name TEXT 200) RETURNS TABLE(node_name NAME, node_type TEXT, restore_point pg_lsn) 201AS '@MODULE_PATHNAME@', 'ts_create_distributed_restore_point' LANGUAGE C VOLATILE STRICT; 202 203-- Sets new replication factor for distributed hypertable 204CREATE OR REPLACE FUNCTION set_replication_factor( 205 hypertable REGCLASS, 206 replication_factor INTEGER 207) RETURNS VOID 208AS '@MODULE_PATHNAME@', 'ts_hypertable_distributed_set_replication_factor' LANGUAGE C VOLATILE; 209 210-- Refresh a continuous aggregate across the given window. 211CREATE OR REPLACE PROCEDURE refresh_continuous_aggregate( 212 continuous_aggregate REGCLASS, 213 window_start "any", 214 window_end "any" 215) LANGUAGE C AS '@MODULE_PATHNAME@', 'ts_continuous_agg_refresh'; 216