-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION _timescaledb_internal.test_status(int) RETURNS JSONB
    AS :MODULE_PATHNAME, 'ts_test_status' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION _timescaledb_internal.test_status_ssl(int) RETURNS JSONB
    AS :MODULE_PATHNAME, 'ts_test_status_ssl' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
CREATE OR REPLACE FUNCTION _timescaledb_internal.test_status_mock(text) RETURNS JSONB

            

Reported by SQLint.

              INSERT INTO _timescaledb_catalog.metadata VALUES ('foo','bar',TRUE);
INSERT INTO _timescaledb_catalog.metadata VALUES ('bar','baz',FALSE);

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
SELECT _timescaledb_internal.test_status_ssl(200);
SELECT _timescaledb_internal.test_status_ssl(201);
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.test_status_ssl(304);
SELECT _timescaledb_internal.test_status_ssl(400);

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
SELECT _timescaledb_internal.test_status_ssl(200);
SELECT _timescaledb_internal.test_status_ssl(201);
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.test_status_ssl(304);
SELECT _timescaledb_internal.test_status_ssl(400);
SELECT _timescaledb_internal.test_status_ssl(401);
SELECT _timescaledb_internal.test_status_ssl(404);
SELECT _timescaledb_internal.test_status_ssl(500);

            

Reported by SQLint.

              SELECT _timescaledb_internal.test_status_ssl(404);
SELECT _timescaledb_internal.test_status_ssl(500);
SELECT _timescaledb_internal.test_status_ssl(503);
\set ON_ERROR_STOP 1
SELECT _timescaledb_internal.test_status(200);
SELECT _timescaledb_internal.test_status(201);
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.test_status(304);
SELECT _timescaledb_internal.test_status(400);

            

Reported by SQLint.

              \set ON_ERROR_STOP 1
SELECT _timescaledb_internal.test_status(200);
SELECT _timescaledb_internal.test_status(201);
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.test_status(304);
SELECT _timescaledb_internal.test_status(400);
SELECT _timescaledb_internal.test_status(401);
SELECT _timescaledb_internal.test_status(404);
SELECT _timescaledb_internal.test_status(500);

            

Reported by SQLint.

              SELECT _timescaledb_internal.test_status(404);
SELECT _timescaledb_internal.test_status(500);
SELECT _timescaledb_internal.test_status(503);
\set ON_ERROR_STOP 1

-- This function runs the test 5 times, because each time the internal C function is choosing a random length to send from the server on each socket read. We hit many cases this way.
CREATE OR REPLACE FUNCTION mocker(TEXT) RETURNS SETOF TEXT AS
$BODY$
DECLARE

            

Reported by SQLint.

              DECLARE
r TEXT;
BEGIN
FOR i in 1..5 LOOP
SELECT _timescaledb_internal.test_status_mock($1) INTO r;
RETURN NEXT r;
END LOOP;
RETURN;
END

            

Reported by SQLint.

              BEGIN
FOR i in 1..5 LOOP
SELECT _timescaledb_internal.test_status_mock($1) INTO r;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

            

Reported by SQLint.

              FOR i in 1..5 LOOP
SELECT _timescaledb_internal.test_status_mock($1) INTO r;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql';


            

Reported by SQLint.

              SELECT _timescaledb_internal.test_status_mock($1) INTO r;
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

