--and not how much work constraint_exclusion does
SET constraint_exclusion = 'off';

\qecho test upper bounds
:PREFIX SELECT * FROM hyper WHERE time < 10 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time < 11 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time = 10 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE 10 >= time ORDER BY value;


            

Reported by SQLint.

              :PREFIX SELECT * FROM hyper WHERE time = 10 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE 10 >= time ORDER BY value;

\qecho test lower bounds
:PREFIX SELECT * FROM hyper WHERE time >= 10 and time < 20 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE 10 < time and 20 >= time ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time >= 9 and time < 20 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time > 9 and time < 20 ORDER BY value;


            

Reported by SQLint.

              :PREFIX SELECT * FROM hyper WHERE time >= 9 and time < 20 ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time > 9 and time < 20 ORDER BY value;

\qecho test empty result
:PREFIX SELECT * FROM hyper WHERE time < 0;

\qecho test expression evaluation
:PREFIX SELECT * FROM hyper WHERE time < (5*2)::smallint;


            

Reported by SQLint.

              \qecho test empty result
:PREFIX SELECT * FROM hyper WHERE time < 0;

\qecho test expression evaluation
:PREFIX SELECT * FROM hyper WHERE time < (5*2)::smallint;

\qecho test logic at INT64_MAX
:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775807::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775806::bigint ORDER BY value;

            

Reported by SQLint.

              \qecho test expression evaluation
:PREFIX SELECT * FROM hyper WHERE time < (5*2)::smallint;

\qecho test logic at INT64_MAX
:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775807::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time = 9223372036854775806::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time >= 9223372036854775807::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time > 9223372036854775807::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time > 9223372036854775806::bigint ORDER BY value;

            

Reported by SQLint.

              :PREFIX SELECT * FROM hyper WHERE time > 9223372036854775807::bigint ORDER BY value;
:PREFIX SELECT * FROM hyper WHERE time > 9223372036854775806::bigint ORDER BY value;

\qecho cte
:PREFIX WITH cte AS(
  SELECT * FROM hyper WHERE time < 10
)
SELECT * FROM cte ORDER BY value;


            

Reported by SQLint.

              )
SELECT * FROM cte ORDER BY value;

\qecho subquery
:PREFIX SELECT 0 = ANY (SELECT value FROM hyper WHERE time < 10);

\qecho no space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 ORDER BY value;


            

Reported by SQLint.

              \qecho subquery
:PREFIX SELECT 0 = ANY (SELECT value FROM hyper WHERE time < 10);

\qecho no space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 ORDER BY value;

\qecho valid space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and device_id = 'dev5' ORDER BY value;
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev5' = device_id ORDER BY value;

            

Reported by SQLint.

              \qecho no space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 ORDER BY value;

\qecho valid space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and device_id = 'dev5' ORDER BY value;
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev5' = device_id ORDER BY value;
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev'||(2+3) = device_id ORDER BY value;

\qecho only space constraint

            

Reported by SQLint.

              :PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev5' = device_id ORDER BY value;
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and 'dev'||(2+3) = device_id ORDER BY value;

\qecho only space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE 'dev5' = device_id ORDER BY value;

\qecho unhandled space constraint
:PREFIX SELECT * FROM hyper_w_space WHERE time < 10 and device_id > 'dev5' ORDER BY value;


            

Reported by SQLint.

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

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\o
\set ECHO all

            

Reported by SQLint.

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

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\o
\set ECHO all


            

Reported by SQLint.

              -- Add data nodes using TimescaleDB data_node management API.
SELECT * FROM add_data_node('data_node_1', host => 'localhost', database => :'DN_DBNAME_1');
SELECT * FROM add_data_node('data_node_2', 'localhost', database => :'DN_DBNAME_2');
\set ON_ERROR_STOP 0
-- Add again
SELECT * FROM add_data_node('data_node_2', host => 'localhost', database => :'DN_DBNAME_2');
-- No host provided
SELECT * FROM add_data_node('data_node_99');
SELECT * FROM add_data_node(NULL);

            

Reported by SQLint.

                                          port => -1,
                            database => :'DN_DBNAME_3');

SELECT inet_server_port() as PGPORT \gset

-- Adding a data node via ADD SERVER is blocked
CREATE SERVER data_node_4 FOREIGN DATA WRAPPER timescaledb_fdw
OPTIONS (host 'localhost', port ':PGPORT', dbname :'DN_DBNAME_4');
-- Dropping a data node via DROP SERVER is also blocked

            