select * from mocker(

            

Reported by SQLint.

              
# Python script to check if there are memory spikes when running
# out of order random inserts to TimescaleDB database
import psutil
import time
import sys

DEFAULT_MEMCAP = 300 # in MB
THRESHOLD_RATIO = 1.5 # ratio above which considered memory spike

            

Reported by Pylint.

                  time.sleep(60)

    # Calculate average memory consumption from 5 values over 15 seconds
    sum = 0
    for i in range (0, 5):
        sum += p.memory_info().rss
        time.sleep(3)
    avg = sum / 5
    print('Average memory consumption: ', bytes2human(avg))

            

Reported by Pylint.

              
    # Calculate average memory consumption from 5 values over 15 seconds
    sum = 0
    for i in range (0, 5):
        sum += p.memory_info().rss
        time.sleep(3)
    avg = sum / 5
    print('Average memory consumption: ', bytes2human(avg))


            

Reported by Pylint.

              # Copyright (c) 2016-2018  Timescale, Inc. All Rights Reserved.
#
# This file is licensed under the Apache License, see LICENSE-APACHE
# at the top level directory of the timescaledb distribution.

# Python script to check if there are memory spikes when running
# out of order random inserts to TimescaleDB database
import psutil
import time

            

Reported by Pylint.

              # Python script to check if there are memory spikes when running
# out of order random inserts to TimescaleDB database
import psutil
import time
import sys

DEFAULT_MEMCAP = 300 # in MB
THRESHOLD_RATIO = 1.5 # ratio above which considered memory spike


            

Reported by Pylint.

              # out of order random inserts to TimescaleDB database
import psutil
import time
import sys

DEFAULT_MEMCAP = 300 # in MB
THRESHOLD_RATIO = 1.5 # ratio above which considered memory spike

# finds processes with name as argument

            

Reported by Pylint.

              THRESHOLD_RATIO = 1.5 # ratio above which considered memory spike

# finds processes with name as argument
def find_procs_by_name(name):
    # Return a list of processes matching 'name'
    ls = []
    for p in psutil.process_iter():
        if p.name() == name:
            ls.append(p)

            

Reported by Pylint.

              # finds processes with name as argument
def find_procs_by_name(name):
    # Return a list of processes matching 'name'
    ls = []
    for p in psutil.process_iter():
        if p.name() == name:
            ls.append(p)
    return ls


            

Reported by Pylint.

              def find_procs_by_name(name):
    # Return a list of processes matching 'name'
    ls = []
    for p in psutil.process_iter():
        if p.name() == name:
            ls.append(p)
    return ls

# return human readable form of number of bytes n

            

Reported by Pylint.

                  return ls

# return human readable form of number of bytes n
def bytes2human(n):
    # http://code.activestate.com/recipes/578019
    # >>> bytes2human(10000)
    # '9.8K'
    # >>> bytes2human(100001221)
    # '95.4M'

            

Reported by Pylint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.

\d+ _timescaledb_internal.*

CREATE OR REPLACE FUNCTION timescaledb_integrity_test()
    RETURNS VOID LANGUAGE PLPGSQL STABLE AS
$BODY$
DECLARE

            

Reported by SQLint.

              $BODY$
DECLARE
    dimension_slice RECORD;
    constraint_row RECORD;
    index_row      RECORD;
    chunk_count    INTEGER;
    chunk_constraint_count INTEGER;
    chunk_index_count      INTEGER;
BEGIN

            

Reported by SQLint.

              DECLARE
    dimension_slice RECORD;
    constraint_row RECORD;
    index_row      RECORD;
    chunk_count    INTEGER;
    chunk_constraint_count INTEGER;
    chunk_index_count      INTEGER;
BEGIN
    -- Check integrity of chunk indexes on non-distributed hypertables

            

Reported by SQLint.

                  dimension_slice RECORD;
    constraint_row RECORD;
    index_row      RECORD;
    chunk_count    INTEGER;
    chunk_constraint_count INTEGER;
    chunk_index_count      INTEGER;
BEGIN
    -- Check integrity of chunk indexes on non-distributed hypertables
    -- (distributed ones do not have chunk indexes on the access node)

            

Reported by SQLint.

                  constraint_row RECORD;
    index_row      RECORD;
    chunk_count    INTEGER;
    chunk_constraint_count INTEGER;
    chunk_index_count      INTEGER;
BEGIN
    -- Check integrity of chunk indexes on non-distributed hypertables
    -- (distributed ones do not have chunk indexes on the access node)
    FOR index_row IN

            

Reported by SQLint.

                  index_row      RECORD;
    chunk_count    INTEGER;
    chunk_constraint_count INTEGER;
    chunk_index_count      INTEGER;
BEGIN
    -- Check integrity of chunk indexes on non-distributed hypertables
    -- (distributed ones do not have chunk indexes on the access node)
    FOR index_row IN
    SELECT h.schema_name, c.relname AS index_name, h.id AS hypertable_id, h.table_name AS hypertable_name

            

Reported by SQLint.

              BEGIN
    -- Check integrity of chunk indexes on non-distributed hypertables
    -- (distributed ones do not have chunk indexes on the access node)
    FOR index_row IN
    SELECT h.schema_name, c.relname AS index_name, h.id AS hypertable_id, h.table_name AS hypertable_name
    FROM _timescaledb_catalog.hypertable h
    INNER JOIN pg_index i ON (i.indrelid = format('%I.%I', h.schema_name, h.table_name)::regclass)
    INNER JOIN pg_class c ON (i.indexrelid = c.oid)
    WHERE h.replication_factor IS NULL

            

Reported by SQLint.

                      SELECT count(c.*) FROM _timescaledb_catalog.chunk_index c
        WHERE c.hypertable_id = index_row.hypertable_id
        AND c.hypertable_index_name = index_row.index_name
        INTO STRICT chunk_index_count;

        IF chunk_index_count != chunk_count THEN
           RAISE EXCEPTION 'Missing chunk indexes. Expected %, but found %', chunk_count, chunk_index_count;
        END IF;
    END LOOP;

            

Reported by SQLint.

                      AND c.hypertable_index_name = index_row.index_name
        INTO STRICT chunk_index_count;

        IF chunk_index_count != chunk_count THEN
           RAISE EXCEPTION 'Missing chunk indexes. Expected %, but found %', chunk_count, chunk_index_count;
        END IF;
    END LOOP;

    -- Check integrity of chunk_constraints

            

Reported by SQLint.

              
        IF chunk_index_count != chunk_count THEN
           RAISE EXCEPTION 'Missing chunk indexes. Expected %, but found %', chunk_count, chunk_index_count;
        END IF;
    END LOOP;

    -- Check integrity of chunk_constraints
    FOR constraint_row IN
    SELECT c.conname, h.id AS hypertable_id FROM _timescaledb_catalog.hypertable h INNER JOIN

            

Reported by SQLint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.

\c :TEST_DBNAME :ROLE_SUPERUSER
\set DN_DBNAME_1 :TEST_DBNAME _1
\set DN_DBNAME_2 :TEST_DBNAME _2

\ir include/remote_exec.sql
GRANT CREATE ON DATABASE :"TEST_DBNAME" TO :ROLE_DEFAULT_PERM_USER;

            

Reported by SQLint.

              -- return the existing chunk
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi',' {"time": [1514419200000000, 1515024000000000], "device": [-9223372036854775808, 1073741823]}');

\set VERBOSITY default
\set ON_ERROR_STOP 0
-- Modified time constraint should fail with collision
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi',' {"time": [1514419600000000, 1515024000000000], "device": [-9223372036854775808, 1073741823]}');
-- Missing dimension
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi',' {"time": [1514419600000000, 1515024000000000]}');

            

Reported by SQLint.

              -- Valid chunk, but no permissions
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi',' {"time": [1515024000000000, 1519024000000000], "device": [-9223372036854775808, 1073741823]}', 'ChunkSchema', 'My_chunk_Table_name');
\set ON_ERROR_STOP 1

SET ROLE :ROLE_DEFAULT_PERM_USER;
-- Test create_chunk_table for errors
\set ON_ERROR_STOP 0
-- Test create_chunk_table for NULL input

            

Reported by SQLint.

              
SET ROLE :ROLE_DEFAULT_PERM_USER;
-- Test create_chunk_table for errors
\set ON_ERROR_STOP 0
-- Test create_chunk_table for NULL input
SELECT * FROM _timescaledb_internal.create_chunk_table(NULL,' {"time": [1515024000000000, 1519024000000000], "device": [-9223372036854775808, 1073741823]}', '_timescaledb_internal','_hyper_1_1_chunk');
SELECT * FROM _timescaledb_internal.create_chunk_table('chunkapi', NULL, '_timescaledb_internal','_hyper_1_1_chunk');
SELECT * FROM _timescaledb_internal.create_chunk_table('chunkapi',' {"time": [1515024000000000, 1519024000000000], "device": [-9223372036854775808, 1073741823]}', NULL,'_hyper_1_1_chunk');
SELECT * FROM _timescaledb_internal.create_chunk_table('chunkapi',' {"time": [1515024000000000, 1519024000000000], "device": [-9223372036854775808, 1073741823]}', '_timescaledb_internal',NULL);

            

Reported by SQLint.

              -- Valid chunk, but no permissions
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
SELECT * FROM _timescaledb_internal.create_chunk_table('chunkapi',' {"time": [1515024000000000, 1519024000000000], "device": [-9223372036854775808, 1073741823]}', '_timescaledb_internal','_hyper_1_1_chunk');
\set ON_ERROR_STOP 1

-- Test that granting insert on tables allow create_chunk to be
-- called. This will also create a chunk that does not collide and has
-- a custom schema and name.
SET ROLE :ROLE_SUPERUSER;

            

Reported by SQLint.

              
SET ROLE :ROLE_DEFAULT_PERM_USER;

\set VERBOSITY terse

SELECT (_timescaledb_internal.show_chunk(show_chunks)).*
FROM show_chunks('chunkapi')
ORDER BY chunk_id;


            

Reported by SQLint.

              ORDER BY chunk_id;

-- Show the new chunks
\dt public.*
\dt "ChunkSchema".*

-- Make ANALYZE deterministic
SELECT setseed(1);


            

Reported by SQLint.

              SELECT chunk_schema AS "CHUNK_SCHEMA", chunk_name AS "CHUNK_NAME"
FROM timescaledb_information.chunks c
ORDER BY chunk_name DESC
LIMIT 1 \gset

SELECT slices AS "SLICES"
FROM _timescaledb_internal.show_chunk(:'CHUNK_SCHEMA'||'.'||:'CHUNK_NAME') \gset

SELECT relname

            

Reported by SQLint.

              WHERE inhrelid = (:'CHUNK_SCHEMA'||'.'||:'CHUNK_NAME')::regclass AND inhparent = oid;

-- Test that creat_chunk fails since chunk table already exists
\set ON_ERROR_STOP 0
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi', :'SLICES', :'CHUNK_SCHEMA', :'CHUNK_NAME');
\set ON_ERROR_STOP 1

-- Test create_chunk_table on a hypertable where the chunk didn't exist before


            

Reported by SQLint.

              -- Test that creat_chunk fails since chunk table already exists
\set ON_ERROR_STOP 0
SELECT * FROM _timescaledb_internal.create_chunk('chunkapi', :'SLICES', :'CHUNK_SCHEMA', :'CHUNK_NAME');
\set ON_ERROR_STOP 1

-- Test create_chunk_table on a hypertable where the chunk didn't exist before

DROP TABLE chunkapi;
DROP TABLE :CHUNK_SCHEMA.:CHUNK_NAME;

            

Reported by SQLint.

              -- LICENSE-TIMESCALE for a copy of the license.

-- initialize the bgw mock state to prevent the materialization workers from running
\c :TEST_DBNAME :ROLE_SUPERUSER

CREATE OR REPLACE FUNCTION ts_bgw_params_create() RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

CREATE OR REPLACE FUNCTION test.continuous_aggs_find_view(cagg REGCLASS) RETURNS VOID

            

Reported by SQLint.

              CREATE OR REPLACE FUNCTION test.continuous_aggs_find_view(cagg REGCLASS) RETURNS VOID
AS :TSL_MODULE_PATHNAME, 'ts_test_continuous_agg_find_by_view_name' LANGUAGE C;

\set WAIT_ON_JOB 0
\set IMMEDIATELY_SET_UNTIL 1
\set WAIT_FOR_OTHER_TO_ADVANCE 2

-- remove any default jobs, e.g., telemetry so bgw_job isn't polluted
DELETE FROM _timescaledb_config.bgw_job WHERE TRUE;

            

Reported by SQLint.

              group by time_bucket(1, a), a WITH NO DATA;

SELECT add_continuous_aggregate_policy('mat_m1', NULL, 2::integer, '12 h'::interval) AS job_id
\gset
SELECT * FROM _timescaledb_config.bgw_job;

SELECT ca.raw_hypertable_id as "RAW_HYPERTABLE_ID",
       h.schema_name AS "MAT_SCHEMA_NAME",
       h.table_name AS "MAT_TABLE_NAME",

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_m1'
\gset

insert into :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select a, _timescaledb_internal.partialize_agg(count(b)),
time_bucket(1, a)
,1

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_m1'
\gset

-- Materialized hypertable for mat_m1 should not be visible in the
-- hypertables view:
SELECT hypertable_schema, hypertable_name
FROM timescaledb_information.hypertables ORDER BY 1,2;

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_m1'
\gset

SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_m1'
\gset

SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_m1'
\gset

SET ROLE :ROLE_SUPERUSER;
insert into  :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME"
select
 time_bucket('1week', timec),  _timescaledb_internal.partialize_agg( min(location)), _timescaledb_internal.partialize_agg( sum(temperature)) , _timescaledb_internal.partialize_agg( sum(humidity)), _timescaledb_internal.partialize_agg(stddev(humidity))

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_naming'
\gset

select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_naming'
\gset

select attnum , attname from pg_attribute
where attnum > 0 and attrelid =
(Select oid from pg_class where relname like :'MAT_TABLE_NAME')
order by attnum, attname;

            

Reported by SQLint.

              
-- Set this variable to avoid using a hard-coded path each time query
-- results are compared
\set QUERY_RESULT_TEST_EQUAL_RELPATH 'include/query_result_test_equal.sql'

-- DROP a table's column before making it a hypertable
CREATE TABLE alter_before(id serial, time timestamp, temp float, colorid integer, notes text, notes_2 text);
ALTER TABLE alter_before DROP COLUMN id;
ALTER TABLE alter_before ALTER COLUMN temp SET (n_distinct = 10);

            

Reported by SQLint.

                                             ('2017-05-22T09:18:23', 15.2, 2);

-- Make sure tuple conversion also works with COPY
\COPY alter_after FROM 'data/alter.tsv' NULL AS '';

-- Data should look OK
SELECT * FROM alter_after;

-- Show that attnums are different for chunks created after DROP

            

Reported by SQLint.

              SELECT relname, reloptions FROM pg_class WHERE relname IN ('_hyper_2_3_chunk','_hyper_2_4_chunk');

-- Need superuser to ALTER chunks in _timescaledb_internal schema
\c :TEST_DBNAME :ROLE_SUPERUSER
SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;

-- Rename chunk
ALTER TABLE _timescaledb_internal._hyper_2_2_chunk RENAME TO new_chunk_name;
SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;

            

Reported by SQLint.

              SELECT * FROM _timescaledb_catalog.chunk WHERE id = 2;

-- Test that we cannot rename chunk columns
\set ON_ERROR_STOP 0
ALTER TABLE public.new_chunk_name RENAME COLUMN time TO newtime;
\set ON_ERROR_STOP 1

-- Test that we can set tablespace of a hypertable
\c :TEST_DBNAME :ROLE_SUPERUSER

            

Reported by SQLint.

              -- Test that we cannot rename chunk columns
\set ON_ERROR_STOP 0
ALTER TABLE public.new_chunk_name RENAME COLUMN time TO newtime;
\set ON_ERROR_STOP 1

-- Test that we can set tablespace of a hypertable
\c :TEST_DBNAME :ROLE_SUPERUSER
SET client_min_messages = ERROR;
DROP TABLESPACE IF EXISTS tablespace1;

            

Reported by SQLint.

              --test hypertable with tables space
CREATE TABLESPACE tablespace1 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE1_PATH;
CREATE TABLESPACE tablespace2 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE2_PATH;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- Test that we can directly change chunk tablespace
ALTER TABLE public.new_chunk_name SET TABLESPACE tablespace1;
SELECT tablespace FROM pg_tables WHERE tablename = 'new_chunk_name';


            

Reported by SQLint.

              -- should not fail (unlike attach_tablespace)
ALTER TABLE hyper_in_space SET TABLESPACE tablespace1;

\set ON_ERROR_STOP 0
-- not an empty tablespace
DROP TABLESPACE tablespace1;
\set ON_ERROR_STOP 1

-- show_chunks and drop_chunks output should be the same

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- not an empty tablespace
DROP TABLESPACE tablespace1;
\set ON_ERROR_STOP 1

-- show_chunks and drop_chunks output should be the same
\set QUERY1 'SELECT show_chunks(\'hyper_in_space\', 22)::NAME'
\set QUERY2 'SELECT drop_chunks(\'hyper_in_space\', 22)::NAME'
\set ECHO errors

            

Reported by SQLint.

              \set ECHO all
SELECT tablename, tablespace FROM pg_tables WHERE tablespace = 'tablespace1' ORDER BY tablename;

\set ON_ERROR_STOP 0
-- should not be able to drop tablespace if a hypertable depends on it
-- even when there are no chunks
DROP TABLESPACE tablespace1;
\set ON_ERROR_STOP 1


            

Reported by SQLint.

              -- should not be able to drop tablespace if a hypertable depends on it
-- even when there are no chunks
DROP TABLESPACE tablespace1;
\set ON_ERROR_STOP 1

DROP TABLE hyper_in_space;

CREATE TABLE hyper_in_space(time bigint, temp float, device int) TABLESPACE tablespace1;
SELECT create_hypertable('hyper_in_space', 'time', 'device', 4, chunk_time_interval=>1);

            

Reported by SQLint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.

\set ON_ERROR_STOP 0

CREATE VIEW hypertable_details AS
WITH
  names AS (SELECT * FROM pg_class cl JOIN pg_namespace ns ON ns.oid = relnamespace)
SELECT (SELECT format('%I.%I', ht.schema_name, ht.table_name) FROM names

            

Reported by SQLint.

              
select table_name from create_hypertable( 'conditions', 'timec', chunk_time_interval=>'1month'::interval);

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'timec');
insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 'klick', 55, 75;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 'klick', 55, 75;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
--now owner tries and succeeds --
alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'timec');
insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 'klick', 55, 75;


            