Reported by SQLint.

              OPTIONS (host 'localhost', port ':PGPORT', dbname :'DN_DBNAME_4');
-- Dropping a data node via DROP SERVER is also blocked
DROP SERVER data_node_1, data_node_2;
\set ON_ERROR_STOP 1

-- Should not generate error with if_not_exists option
SELECT * FROM add_data_node('data_node_2', host => 'localhost', database => :'DN_DBNAME_2',
                            if_not_exists => true);


            

Reported by SQLint.

              

-- Test altering server command is blocked
\set ON_ERROR_STOP 0
ALTER SERVER data_node_1 OPTIONS (SET fdw_startup_cost '110.0');
ALTER SERVER data_node_1 OPTIONS (DROP sslmode);
ALTER SERVER data_node_1 RENAME TO data_node_k;
ALTER SERVER data_node_1 OWNER TO CURRENT_USER;
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              ALTER SERVER data_node_1 OPTIONS (DROP sslmode);
ALTER SERVER data_node_1 RENAME TO data_node_k;
ALTER SERVER data_node_1 OWNER TO CURRENT_USER;
\set ON_ERROR_STOP 1

-- List foreign data nodes
SELECT node_name, "options" FROM timescaledb_information.data_nodes ORDER BY node_name;

-- Delete a data node

            

Reported by SQLint.

              -- List data nodes
SELECT node_name, "options" FROM timescaledb_information.data_nodes ORDER BY node_name;

\set ON_ERROR_STOP 0
-- Deleting a non-existing data node generates error
SELECT * FROM delete_data_node('data_node_3');
\set ON_ERROR_STOP 1

-- Deleting non-existing data node with "if_exists" set does not generate error

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- Deleting a non-existing data node generates error
SELECT * FROM delete_data_node('data_node_3');
\set ON_ERROR_STOP 1

-- Deleting non-existing data node with "if_exists" set does not generate error
SELECT * FROM delete_data_node('data_node_3', if_exists => true);

SELECT node_name, "options" FROM timescaledb_information.data_nodes ORDER BY node_name;

            

Reported by SQLint.

              -- Create a distributed hypertable where no nodes can be selected
-- because there are no data nodes with the right permissions.
CREATE TABLE disttable(time timestamptz, device int, temp float);
\set ON_ERROR_STOP 0
\set VERBOSITY default
SELECT * FROM create_distributed_hypertable('disttable', 'time', 'device');
\set VERBOSITY terse
\set ON_ERROR_STOP 1


            

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;
CREATE OR REPLACE FUNCTION show_data_nodes()
RETURNS TABLE(data_node_name NAME, host TEXT, port INT, dbname NAME)
AS :TSL_MODULE_PATHNAME, 'ts_test_data_node_show' LANGUAGE C;

-- Fetch the encoding, collation, and ctype as quoted strings into

            

Reported by SQLint.

                   , QUOTE_LITERAL(datctype) AS ctype
  FROM pg_database
 WHERE datname = current_database()
 \gset

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;

SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test');


            

Reported by SQLint.

              
-- Ensure database and extensions are installed and have the correct
-- encoding, ctype and collation.
\c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
SELECT extname, nspname
FROM pg_extension e, pg_namespace n
WHERE e.extnamespace = n.oid
AND e.extname = 'timescaledb';
SELECT PG_ENCODING_TO_CHAR(encoding) = :enc

            

Reported by SQLint.

                FROM pg_database
 WHERE datname = current_database();

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
-- After delete_data_node, the database and extension should still
-- exist on the data node
SELECT * FROM delete_data_node('bootstrap_test');
SELECT * FROM show_data_nodes();


            

Reported by SQLint.

              SELECT * FROM delete_data_node('bootstrap_test');
SELECT * FROM show_data_nodes();

\c bootstrap_test :ROLE_CLUSTER_SUPERUSER;

SELECT extname, nspname
FROM pg_extension e, pg_namespace n
WHERE e.extnamespace = n.oid
AND e.extname = 'timescaledb';

            

Reported by SQLint.

              WHERE e.extnamespace = n.oid
AND e.extname = 'timescaledb';

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
\set ON_ERROR_STOP 0
-- Trying to add the data node again should fail, with or without
-- bootstrapping.
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test', bootstrap=>false);
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test');

            

Reported by SQLint.

              AND e.extname = 'timescaledb';

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
\set ON_ERROR_STOP 0
-- Trying to add the data node again should fail, with or without
-- bootstrapping.
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test', bootstrap=>false);
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test');
\set ON_ERROR_STOP 0

            

Reported by SQLint.

              -- bootstrapping.
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test', bootstrap=>false);
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost', database => 'bootstrap_test');
\set ON_ERROR_STOP 0

DROP DATABASE bootstrap_test;

----------------------------------------------------------------------
-- Bootstrap the database and check that calling it without

            

Reported by SQLint.

              ----------------------------------------------------------------------
-- Bootstrap the database and check that calling it without
-- bootstrapping does not find any problems.
\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
SELECT * FROM add_data_node('bootstrap_test', host => 'localhost',
                            database => 'bootstrap_test', bootstrap => true);
SELECT * FROM show_data_nodes();

SELECT * FROM delete_data_node('bootstrap_test');

            

Reported by SQLint.

              
SELECT * FROM delete_data_node('bootstrap_test');

\c bootstrap_test :ROLE_CLUSTER_SUPERUSER;
-- This should show dist_uuid row on the deleted node since that is
-- not removed by delete_data_node.
SELECT key FROM _timescaledb_catalog.metadata WHERE key = 'dist_uuid';

-- Delete the dist_uuid so that we can try to re-add it without

            

Reported by SQLint.

              --
-- Setup
--
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(timeout INT = -1, mock_start_time INT = 0) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run(timeout INT = -1, mock_start_time INT = 0) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

            

Reported by SQLint.

                UPDATE _timescaledb_config.bgw_job SET scheduled = NOT scheduled WHERE id = $1;
$$;

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

CREATE OR REPLACE FUNCTION ts_bgw_params_mock_wait_returns_immediately(new_val INTEGER) RETURNS VOID

            

Reported by SQLint.

              DELETE FROM _timescaledb_config.bgw_job WHERE TRUE;
TRUNCATE _timescaledb_internal.bgw_job_stat;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE public.bgw_log(
    msg_no INT,
    mock_time BIGINT,
    application_name TEXT,

            

Reported by SQLint.

              -- test deleting job also terminates running jobs
--

SELECT add_job('ts_bgw_test_job_sleep','1h') AS job_id \gset
SELECT ts_bgw_db_scheduler_test_run();

SELECT wait_for_logentry(:job_id);
SELECT application_name FROM pg_stat_activity WHERE application_name LIKE 'User-Defined Action%';


            

Reported by SQLint.

              SELECT wait_for_logentry(:job_id);
SELECT application_name FROM pg_stat_activity WHERE application_name LIKE 'User-Defined Action%';

\x on
SELECT job_id, job_status FROM timescaledb_information.job_stats;
-- Showing non-volatile information from pg_stat_activity for
-- debugging purposes. Information schema above reads from this view.
SELECT datname, usename, application_name, state, query, wait_event_type, wait_event
  FROM pg_stat_activity WHERE application_name LIKE 'User-Defined Action%';

            

Reported by SQLint.

              -- debugging purposes. Information schema above reads from this view.
SELECT datname, usename, application_name, state, query, wait_event_type, wait_event
  FROM pg_stat_activity WHERE application_name LIKE 'User-Defined Action%';
\x off

-- have to suppress notices here as delete_job will print pid of the running background worker processes
SET client_min_messages TO WARNING;
SELECT delete_job(:job_id);
RESET client_min_messages;

            

Reported by SQLint.

              --
-- Test running a normal job
--
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE bgw_log;
ALTER SEQUENCE _timescaledb_config.bgw_job_id_seq RESTART;
SELECT ts_bgw_params_reset_time();
SELECT insert_job('test_job_1', 'bgw_test_job_1', INTERVAL '100ms', INTERVAL '100s', INTERVAL '1s');
select * from _timescaledb_config.bgw_job;

            

Reported by SQLint.

              SELECT ts_bgw_params_reset_time();
SELECT insert_job('test_job_1', 'bgw_test_job_1', INTERVAL '100ms', INTERVAL '100s', INTERVAL '1s');
select * from _timescaledb_config.bgw_job;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--Tests that the scheduler start a job right away if it's the first time and there is no job_stat entry for it
SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25);
SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes
FROM _timescaledb_internal.bgw_job_stat;

            