Reported by SQLint.

              select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 'klick', 55, 75;

--try modifying compress properties --
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'humidity');

--- compress_chunks and decompress_chunks fail without correct perm --
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
select  compress_chunk(ch1.schema_name|| '.' || ch1.table_name)

            

Reported by SQLint.

              alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'humidity');

--- compress_chunks and decompress_chunks fail without correct perm --
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
select  compress_chunk(ch1.schema_name|| '.' || ch1.table_name)
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions' and ch1.compressed_chunk_id IS NULL;
select decompress_chunk(ch1.schema_name|| '.' || ch1.table_name)
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions';
select add_compression_policy('conditions', '1day'::interval);

            

Reported by SQLint.

              FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions';
select add_compression_policy('conditions', '1day'::interval);

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
select add_compression_policy('conditions', '1day'::interval);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
--try dropping policy
select remove_compression_policy('conditions', true);


            

Reported by SQLint.

              
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
select add_compression_policy('conditions', '1day'::interval);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
--try dropping policy
select remove_compression_policy('conditions', true);

--Tests for GRANTS.
-- as owner grant select , compress chunk and check SELECT works

            

Reported by SQLint.

              
--Tests for GRANTS.
-- as owner grant select , compress chunk and check SELECT works
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
GRANT SELECT on conditions to :ROLE_DEFAULT_PERM_USER_2;
select count(*) from
(select  compress_chunk(ch1.schema_name|| '.' || ch1.table_name)
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions' and ch1.compressed_chunk_id IS NULL ) as subq;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2

            

Reported by SQLint.

              select count(*) from
(select  compress_chunk(ch1.schema_name|| '.' || ch1.table_name)
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions' and ch1.compressed_chunk_id IS NULL ) as subq;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
select count(*) from conditions;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
GRANT INSERT on conditions to :ROLE_DEFAULT_PERM_USER_2;


            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
select count(*) from conditions;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
GRANT INSERT on conditions to :ROLE_DEFAULT_PERM_USER_2;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
insert into conditions
select '2019-04-01 00:00+0'::timestamp with time zone, 'NYC', 'klick', 55, 75;

            

Reported by SQLint.

              
    def check_subject_imperative(self):
        'Rule 5: Use the imperative mood in the subject line.'
        'We can only check for common mistakes here, like using'
        'the -ing form of a verb or non-imperative version of '
        'common verbs'

        firstword = self.subject_words[0]


            

Reported by Pylint.

                  def check_subject_imperative(self):
        'Rule 5: Use the imperative mood in the subject line.'
        'We can only check for common mistakes here, like using'
        'the -ing form of a verb or non-imperative version of '
        'common verbs'

        firstword = self.subject_words[0]

        if firstword.endswith('ing'):

            

Reported by Pylint.

                      'Rule 5: Use the imperative mood in the subject line.'
        'We can only check for common mistakes here, like using'
        'the -ing form of a verb or non-imperative version of '
        'common verbs'

        firstword = self.subject_words[0]

        if firstword.endswith('ing'):
            return False

            

Reported by Pylint.

                              num_violations += 1

        if num_violations > 0:
            print
            print("**** WARNING ****")
            print
            print("The commit message does not seem to comply with the project's guidelines.")
            print("Please try to follow the \"Seven rules of a great commit message\":")
            print("https://chris.beams.io/posts/git-commit/")

            

Reported by Pylint.

                      if num_violations > 0:
            print
            print("**** WARNING ****")
            print
            print("The commit message does not seem to comply with the project's guidelines.")
            print("Please try to follow the \"Seven rules of a great commit message\":")
            print("https://chris.beams.io/posts/git-commit/")
            print
            print("The following rules are violated:\n")

            

Reported by Pylint.

                          print("The commit message does not seem to comply with the project's guidelines.")
            print("Please try to follow the \"Seven rules of a great commit message\":")
            print("https://chris.beams.io/posts/git-commit/")
            print
            print("The following rules are violated:\n")

            for i in range(len(self.rule_funcs)):
                if not self.valid_rules[i]:
                    print("\t* Rule %d: \"%s\"" % (i+1, self.rules[i]))

            

Reported by Pylint.

              
        # Extra sanity checks beyond the seven rules
        if len(self.body_lines) == 0:
            print
            print("NOTE: the commit message has no body.")
            print("It is recommended to add a body with a description of your")
            print("changes, even if they are small. Explain what and why instead of how:")
            print("https://chris.beams.io/posts/git-commit/#why-not-how")


            

Reported by Pylint.

                          print("https://chris.beams.io/posts/git-commit/#why-not-how")

        if len(self.subject_words) < 3:
            print
            print("Warning: the subject line has less than three words.")
            print("Consider using a more explanatory subject line.")

        if num_violations > 0:
            print

            

Reported by Pylint.

                          print("Consider using a more explanatory subject line.")

        if num_violations > 0:
            print
            print("Run 'git commit --amend' to change the commit message")

        print

        return num_violations

            

Reported by Pylint.

                          print
            print("Run 'git commit --amend' to change the commit message")

        print

        return num_violations

def main():
    if len(sys.argv) != 2:

            

Reported by Pylint.

              SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE TABLE continuous_agg_test_mat(time int);
select create_hypertable('continuous_agg_test_mat', 'time', chunk_time_interval=> 10);
INSERT INTO _timescaledb_catalog.continuous_agg VALUES (2, 1, '','','','',0,'','');
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER


            

Reported by SQLint.

              CREATE TABLE continuous_agg_test_mat(time int);
select create_hypertable('continuous_agg_test_mat', 'time', chunk_time_interval=> 10);
INSERT INTO _timescaledb_catalog.continuous_agg VALUES (2, 1, '','','','',0,'','');
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- create the trigger
CREATE TRIGGER continuous_agg_insert_trigger
    AFTER INSERT ON continuous_agg_test
    FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.continuous_agg_invalidation_trigger(1);

            

Reported by SQLint.

              SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

-- set the continuous_aggs_invalidation_threshold to 15, any insertions below that value need an invalidation
\c :TEST_DBNAME :ROLE_SUPERUSER
INSERT INTO _timescaledb_catalog.continuous_aggs_invalidation_threshold VALUES (1, 15);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2), (21, 3), (22, 4);


            

Reported by SQLint.

              -- set the continuous_aggs_invalidation_threshold to 15, any insertions below that value need an invalidation
\c :TEST_DBNAME :ROLE_SUPERUSER
INSERT INTO _timescaledb_catalog.continuous_aggs_invalidation_threshold VALUES (1, 15);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

INSERT INTO continuous_agg_test VALUES (10, 1), (11, 2), (21, 3), (22, 4);

SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

            

Reported by SQLint.

              SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

\c :TEST_DBNAME :ROLE_SUPERUSER
DELETE FROM _timescaledb_catalog.continuous_agg where mat_hypertable_id =  2;
DELETE FROM _timescaledb_config.bgw_job WHERE id = 2;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

DROP TABLE continuous_agg_test CASCADE;

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_SUPERUSER
DELETE FROM _timescaledb_catalog.continuous_agg where mat_hypertable_id =  2;
DELETE FROM _timescaledb_config.bgw_job WHERE id = 2;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

DROP TABLE continuous_agg_test CASCADE;
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

DROP TABLE continuous_agg_test CASCADE;
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- CREATE VIEW creates the invalidation trigger correctly

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;
TRUNCATE _timescaledb_catalog.continuous_aggs_invalidation_threshold;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- CREATE VIEW creates the invalidation trigger correctly
CREATE TABLE ca_inval_test(time int);
SELECT create_hypertable('ca_inval_test', 'time', chunk_time_interval=> 10);
CREATE OR REPLACE FUNCTION integer_now_test2() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(time), 0) FROM ca_inval_test $$;

            