Reported by SQLint.

              --
-- Test what happens when running a job that throws an error
--
\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE bgw_log;
TRUNCATE _timescaledb_internal.bgw_job_stat;
SELECT ts_bgw_params_reset_time();
DELETE FROM _timescaledb_config.bgw_job;
SELECT insert_job('test_job_2', 'bgw_test_job_2_error', INTERVAL '100ms', INTERVAL '100s', INTERVAL '100ms');

            

Reported by SQLint.

              SELECT ts_bgw_params_reset_time();
DELETE FROM _timescaledb_config.bgw_job;
SELECT insert_job('test_job_2', 'bgw_test_job_2_error', INTERVAL '100ms', INTERVAL '100s', INTERVAL '100ms');
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--Run the first time and error
SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25);
SELECT job_id, next_start-last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes
FROM _timescaledb_internal.bgw_job_stat;

            

Reported by SQLint.

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

\c :TEST_DBNAME :ROLE_SUPERUSER
create schema test_schema AUTHORIZATION :ROLE_DEFAULT_PERM_USER;
create schema chunk_schema AUTHORIZATION :ROLE_DEFAULT_PERM_USER_2;

SET ROLE :ROLE_DEFAULT_PERM_USER;
create table test_schema.test_table(time BIGINT, temp float8, device_id text, device_type text, location text, id int, id2 int);

            

Reported by SQLint.

              SET ROLE :ROLE_DEFAULT_PERM_USER;
create table test_schema.test_table(time BIGINT, temp float8, device_id text, device_type text, location text, id int, id2 int);

\set ON_ERROR_STOP 0
-- get_create_command should fail since hypertable isn't made yet
SELECT * FROM _timescaledb_internal.get_create_command('test_table');
\set ON_ERROR_STOP 1

\dt "test_schema".*

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- get_create_command should fail since hypertable isn't made yet
SELECT * FROM _timescaledb_internal.get_create_command('test_table');
\set ON_ERROR_STOP 1

\dt "test_schema".*

create table test_schema.test_table_no_not_null(time BIGINT, device_id text);


            

Reported by SQLint.

              
create table test_schema.test_table_no_not_null(time BIGINT, device_id text);

\set ON_ERROR_STOP 0
-- Permission denied with unprivileged role
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
select * from create_hypertable('test_schema.test_table_no_not_null', 'time', 'device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

-- CREATE on schema is not enough

            

Reported by SQLint.

              GRANT ALL ON SCHEMA test_schema TO :ROLE_DEFAULT_PERM_USER_2;
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
select * from create_hypertable('test_schema.test_table_no_not_null', 'time', 'device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
\set ON_ERROR_STOP 1

-- Should work with when granted table owner role
RESET ROLE;
GRANT :ROLE_DEFAULT_PERM_USER TO :ROLE_DEFAULT_PERM_USER_2;
SET ROLE :ROLE_DEFAULT_PERM_USER_2;

            

Reported by SQLint.

              SET ROLE :ROLE_DEFAULT_PERM_USER_2;
select * from create_hypertable('test_schema.test_table_no_not_null', 'time', 'device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

\set ON_ERROR_STOP 0
insert into test_schema.test_table_no_not_null (device_id) VALUES('foo');
\set ON_ERROR_STOP 1
insert into test_schema.test_table_no_not_null (time, device_id) VALUES(1, 'foo');

RESET ROLE;

            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
insert into test_schema.test_table_no_not_null (device_id) VALUES('foo');
\set ON_ERROR_STOP 1
insert into test_schema.test_table_no_not_null (time, device_id) VALUES(1, 'foo');

RESET ROLE;
SET ROLE :ROLE_DEFAULT_PERM_USER;


            

Reported by SQLint.

              RESET ROLE;
SET ROLE :ROLE_DEFAULT_PERM_USER;

\set ON_ERROR_STOP 0
-- No permissions on associated schema should fail
select * from create_hypertable('test_schema.test_table', 'time', 'device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'), associated_schema_name => 'chunk_schema');
\set ON_ERROR_STOP 1

-- Granting permissions on chunk_schema should make things work

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- No permissions on associated schema should fail
select * from create_hypertable('test_schema.test_table', 'time', 'device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'), associated_schema_name => 'chunk_schema');
\set ON_ERROR_STOP 1

-- Granting permissions on chunk_schema should make things work
RESET ROLE;
GRANT CREATE ON SCHEMA chunk_schema TO :ROLE_DEFAULT_PERM_USER;
SET ROLE :ROLE_DEFAULT_PERM_USER;

            

Reported by SQLint.

              SELECT set_number_partitions('test_schema.test_table', 2, 'location');
select * from _timescaledb_catalog.dimension WHERE column_name = 'location';

\set ON_ERROR_STOP 0
--must give an explicit dimension when there are multiple space dimensions
SELECT set_number_partitions('test_schema.test_table', 3);
--too few
SELECT set_number_partitions('test_schema.test_table', 0, 'location');
-- Too many

            

Reported by SQLint.

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

\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER

-- Support for execute_sql_and_filter_server_name_on_error()
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql

            

Reported by SQLint.

              SELECT * FROM add_data_node('data_node_3', host => 'localhost', database => :'DN_DBNAME_3');
GRANT USAGE ON FOREIGN SERVER data_node_1, data_node_2, data_node_3 TO PUBLIC;

\des+

RESET ROLE;
CREATE FUNCTION _timescaledb_internal.invoke_distributed_commands()
RETURNS void
AS :TSL_MODULE_PATHNAME, 'ts_invoke_distributed_commands'

            

Reported by SQLint.

              
SELECT _timescaledb_internal.invoke_distributed_commands();

\c :DN_DBNAME_1
\dt
SELECT * FROM disttable1;
\c :DN_DBNAME_2
\dt
SELECT * FROM disttable1;

            

Reported by SQLint.

              \c :DN_DBNAME_1
\dt
SELECT * FROM disttable1;
\c :DN_DBNAME_2
\dt
SELECT * FROM disttable1;
\c :DN_DBNAME_3
\dt
SELECT * FROM disttable1;

            

Reported by SQLint.

              \c :DN_DBNAME_2
\dt
SELECT * FROM disttable1;
\c :DN_DBNAME_3
\dt
SELECT * FROM disttable1;
\c :TEST_DBNAME :ROLE_SUPERUSER
SET ROLE :ROLE_1;


            

Reported by SQLint.

              \c :DN_DBNAME_3
\dt
SELECT * FROM disttable1;
\c :TEST_DBNAME :ROLE_SUPERUSER
SET ROLE :ROLE_1;

-- Verify failed insert command gets fully rolled back
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.invoke_faulty_distributed_command();

            

Reported by SQLint.

              SET ROLE :ROLE_1;

-- Verify failed insert command gets fully rolled back
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.invoke_faulty_distributed_command();
\set ON_ERROR_STOP 1

\c :DN_DBNAME_1
SELECT * from disttable2;

            

Reported by SQLint.

              -- Verify failed insert command gets fully rolled back
\set ON_ERROR_STOP 0
SELECT _timescaledb_internal.invoke_faulty_distributed_command();
\set ON_ERROR_STOP 1

\c :DN_DBNAME_1
SELECT * from disttable2;
\c :DN_DBNAME_2
SELECT * from disttable2;

            

Reported by SQLint.

              
\c :DN_DBNAME_1
SELECT * from disttable2;
\c :DN_DBNAME_2
SELECT * from disttable2;

-- Test connection session identity
\c :TEST_DBNAME :ROLE_SUPERUSER
\unset ECHO

            

Reported by SQLint.

              SELECT * from disttable2;

-- Test connection session identity
\c :TEST_DBNAME :ROLE_SUPERUSER
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\o
\set ECHO all

            

Reported by SQLint.

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

--install necessary functions for tests
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_test_compression() RETURNS VOID
AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE;
\ir include/compression_utils.sql
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER


            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_test_compression() RETURNS VOID
AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE;
\ir include/compression_utils.sql
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

------------------
-- C unit tests --
------------------

            

Reported by SQLint.

              
SELECT ts_test_compression();

\ir include/rand_generator.sql

------------------------
-- BIGINT Compression --
------------------------
SELECT

            

Reported by SQLint.

              
-- random order
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, item::bigint FROM (select sub.item from (SELECT generate_series(1, 1000) item) as sub ORDER BY gen_rand_minstd()) sub;
\ir include/compression_test.sql
DROP TABLE base_ints;

-- ascending order with nulls
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, item::bigint FROM (SELECT generate_series(1, 1000) item) sub;
INSERT INTO base_ints VALUES (0, NULL), (10, NULL), (10000, NULL);

            

Reported by SQLint.

              -- ascending order with nulls
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, item::bigint FROM (SELECT generate_series(1, 1000) item) sub;
INSERT INTO base_ints VALUES (0, NULL), (10, NULL), (10000, NULL);
\ir include/compression_test.sql

SELECT c ints_text FROM compressed;

DROP TABLE base_ints;


            

Reported by SQLint.

              
-- single element
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, item::bigint FROM (SELECT generate_series(1, 1) item) sub;
\ir include/compression_test.sql
DROP TABLE base_ints;

-- really big deltas
SELECT  9223372036854775807 as big_int_max \gset
SELECT -9223372036854775808	 as big_int_min \gset

            

Reported by SQLint.

              DROP TABLE base_ints;

-- really big deltas
SELECT  9223372036854775807 as big_int_max \gset
SELECT -9223372036854775808	 as big_int_min \gset
CREATE TABLE base_ints AS SELECT row_number() over () as rn, item FROM
    (
        VALUES
           --big deltas

            

Reported by SQLint.

                          (0), (:big_int_max-1), (:big_int_max-1), (:big_int_min), (:big_int_min), (:big_int_max-1), (:big_int_max-1)
    ) as t(item);

\ir include/compression_test.sql
DROP TABLE base_ints;

-- NULLs
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, NULLIF(i, 5) item FROM generate_series(1::BIGINT, 10) i;
\ir include/compression_test.sql

            

Reported by SQLint.

              
-- NULLs
CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, NULLIF(i, 5) item FROM generate_series(1::BIGINT, 10) i;
\ir include/compression_test.sql
DROP TABLE base_ints;

CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, NULLIF(i, 1) item FROM generate_series(1::BIGINT, 10) i;
\ir include/compression_test.sql
DROP TABLE base_ints;

            

Reported by SQLint.

              DROP TABLE base_ints;

CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, NULLIF(i, 1) item FROM generate_series(1::BIGINT, 10) i;
\ir include/compression_test.sql
DROP TABLE base_ints;

CREATE TABLE base_ints AS SELECT row_number() OVER() as rn, NULLIF(i, 10) item FROM generate_series(1::BIGINT, 10) i;
\ir include/compression_test.sql
DROP TABLE base_ints;

            

Reported by SQLint.

              #!/usr/bin/env python

import unittest
from commit_msg import GitCommitMessage

class TestCommitMsg(unittest.TestCase):

    def testNoInput(self):
        m = GitCommitMessage().parse_lines([])

            

Reported by Pylint.

              import unittest
from commit_msg import GitCommitMessage

class TestCommitMsg(unittest.TestCase):

    def testNoInput(self):
        m = GitCommitMessage().parse_lines([])
        self.assertEqual(len(m.body_lines), 0)
        m = GitCommitMessage().parse_lines(None)

            

Reported by Pylint.

              
class TestCommitMsg(unittest.TestCase):

    def testNoInput(self):
        m = GitCommitMessage().parse_lines([])
        self.assertEqual(len(m.body_lines), 0)
        m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)


            