Reported by SQLint.

              SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

\c :TEST_DBNAME :ROLE_SUPERUSER
INSERT INTO _timescaledb_catalog.continuous_aggs_invalidation_threshold VALUES (3, 15);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

INSERT INTO ca_inval_test SELECT generate_series(5, 15);


            

Reported by SQLint.

              
\c :TEST_DBNAME :ROLE_SUPERUSER
INSERT INTO _timescaledb_catalog.continuous_aggs_invalidation_threshold VALUES (3, 15);
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

INSERT INTO ca_inval_test SELECT generate_series(5, 15);

SELECT * FROM _timescaledb_catalog.continuous_aggs_invalidation_threshold;
SELECT * from _timescaledb_catalog.continuous_aggs_hypertable_invalidation_log;

            

Reported by SQLint.

              -- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.

\set ON_ERROR_STOP 0

--negative tests for query validation
create table mat_t1( a integer, b integer,c TEXT);

CREATE TABLE conditions (

            

Reported by SQLint.

              FROM _timescaledb_catalog.continuous_agg ca
INNER JOIN _timescaledb_catalog.hypertable h ON(h.id = ca.mat_hypertable_id)
WHERE user_view_name = 'mat_with_test'
\gset

\set ON_ERROR_STOP 0
ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'false');
ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'true');
ALTER MATERIALIZED VIEW mat_with_test ALTER timec DROP default;

            