Reported by Pylint.

              
class TestCommitMsg(unittest.TestCase):

    def testNoInput(self):
        m = GitCommitMessage().parse_lines([])
        self.assertEqual(len(m.body_lines), 0)
        m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)


            

Reported by Pylint.

              class TestCommitMsg(unittest.TestCase):

    def testNoInput(self):
        m = GitCommitMessage().parse_lines([])
        self.assertEqual(len(m.body_lines), 0)
        m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):

            

Reported by Pylint.

                  def testNoInput(self):
        m = GitCommitMessage().parse_lines([])
        self.assertEqual(len(m.body_lines), 0)
        m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):
        m = GitCommitMessage().parse_lines(['This is a subject line', '   ', 'body line 1', 'body line 2'])
        self.assertEqual(m.subject, 'This is a subject line')

            

Reported by Pylint.

                      m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):
        m = GitCommitMessage().parse_lines(['This is a subject line', '   ', 'body line 1', 'body line 2'])
        self.assertEqual(m.subject, 'This is a subject line')
        self.assertTrue(m.has_subject_body_separator)
        self.assertEqual(m.body_lines[0], 'body line 1')
        self.assertEqual(m.body_lines[1], 'body line 2')

            

Reported by Pylint.

                      m = GitCommitMessage().parse_lines(None)
        self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):
        m = GitCommitMessage().parse_lines(['This is a subject line', '   ', 'body line 1', 'body line 2'])
        self.assertEqual(m.subject, 'This is a subject line')
        self.assertTrue(m.has_subject_body_separator)
        self.assertEqual(m.body_lines[0], 'body line 1')
        self.assertEqual(m.body_lines[1], 'body line 2')

            

Reported by Pylint.

                      self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):
        m = GitCommitMessage().parse_lines(['This is a subject line', '   ', 'body line 1', 'body line 2'])
        self.assertEqual(m.subject, 'This is a subject line')
        self.assertTrue(m.has_subject_body_separator)
        self.assertEqual(m.body_lines[0], 'body line 1')
        self.assertEqual(m.body_lines[1], 'body line 2')


            

Reported by Pylint.

                      self.assertEqual(len(m.body_lines), 0)

    def testParsing(self):
        m = GitCommitMessage().parse_lines(['This is a subject line', '   ', 'body line 1', 'body line 2'])
        self.assertEqual(m.subject, 'This is a subject line')
        self.assertTrue(m.has_subject_body_separator)
        self.assertEqual(m.body_lines[0], 'body line 1')
        self.assertEqual(m.body_lines[1], 'body line 2')


            

Reported by Pylint.

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

-- Need to be super user to create extension and add data nodes
\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;

-- Support for execute_sql_and_filter_server_name_on_error()
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;

-- Support for execute_sql_and_filter_server_name_on_error()
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\ir include/filter_exec.sql
\o
\set ECHO all

            

Reported by SQLint.

              CALL distributed_exec($$ SELECT create_hypertable('local', 'time') $$, '{ "data_node_1", "data_node_3" }');

-- Import testsupport.sql file to data nodes
\unset ECHO
\o /dev/null
\c :MY_DB1
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :MY_DB2

            

Reported by SQLint.

              \o /dev/null
\c :MY_DB1
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :MY_DB2
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :MY_DB3
SET client_min_messages TO ERROR;

            

Reported by SQLint.

              \ir :TEST_SUPPORT_FILE
\c :MY_DB2
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :MY_DB3
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :TEST_DBNAME :ROLE_SUPERUSER;
\o

            

Reported by SQLint.

              \ir :TEST_SUPPORT_FILE
\c :MY_DB3
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :TEST_DBNAME :ROLE_SUPERUSER;
\o
SET client_min_messages TO NOTICE;
\set ECHO all


            

Reported by SQLint.

              SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :TEST_DBNAME :ROLE_SUPERUSER;
\o
SET client_min_messages TO NOTICE;
\set ECHO all

-- This SCHEMA will not be created on data nodes
CREATE SCHEMA disttable_schema AUTHORIZATION :ROLE_1;

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_SUPERUSER;
\o
SET client_min_messages TO NOTICE;
\set ECHO all

-- This SCHEMA will not be created on data nodes
CREATE SCHEMA disttable_schema AUTHORIZATION :ROLE_1;
CREATE SCHEMA some_schema AUTHORIZATION :ROLE_1;
SET ROLE :ROLE_1;

            

Reported by SQLint.

              TRUNCATE disttable;

-- Test unsupported operations on distributed hypertable
\set ON_ERROR_STOP 0

-- test set_replication_factor on non-hypertable
SELECT * FROM set_replication_factor('non_disttable1', 1);
-- test set_replication_factor on non-distributed
SELECT * FROM set_replication_factor('non_disttable2', 1);

            

Reported by SQLint.

              DROP TABLE disttable, non_disttable2;
DROP TABLE disttable, disttable;

\set ON_ERROR_STOP 1

--------------------------------------------------------------------
-- Test renaming columns, constraints, indexes, and REINDEX command.
--------------------------------------------------------------------
INSERT INTO disttable VALUES

            

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'

CREATE OR REPLACE FUNCTION dimension_get_time(
    hypertable_id INT
)
    RETURNS _timescaledb_catalog.dimension LANGUAGE SQL STABLE AS

            

Reported by SQLint.

              CREATE INDEX ON drop_chunk_test1(time DESC);

-- show_chunks() without specifying a table is not allowed
\set ON_ERROR_STOP 0
SELECT show_chunks(NULL);
\set ON_ERROR_STOP 1