Reported by SQLint.

              ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'false');
ALTER MATERIALIZED VIEW mat_with_test SET(timescaledb.create_group_indexes = 'true');
ALTER MATERIALIZED VIEW mat_with_test ALTER timec DROP default;
\set ON_ERROR_STOP 1

DROP TABLE conditions CASCADE;


--test WITH using a hypertable with an integer time dimension

            

Reported by SQLint.

              CREATE OR REPLACE FUNCTION integer_now_test_s() returns smallint LANGUAGE SQL STABLE as $$ SELECT coalesce(max(timec), 0)::smallint FROM conditions $$;
SELECT set_integer_now_func('conditions', 'integer_now_test_s');

\set ON_ERROR_STOP 0
create materialized view mat_with_test( timec, minl, sumt , sumh)
WITH (timescaledb.continuous)
as
select time_bucket(100, timec), min(location), sum(temperature),sum(humidity)
from conditions

            

Reported by SQLint.

              from conditions
group by time_bucket(100, timec) WITH NO DATA;

\set ON_ERROR_STOP 1
DROP TABLE conditions cascade;

CREATE TABLE conditions (
      timec       BIGINT       NOT NULL,
      location    TEXT              NOT NULL,

            

Reported by SQLint.

              CREATE TABLE text_time(time TEXT);
    SELECT create_hypertable('text_time', 'time', chunk_time_interval => 10, time_partitioning_func => 'text_part_func');

\set ON_ERROR_STOP 0
CREATE MATERIALIZED VIEW text_view
    WITH (timescaledb.continuous)
    AS SELECT time_bucket('5', text_part_func(time)), COUNT(time)
        FROM text_time
        GROUP BY 1 WITH NO DATA;

            

Reported by SQLint.

                  AS SELECT time_bucket('5', text_part_func(time)), COUNT(time)
        FROM text_time
        GROUP BY 1 WITH NO DATA;
\set ON_ERROR_STOP 1

-- Check that we get an error when mixing normal materialized views
-- and continuous aggregates.
CREATE MATERIALIZED VIEW normal_mat_view AS
SELECT time_bucket('5', text_part_func(time)), COUNT(time)

            

Reported by SQLint.

                FROM text_time
GROUP BY 1 WITH NO DATA;

\set ON_ERROR_STOP 0
DROP MATERIALIZED VIEW normal_mat_view, mat_with_test;
\set ON_ERROR_STOP 1

DROP TABLE text_time CASCADE;


            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
DROP MATERIALIZED VIEW normal_mat_view, mat_with_test;
\set ON_ERROR_STOP 1

DROP TABLE text_time CASCADE;

CREATE TABLE measurements (time TIMESTAMPTZ NOT NULL, device INT, value FLOAT);
SELECT create_hypertable('measurements', 'time');

            

Reported by SQLint.

              
INSERT INTO measurements VALUES ('2019-03-04 13:30', 1, 1.3);

\set VERBOSITY default
\set ON_ERROR_STOP 0
SELECT timescaledb_experimental.refresh_continuous_aggregate(
  'mat_with_test',
  show_chunks('measurements')
);

            

Reported by SQLint.