SELECT create_hypertable('public.drop_chunk_test1', 'time', chunk_time_interval => 1, create_default_indexes=>false);
SELECT create_hypertable('public.drop_chunk_test2', 'time', chunk_time_interval => 1, create_default_indexes=>false);

            

Reported by SQLint.

              -- show_chunks() without specifying a table is not allowed
\set ON_ERROR_STOP 0
SELECT show_chunks(NULL);
\set ON_ERROR_STOP 1

SELECT create_hypertable('public.drop_chunk_test1', 'time', chunk_time_interval => 1, create_default_indexes=>false);
SELECT create_hypertable('public.drop_chunk_test2', 'time', chunk_time_interval => 1, create_default_indexes=>false);
SELECT create_hypertable('public.drop_chunk_test3', 'time', chunk_time_interval => 1, create_default_indexes=>false);


            

Reported by SQLint.

              WHERE h.schema_name = 'public' AND (h.table_name = 'drop_chunk_test1' OR h.table_name = 'drop_chunk_test2')
ORDER BY c.id;

\dt "_timescaledb_internal"._hyper*

SELECT  _timescaledb_internal.get_partition_for_key('dev1'::text);
SELECT  _timescaledb_internal.get_partition_for_key('dev7'::varchar(5));

INSERT INTO PUBLIC.drop_chunk_test1 VALUES(1, 1.0, 'dev1');

            

Reported by SQLint.

              INNER JOIN  _timescaledb_catalog.chunk_constraint cc ON (cc.dimension_slice_id = ds.id AND cc.chunk_id = c.id)
WHERE h.schema_name = 'public' AND (h.table_name = 'drop_chunk_test1' OR h.table_name = 'drop_chunk_test2')
ORDER BY c.id;
\dt "_timescaledb_internal"._hyper*

-- next two calls of show_chunks should give same set of chunks as above when combined
SELECT show_chunks('drop_chunk_test1');
SELECT * FROM show_chunks('drop_chunk_test2');


            

Reported by SQLint.

              
CREATE VIEW dependent_view AS SELECT * FROM _timescaledb_internal._hyper_1_1_chunk;

\set ON_ERROR_STOP 0
SELECT drop_chunks('drop_chunk_test1');
SELECT drop_chunks('drop_chunk_test1', older_than => 2);
SELECT drop_chunks('drop_chunk_test1', older_than => NULL::interval);
SELECT drop_chunks('drop_chunk_test1', older_than => NULL::int);


            

Reported by SQLint.

              -- should error because there is no relation with that OID.
SELECT drop_chunks(3533, older_than => 3);

\set ON_ERROR_STOP 1

-- show created constraints and dimension slices for each chunk
SELECT c.table_name, cc.constraint_name, ds.id AS dimension_slice_id, ds.range_start, ds.range_end
FROM _timescaledb_catalog.chunk c
INNER JOIN _timescaledb_catalog.chunk_constraint cc ON (c.id = cc.chunk_id)

            

Reported by SQLint.

              SELECT show_chunks('drop_chunk_test1');
SELECT * FROM show_chunks('drop_chunk_test2');

\dt "_timescaledb_internal"._hyper*
-- show_chunks and drop_chunks output should be the same
\set QUERY1 'SELECT show_chunks(\'drop_chunk_test1\', older_than => 3)::NAME'
\set QUERY2 'SELECT drop_chunks(\'drop_chunk_test1\', older_than => 3)::NAME'
\set ECHO errors
\ir  :QUERY_RESULT_TEST_EQUAL_RELPATH

            

Reported by SQLint.

              WHERE h.schema_name = 'public' AND (h.table_name = 'drop_chunk_test1' OR h.table_name = 'drop_chunk_test2')
ORDER BY c.id;

\dt "_timescaledb_internal".*

-- next two calls of show_chunks should give same set of chunks as above when combined
SELECT show_chunks('drop_chunk_test1');
SELECT * FROM show_chunks('drop_chunk_test2');


            

Reported by SQLint.

              
-- 2,147,483,647 is the largest int so this tests that BIGINTs work
-- show_chunks and drop_chunks output should be the same
\set QUERY1 'SELECT show_chunks(\'drop_chunk_test3\', older_than => 2147483648)::NAME'
\set QUERY2 'SELECT drop_chunks(\'drop_chunk_test3\', older_than => 2147483648)::NAME'
\set ECHO errors
\ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all


            

Reported by SQLint.