FROM int_tab
GROUP BY time_bucket(1, a), a WITH NO DATA;

\c :TEST_DBNAME :ROLE_SUPERUSER
DELETE FROM _timescaledb_config.bgw_job WHERE TRUE;

SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT count(*) FROM _timescaledb_config.bgw_job;


            

Reported by SQLint.

              SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT count(*) FROM _timescaledb_config.bgw_job;

\set ON_ERROR_STOP 0
\set VERBOSITY default
SELECT add_continuous_aggregate_policy('int_tab', '1 day'::interval, 10 , '1 h'::interval);
SELECT add_continuous_aggregate_policy('mat_m1', '1 day'::interval, 10 , '1 h'::interval);
SELECT add_continuous_aggregate_policy('mat_m1', '1 day'::interval, 10 );
SELECT add_continuous_aggregate_policy('mat_m1', 10, '1 day'::interval, '1 h'::interval);

            

Reported by SQLint.

              SELECT add_continuous_aggregate_policy('mat_m1', 5, 10, '1h'::interval);
--refresh window less than two buckets
SELECT add_continuous_aggregate_policy('mat_m1', 11, 10, '1h'::interval);
SELECT add_continuous_aggregate_policy('mat_m1', 20, 10, '1h'::interval) as job_id \gset

--adding again should warn/error
SELECT add_continuous_aggregate_policy('mat_m1', 20, 10, '1h'::interval, if_not_exists=>false);
SELECT add_continuous_aggregate_policy('mat_m1', 20, 15, '1h'::interval, if_not_exists=>true);
SELECT add_continuous_aggregate_policy('mat_m1', 20, 10, '1h'::interval, if_not_exists=>true);

            

Reported by SQLint.

              SET ROLE :ROLE_DEFAULT_PERM_USER;
SELECT config FROM _timescaledb_config.bgw_job where id = :job_id;

\set ON_ERROR_STOP 0
\set VERBOSITY default
SELECT add_continuous_aggregate_policy('mat_m1', 20, 10, '1h'::interval, if_not_exists=>true);

SELECT remove_continuous_aggregate_policy('int_tab');
SELECT remove_continuous_aggregate_policy('mat_m1');

            

Reported by SQLint.

              --should fail
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
SELECT add_continuous_aggregate_policy('mat_m1', 20, 10, '1h'::interval) as job_id ;
\set VERBOSITY terse
\set ON_ERROR_STOP 1

SET ROLE :ROLE_DEFAULT_PERM_USER;
DROP MATERIALIZED VIEW mat_m1;


            

Reported by SQLint.

              		FROM continuous_agg_max_mat_date
		GROUP BY 1 WITH NO DATA;

\set ON_ERROR_STOP 0
\set VERBOSITY default
SELECT add_continuous_aggregate_policy('max_mat_view_date', '2 days', 10, '1 day'::interval);
--start_interval < end_interval
SELECT add_continuous_aggregate_policy('max_mat_view_date', '1 day'::interval, '2 days'::interval , '1 day'::interval) ;
--interval less than two buckets

            

Reported by SQLint.

              SELECT add_continuous_aggregate_policy('max_mat_view_date', '7 days', '1 day', '1 day'::interval);
SELECT add_continuous_aggregate_policy('max_mat_view_date', '14 days', '1 day', '1 day'::interval);
SELECT add_continuous_aggregate_policy('max_mat_view_date', '13 days', '-10 hours', '1 day'::interval);
\set VERBOSITY terse
\set ON_ERROR_STOP 1

-- Negative start offset gives two bucket window:
SELECT add_continuous_aggregate_policy('max_mat_view_date', '13 days', '-1 day', '1 day'::interval);
SELECT remove_continuous_aggregate_policy('max_mat_view_date');

            

Reported by SQLint.

              SELECT add_continuous_aggregate_policy('max_mat_view_date', NULL, NULL, '1 day'::interval);
SELECT remove_continuous_aggregate_policy('max_mat_view_date');

SELECT add_continuous_aggregate_policy('max_mat_view_date', '15 days', '1 day', '1 day'::interval) as job_id \gset
SELECT config FROM _timescaledb_config.bgw_job
WHERE id = :job_id;

INSERT INTO continuous_agg_max_mat_date
	SELECT generate_series('2019-09-01'::date, '2019-09-10'::date, '1 day');

            

Reported by SQLint.

              SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '1000000 years', '1 day' , '1 h'::interval);
SELECT remove_continuous_aggregate_policy('max_mat_view_timestamp');

\set ON_ERROR_STOP 0
\set VERBOSITY default
--start and end offset capped at the lowest time value, which means
--zero size window
SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '1000000 years', '900000 years' , '1 h'::interval);
SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '301 days', '10 months' , '1 h'::interval);

            

Reported by SQLint.

              --zero size window
SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '1000000 years', '900000 years' , '1 h'::interval);
SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '301 days', '10 months' , '1 h'::interval);
\set VERBOSITY terse
\set ON_ERROR_STOP 1

SELECT add_continuous_aggregate_policy('max_mat_view_timestamp', '15 days', '1 h'::interval , '1 h'::interval) as job_id \gset

--- to prevent NOTICES set message level to warning

            

Reported by SQLint.

              );
CREATE INDEX ON PUBLIC."Hypertable_1" (time, "Device_id");

\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable(NULL, NULL);
SELECT * FROM create_hypertable('"public"."Hypertable_1"', NULL);
-- integer time dimensions require an explicit interval
SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time');
-- space dimensions require explicit number of partitions

            

Reported by SQLint.

              SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

DELETE FROM  PUBLIC."Hypertable_1" ;
\set ON_ERROR_STOP 1

SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

            

Reported by SQLint.

              
SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
\set ON_ERROR_STOP 1

INSERT INTO "Hypertable_1" VALUES (0, 1, 0);


            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Hypertable_1"', 'time', 'Device_id', 2, chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
\set ON_ERROR_STOP 1

INSERT INTO "Hypertable_1" VALUES (0, 1, 0);

\set ON_ERROR_STOP 0
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk ALTER COLUMN temp_c DROP NOT NULL;

            

Reported by SQLint.

              
INSERT INTO "Hypertable_1" VALUES (0, 1, 0);

\set ON_ERROR_STOP 0
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk ALTER COLUMN temp_c DROP NOT NULL;
\set ON_ERROR_STOP 1

CREATE TABLE PUBLIC."Parent" (
  time BIGINT NOT NULL,

            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk ALTER COLUMN temp_c DROP NOT NULL;
\set ON_ERROR_STOP 1

CREATE TABLE PUBLIC."Parent" (
  time BIGINT NOT NULL,
  "Device_id" TEXT NOT NULL,
  temp_c int NOT NULL DEFAULT -1

            

Reported by SQLint.

                temp_c int NOT NULL DEFAULT -1
);

\set ON_ERROR_STOP 0
ALTER TABLE "Hypertable_1" INHERIT "Parent";
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk INHERIT "Parent";
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk NO INHERIT "Parent";
\set ON_ERROR_STOP 1


            

Reported by SQLint.

              ALTER TABLE "Hypertable_1" INHERIT "Parent";
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk INHERIT "Parent";
ALTER TABLE _timescaledb_internal._hyper_1_1_chunk NO INHERIT "Parent";
\set ON_ERROR_STOP 1

CREATE TABLE PUBLIC."Child" () INHERITS ("Parent");

\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Parent"', 'time', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));

            

Reported by SQLint.

              
CREATE TABLE PUBLIC."Child" () INHERITS ("Parent");

\set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Parent"', 'time', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
SELECT * FROM create_hypertable('"public"."Child"', 'time', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
\set ON_ERROR_STOP 1

CREATE UNLOGGED TABLE PUBLIC."Hypertable_unlogged" (

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
SELECT * FROM create_hypertable('"public"."Parent"', 'time', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
SELECT * FROM create_hypertable('"public"."Child"', 'time', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 month'));
\set ON_ERROR_STOP 1

CREATE UNLOGGED TABLE PUBLIC."Hypertable_unlogged" (
  time BIGINT NOT NULL,
  "Device_id" TEXT NOT NULL,
  temp_c int NOT NULL DEFAULT -1

            

Reported by SQLint.

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

\ir include/rand_generator.sql
\c :TEST_DBNAME :ROLE_SUPERUSER

SET client_min_messages = ERROR;
DROP TABLESPACE IF EXISTS tablespace1;
DROP TABLESPACE IF EXISTS tablespace2;

            

Reported by SQLint.

              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

CREATE TABLE test1 ("Time" timestamptz, i integer, b bigint, t text);
SELECT table_name from create_hypertable('test1', 'Time', chunk_time_interval=> INTERVAL '1 day');

INSERT INTO test1 SELECT t,  gen_rand_minstd(), gen_rand_minstd(), gen_rand_minstd()::text FROM generate_series('2018-03-02 1:00'::TIMESTAMPTZ, '2018-03-28 1:00', '1 hour') t;

            

Reported by SQLint.

              SELECT count(*) as "COUNT_CHUNKS_UNCOMPRESSED"
FROM _timescaledb_catalog.chunk chunk
INNER JOIN _timescaledb_catalog.hypertable hypertable ON (chunk.hypertable_id = hypertable.id)
WHERE hypertable.table_name like 'test1' \gset

SELECT count(*) as "COUNT_CHUNKS_COMPRESSED"
FROM _timescaledb_catalog.chunk chunk
INNER JOIN _timescaledb_catalog.hypertable comp_hyper ON (chunk.hypertable_id = comp_hyper.id)
INNER JOIN _timescaledb_catalog.hypertable uncomp_hyper ON (comp_hyper.id = uncomp_hyper.compressed_hypertable_id)

            

Reported by SQLint.

              INNER JOIN _timescaledb_catalog.hypertable comp_hyper ON (comp_chunk.hypertable_id = comp_hyper.id)
INNER JOIN _timescaledb_catalog.hypertable uncomp_hyper ON (comp_hyper.id = uncomp_hyper.compressed_hypertable_id)
INNER JOIN _timescaledb_catalog.chunk uncomp_chunk ON (uncomp_chunk.compressed_chunk_id = comp_chunk.id)
WHERE uncomp_hyper.table_name like 'test1' ORDER BY comp_chunk.id LIMIT 1\gset

-- ensure compression chunk cannot be moved directly
SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';


            

Reported by SQLint.

              SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';

\set ON_ERROR_STOP 0
ALTER TABLE :COMPRESSED_CHUNK_NAME SET TABLESPACE tablespace1;
\set ON_ERROR_STOP 1
SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';


            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
ALTER TABLE :COMPRESSED_CHUNK_NAME SET TABLESPACE tablespace1;
\set ON_ERROR_STOP 1
SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';

-- ensure that both compressed and uncompressed chunks moved
ALTER TABLE :UNCOMPRESSED_CHUNK_NAME SET TABLESPACE tablespace1;

            

Reported by SQLint.

              SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';

\set ON_ERROR_STOP 0
SELECT move_chunk(chunk=>:'COMPRESSED_CHUNK_NAME', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1',  reorder_index=>'_timescaledb_internal."compress_hyper_2_28_chunk__compressed_hypertable_2_b__ts_meta_s"');
\set ON_ERROR_STOP 1

-- ensure that both compressed and uncompressed chunks moved
SELECT move_chunk(chunk=>:'UNCOMPRESSED_CHUNK_NAME', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1',  reorder_index=>'_timescaledb_internal."_hyper_1_1_chunk_test1_Time_idx"');

            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
SELECT move_chunk(chunk=>:'COMPRESSED_CHUNK_NAME', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1',  reorder_index=>'_timescaledb_internal."compress_hyper_2_28_chunk__compressed_hypertable_2_b__ts_meta_s"');
\set ON_ERROR_STOP 1

-- ensure that both compressed and uncompressed chunks moved
SELECT move_chunk(chunk=>:'UNCOMPRESSED_CHUNK_NAME', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1',  reorder_index=>'_timescaledb_internal."_hyper_1_1_chunk_test1_Time_idx"');
SELECT tablename
FROM pg_tables WHERE tablespace = 'tablespace1';

            

Reported by SQLint.

              SELECT chunk.schema_name|| '.' || chunk.table_name as "UNCOMPRESSED_CHUNK_NAME"
FROM _timescaledb_catalog.chunk chunk
INNER JOIN _timescaledb_catalog.hypertable hypertable ON (chunk.hypertable_id = hypertable.id)
WHERE hypertable.table_name like 'test1' ORDER BY chunk.id LIMIT 1 \gset

DROP TABLE :UNCOMPRESSED_CHUNK_NAME;

--should decrease #chunks both compressed and decompressed
SELECT count(*) as count_chunks_uncompressed

            

Reported by SQLint.

              SELECT chunk.schema_name|| '.' || chunk.table_name as "UNCOMPRESSED_CHUNK_NAME"
FROM _timescaledb_catalog.chunk chunk
INNER JOIN _timescaledb_catalog.hypertable hypertable ON (chunk.hypertable_id = hypertable.id)
WHERE hypertable.table_name like 'test1' ORDER BY chunk.id LIMIT 1 \gset

SELECT chunk.schema_name|| '.' || chunk.table_name as "COMPRESSED_CHUNK_NAME"
FROM _timescaledb_catalog.chunk chunk
INNER JOIN _timescaledb_catalog.hypertable comp_hyper ON (chunk.hypertable_id = comp_hyper.id)
INNER JOIN _timescaledb_catalog.hypertable uncomp_hyper ON (comp_hyper.id = uncomp_hyper.compressed_hypertable_id)

            

Reported by SQLint.

              );
CREATE INDEX ON PUBLIC."testNs" (device_id, "timeCustom" DESC NULLS LAST) WHERE device_id IS NOT NULL;

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA "testNs" AUTHORIZATION :ROLE_DEFAULT_PERM_USER;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
SELECT * FROM create_hypertable('"public"."testNs"', 'timeCustom', 'device_id', 2, associated_schema_name=>'testNs' );

\c :TEST_DBNAME

            

Reported by SQLint.

              
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE SCHEMA "testNs" AUTHORIZATION :ROLE_DEFAULT_PERM_USER;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
SELECT * FROM create_hypertable('"public"."testNs"', 'timeCustom', 'device_id', 2, associated_schema_name=>'testNs' );

\c :TEST_DBNAME
INSERT INTO PUBLIC."testNs"("timeCustom", device_id, series_0, series_1) VALUES
('2009-11-12T01:00:00+00:00', 'dev1', 1.5, 1),

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
SELECT * FROM create_hypertable('"public"."testNs"', 'timeCustom', 'device_id', 2, associated_schema_name=>'testNs' );

\c :TEST_DBNAME
INSERT INTO PUBLIC."testNs"("timeCustom", device_id, series_0, series_1) VALUES
('2009-11-12T01:00:00+00:00', 'dev1', 1.5, 1),
('2009-11-12T01:00:00+00:00', 'dev1', 1.5, 2),
('2009-11-10T23:00:02+00:00', 'dev1', 2.5, 3);


            

Reported by SQLint.

              
SET client_min_messages = WARNING;

\echo 'The next 2 queries will differ in output between UTC and EST since the mod is on the 100th hour UTC'
SET timezone = 'UTC';
SELECT date_group("timeCustom", '100 days') AS time, sum(series_0)
FROM PUBLIC."testNs" GROUP BY time ORDER BY time ASC;

SET timezone = 'EST';

            

Reported by SQLint.

              SELECT date_group("timeCustom", '100 days') AS time, sum(series_0)
FROM PUBLIC."testNs" GROUP BY time ORDER BY time ASC;

\echo 'The rest of the queries will be the same in output between UTC and EST'
SET timezone = 'UTC';
SELECT date_group("timeCustom", '1 day') AS time, sum(series_0)
FROM PUBLIC."testNs" GROUP BY time ORDER BY time ASC;

SET timezone = 'EST';

            

Reported by SQLint.

              ------------------------------------
-- Test time conversion functions --
------------------------------------
\set ON_ERROR_STOP 0

SET timezone = 'UTC';

-- Conversion to timestamp using Postgres built-in function taking
-- double. Gives inaccurate result on Postgres <= 9.6.2. Accurate on

            

Reported by SQLint.

              -- Julian day zero is -210866803200000000 microseconds from UNIX epoch
SELECT _timescaledb_internal.to_timestamp(-210866803200000000);

\set VERBOSITY default
-- Going beyond Julian day zero should give out-of-range error
SELECT _timescaledb_internal.to_timestamp(-210866803200000001);

-- Lower bound on date (should return the Julian day zero UNIX timestamp above)
SELECT _timescaledb_internal.to_unix_microseconds('4714-11-24 00:00:00+00 BC');

            

Reported by SQLint.

              SELECT time_bucket('1 hour', DATE '2012-01-01');
SELECT time_bucket('25 hour', DATE '2012-01-01');

\set ON_ERROR_STOP 1

SELECT time_bucket(INTERVAL '1 day', TIMESTAMP '2011-01-02 01:01:01');

SELECT time, time_bucket(INTERVAL '2 day ', time)
FROM unnest(ARRAY[

            

Reported by SQLint.

                  INTERVAL '2 day'
    ]) AS int_def;

\set ON_ERROR_STOP 0
SELECT time_bucket(INTERVAL '1 year',TIMESTAMP '2011-01-02 01:01:01.111');
SELECT time_bucket(INTERVAL '1 month',TIMESTAMP '2011-01-02 01:01:01.111');
\set ON_ERROR_STOP 1

SELECT time, time_bucket(INTERVAL '5 minute', time)

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
SELECT time_bucket(INTERVAL '1 year',TIMESTAMP '2011-01-02 01:01:01.111');
SELECT time_bucket(INTERVAL '1 month',TIMESTAMP '2011-01-02 01:01:01.111');
\set ON_ERROR_STOP 1

SELECT time, time_bucket(INTERVAL '5 minute', time)
FROM unnest(ARRAY[
    TIMESTAMP '1970-01-01 00:59:59.999999',
    TIMESTAMP '1970-01-01 01:01:00',

            

Reported by SQLint.

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

\ir include/cluster_test_setup.sql

BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec

            

Reported by SQLint.

              \ir include/cluster_test_setup.sql

BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

-- Show chunk indexes

            

Reported by SQLint.

              SELECT * FROM test.show_indexes('_timescaledb_internal._hyper_1_1_chunk');
SELECT * FROM test.show_indexes('_timescaledb_internal._hyper_1_2_chunk');

\set ON_ERROR_STOP 0
-- cannot reorder a if with no index of the first call
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', verbose => TRUE);
\set ON_ERROR_STOP 1

BEGIN;

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- cannot reorder a if with no index of the first call
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', verbose => TRUE);
\set ON_ERROR_STOP 1

BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec

            

Reported by SQLint.

              \set ON_ERROR_STOP 1

BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

-- reorder a chunk directly with an chunk index

            

Reported by SQLint.

              -- reorder a chunk directly with an chunk index
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', '_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx', TRUE);
BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

-- reorder a chunk without an index

            

Reported by SQLint.

              -- reorder a chunk without an index
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', verbose => TRUE);
BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

-- reorder a chunk directly with a hypertable index

            

Reported by SQLint.

              -- reorder a chunk directly with a hypertable index
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', 'cluster_test_time_idx', TRUE);
BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

\set ON_ERROR_STOP 0

            

Reported by SQLint.

              SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec
COMMIT;

\set ON_ERROR_STOP 0
-- other chunks in the hypertable are still not clustered
SELECT reorder_chunk('_timescaledb_internal._hyper_1_1_chunk', verbose => TRUE);

-- cannot cluster using another chunk's index
SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk', '_timescaledb_internal._hyper_1_1_chunk_cluster_test_time_idx', TRUE);

            

Reported by SQLint.

              SELECT reorder_chunk('_timescaledb_internal._hyper_1_2_chunk',  verbose => TRUE);
END;

\set ON_ERROR_STOP 1

BEGIN;
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', seqscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', indexscan => true) \gexec
SELECT * FROM ensure_scans_work('cluster_test', should_output => 'expected', bitmapscan => true) \gexec

            

Reported by SQLint.

              -- 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;
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\o
\set ECHO all

            

Reported by SQLint.

              
-- Need to be super user to create extension and add data nodes
\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER;
\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\o
\set ECHO all


            

Reported by SQLint.

              -- data node is attached to an existing table.
SELECT * FROM add_data_node('data4', host => 'localhost', database => :'DN_DBNAME_4');

\set ON_ERROR_STOP 0
SELECT * FROM test.remote_exec(NULL, format($$
  SELECT has_table_privilege('%s', 'conditions', 'SELECT') AS "SELECT"
       , has_table_privilege('%s', 'conditions', 'DELETE') AS "DELETE"
       , has_table_privilege('%s', 'conditions', 'INSERT') AS "INSERT"
       , has_table_privilege('%s', 'conditions', 'UPDATE') AS "UPDATE"

            

Reported by SQLint.

                     , has_table_privilege('%s', 'conditions', 'UPDATE') AS "UPDATE"
       , has_table_privilege('%s', 'conditions', 'TRUNCATE') AS "TRUNCATE";
$$, :'ROLE_2', :'ROLE_2', :'ROLE_2', :'ROLE_2', :'ROLE_2'));
\set ON_ERROR_STOP 1

SELECT * FROM attach_data_node('data4', 'conditions');

INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80

            

Reported by SQLint.

              INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80 - 40
FROM generate_series('2018-12-01 00:00'::timestamp, '2018-12-10 00:00'::timestamp, '1h') AS time;
\z conditions
\z _timescaledb_internal.*chunk

-- Add privileges and show that they propagate to the chunks
GRANT SELECT, INSERT ON conditions TO PUBLIC;
\z conditions

            

Reported by SQLint.

              
-- Add privileges and show that they propagate to the chunks
GRANT SELECT, INSERT ON conditions TO PUBLIC;
\z conditions
\z _timescaledb_internal.*chunk

-- Create some more chunks and show that they also get the privileges.
INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80 - 40

            

Reported by SQLint.

              INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80 - 40
FROM generate_series('2018-12-10 00:00'::timestamp, '2018-12-20 00:00'::timestamp, '1h') AS time;
\z conditions
\z _timescaledb_internal.*chunk

-- Revoke one of the privileges and show that it propagate to the
-- chunks.
REVOKE INSERT ON conditions FROM PUBLIC;

            

Reported by SQLint.

              -- Revoke one of the privileges and show that it propagate to the
-- chunks.
REVOKE INSERT ON conditions FROM PUBLIC;
\z conditions
\z _timescaledb_internal.*chunk

-- Add some more chunks and show that it inherits the grants from the
-- hypertable.
INSERT INTO conditions

            

Reported by SQLint.

              INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80 - 40
FROM generate_series('2018-12-20 00:00'::timestamp, '2018-12-30 00:00'::timestamp, '1h') AS time;
\z conditions
\z _timescaledb_internal.*chunk

-- Change grants of one chunk explicitly and check that it is possible

\z _timescaledb_internal._hyper_3_35_chunk

            

Reported by SQLint.

              
\z _timescaledb_internal._hyper_3_35_chunk
GRANT UPDATE ON _timescaledb_internal._hyper_3_35_chunk TO PUBLIC;
\z _timescaledb_internal._hyper_3_35_chunk
REVOKE SELECT ON _timescaledb_internal._hyper_3_35_chunk FROM PUBLIC;
\z _timescaledb_internal._hyper_3_35_chunk

DROP TABLE conditions;


            

Reported by SQLint.

              -- 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;

\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\ir include/filter_exec.sql

            

Reported by SQLint.

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

\unset ECHO
\o /dev/null
\ir include/remote_exec.sql
\ir include/filter_exec.sql
\o
\set ECHO all

            

Reported by SQLint.

              GRANT USAGE ON FOREIGN SERVER data_node_1, data_node_2, data_node_3 TO PUBLIC;

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

            

Reported by SQLint.

              \o /dev/null
\c :DN_DBNAME_1
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :DN_DBNAME_2
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :DN_DBNAME_3
SET client_min_messages TO ERROR;

            

Reported by SQLint.

              \ir :TEST_SUPPORT_FILE
\c :DN_DBNAME_2
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :DN_DBNAME_3
SET client_min_messages TO ERROR;
\ir :TEST_SUPPORT_FILE
\c :TEST_DBNAME :ROLE_SUPERUSER;
\o

            

Reported by SQLint.

              \ir :TEST_SUPPORT_FILE
\c :DN_DBNAME_3
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

-- test NULL (STRICT function)
SELECT create_distributed_restore_point(NULL);

            

Reported by SQLint.

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

-- test NULL (STRICT function)
SELECT create_distributed_restore_point(NULL);

-- test long restore point name (>= 64 chars)

            

Reported by SQLint.

              SELECT create_distributed_restore_point(NULL);

-- test long restore point name (>= 64 chars)
\set ON_ERROR_STOP 0
SELECT create_distributed_restore_point('0123456789012345678901234567890123456789012345678901234567890123456789');
\set ON_ERROR_STOP 1

-- test super user check
\set ON_ERROR_STOP 0

            

Reported by SQLint.

              -- test long restore point name (>= 64 chars)
\set ON_ERROR_STOP 0
SELECT create_distributed_restore_point('0123456789012345678901234567890123456789012345678901234567890123456789');
\set ON_ERROR_STOP 1

-- test super user check
\set ON_ERROR_STOP 0
SET ROLE :ROLE_1;
SELECT create_distributed_restore_point('test');

            

Reported by SQLint.

              ---------------------------------------------------
-- Test compression on a distributed hypertable
---------------------------------------------------
\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER

\set DATA_NODE_1 :TEST_DBNAME _1
\set DATA_NODE_2 :TEST_DBNAME _2
\set DATA_NODE_3 :TEST_DBNAME _3


            

Reported by SQLint.

              ORDER BY 1;

SELECT * FROM timescaledb_information.compression_settings order by attname;
\x
SELECT * FROM _timescaledb_catalog.hypertable
WHERE table_name = 'compressed';
\x

SELECT test.remote_exec(NULL, $$

            

Reported by SQLint.

              \x
SELECT * FROM _timescaledb_catalog.hypertable
WHERE table_name = 'compressed';
\x

SELECT test.remote_exec(NULL, $$
SELECT table_name, compressed_hypertable_id
FROM _timescaledb_catalog.hypertable
WHERE table_name = 'compressed';

            

Reported by SQLint.

              SELECT table_name, compressed_hypertable_id
FROM _timescaledb_catalog.hypertable
WHERE table_name = 'compressed';
$$);

-- There should be no compressed chunks
SELECT * from chunk_compression_stats( 'compressed')
ORDER BY chunk_name, node_name;


            

Reported by SQLint.

              ORDER BY chunk
LIMIT 1;

\x
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'compressed';
SELECT * from timescaledb_information.chunks 
ORDER BY hypertable_name, chunk_name;
SELECT * from timescaledb_information.dimensions 

            

Reported by SQLint.

              ORDER BY hypertable_name, chunk_name;
SELECT * from timescaledb_information.dimensions 
ORDER BY hypertable_name, dimension_number;
\x

SELECT * FROM chunks_detailed_size('compressed'::regclass) 
ORDER BY chunk_name, node_name;
SELECT * FROM hypertable_detailed_size('compressed'::regclass) ORDER BY node_name;


            

Reported by SQLint.

              
--TEST 1--
--cannot set policy without enabling compression --
\set ON_ERROR_STOP 0
select add_compression_policy('conditions', '60d'::interval);
\set ON_ERROR_STOP 1

-- TEST2 --
--add a policy to compress chunks --

            

Reported by SQLint.

              --cannot set policy without enabling compression --
\set ON_ERROR_STOP 0
select add_compression_policy('conditions', '60d'::interval);
\set ON_ERROR_STOP 1

-- TEST2 --
--add a policy to compress chunks --
alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'time');
insert into conditions

            

Reported by SQLint.

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

select add_compression_policy('conditions', '60d'::interval) AS compressjob_id
\gset

select * from _timescaledb_config.bgw_job where id = :compressjob_id;
select * from alter_job(:compressjob_id, schedule_interval=>'1s');
select * from _timescaledb_config.bgw_job where id >= 1000 ORDER BY id;
insert into conditions

            

Reported by SQLint.

              
-- TEST 4 --
--cannot set another policy
\set ON_ERROR_STOP 0
select add_compression_policy('conditions', '60d'::interval, if_not_exists=>true);
select add_compression_policy('conditions', '60d'::interval);
select add_compression_policy('conditions', '30d'::interval, if_not_exists=>true);
\set ON_ERROR_STOP 1


            

Reported by SQLint.

                     extversion >= '2.0.0' AS has_create_mat_view,
       extversion >= '2.0.0' AS has_continuous_aggs_policy
  FROM pg_extension
 WHERE extname = 'timescaledb' \gset

\if :has_create_mat_view
\set :DROP_CAGG DROP MATERIALIZED VIEW
\else
\set :DROP_CAGG DROP VIEW

            

Reported by SQLint.

              
\if :has_continuous_aggs_policy
SELECT remove_continuous_aggregate_policy('mat_drop');
\endif

\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_drop;
\else
DROP VIEW mat_drop;

            

Reported by SQLint.

              
\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_drop;
\else
DROP VIEW mat_drop;
\endif

DROP TABLE drop_test;


            

Reported by SQLint.

              DROP MATERIALIZED VIEW mat_drop;
\else
DROP VIEW mat_drop;
\endif

DROP TABLE drop_test;

\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_conflict;

            

Reported by SQLint.

              
DROP TABLE drop_test;

\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_conflict;
\else
DROP VIEW mat_conflict;
\endif


            

Reported by SQLint.

              
\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_conflict;
\else
DROP VIEW mat_conflict;
\endif

DROP TABLE conflict_test;
\if :has_create_mat_view

            

Reported by SQLint.

              DROP MATERIALIZED VIEW mat_conflict;
\else
DROP VIEW mat_conflict;
\endif

DROP TABLE conflict_test;
\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_inttime;
\else

            

Reported by SQLint.

              \endif

DROP TABLE conflict_test;
\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_inttime;
\else
DROP VIEW mat_inttime;
\endif


            

Reported by SQLint.

              DROP TABLE conflict_test;
\if :has_create_mat_view
DROP MATERIALIZED VIEW mat_inttime;
\else
DROP VIEW mat_inttime;
\endif

DROP FUNCTION integer_now_test;
DROP TABLE int_time_test;

            

Reported by SQLint.

              DROP MATERIALIZED VIEW mat_inttime;
\else
DROP VIEW mat_inttime;
\endif

DROP FUNCTION integer_now_test;
DROP TABLE int_time_test;

\if :has_create_mat_view

            

Reported by SQLint.

              SELECT create_hypertable('part_new_convert1', 'time', 'temp', 2);

INSERT INTO part_new_convert1 VALUES ('2017-03-22T09:18:23', 1.0, 2);
\set ON_ERROR_STOP 0
-- Changing the type of a hash-partitioned column should not be supported
ALTER TABLE part_new_convert1 ALTER COLUMN temp TYPE numeric;
\set ON_ERROR_STOP 1

-- Should be able to change if not hash partitioned though

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- Changing the type of a hash-partitioned column should not be supported
ALTER TABLE part_new_convert1 ALTER COLUMN temp TYPE numeric;
\set ON_ERROR_STOP 1

-- Should be able to change if not hash partitioned though
ALTER TABLE part_new_convert1 ALTER COLUMN time TYPE timestamp;

SELECT * FROM test.show_columnsp('_timescaledb_internal._hyper_3_%_chunk');

            

Reported by SQLint.

              CREATE TABLE part_add_dim(time timestamptz, temp float8, device int, location int);
SELECT create_hypertable('part_add_dim', 'time', 'temp', 2);

\set ON_ERROR_STOP 0
SELECT add_dimension('part_add_dim', 'location', 2, partitioning_func => 'bad_func');
\set ON_ERROR_STOP 1

SELECT add_dimension('part_add_dim', 'location', 2, partitioning_func => '_timescaledb_internal.get_partition_for_key');
SELECT * FROM _timescaledb_catalog.dimension;

            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
SELECT add_dimension('part_add_dim', 'location', 2, partitioning_func => 'bad_func');
\set ON_ERROR_STOP 1

SELECT add_dimension('part_add_dim', 'location', 2, partitioning_func => '_timescaledb_internal.get_partition_for_key');
SELECT * FROM _timescaledb_catalog.dimension;

-- Test that we support custom SQL-based partitioning functions and

            

Reported by SQLint.

              CREATE TABLE hyper_with_index(time timestamptz, temp float, device int);
CREATE UNIQUE INDEX temp_index ON hyper_with_index(temp);

\set ON_ERROR_STOP 0
SELECT create_hypertable('hyper_with_index', 'time');
SELECT create_hypertable('hyper_with_index', 'time', 'device', 2);
SELECT create_hypertable('hyper_with_index', 'time', 'temp', 2);
\set ON_ERROR_STOP 1


            

Reported by SQLint.

              SELECT create_hypertable('hyper_with_index', 'time');
SELECT create_hypertable('hyper_with_index', 'time', 'device', 2);
SELECT create_hypertable('hyper_with_index', 'time', 'temp', 2);
\set ON_ERROR_STOP 1

DROP INDEX temp_index;
CREATE UNIQUE INDEX time_index ON hyper_with_index(time);

\set ON_ERROR_STOP 0

            

Reported by SQLint.

              DROP INDEX temp_index;
CREATE UNIQUE INDEX time_index ON hyper_with_index(time);

\set ON_ERROR_STOP 0
-- should error because device not in index
SELECT create_hypertable('hyper_with_index', 'time', 'device', 4);
\set ON_ERROR_STOP 1
SELECT create_hypertable('hyper_with_index', 'time');
-- make sure user created index is used.

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- should error because device not in index
SELECT create_hypertable('hyper_with_index', 'time', 'device', 4);
\set ON_ERROR_STOP 1
SELECT create_hypertable('hyper_with_index', 'time');
-- make sure user created index is used.
-- not using \d or \d+ because output syntax differs
-- between postgres 9 and postgres 10.
SELECT indexname FROM pg_indexes WHERE tablename = 'hyper_with_index';

            

Reported by SQLint.

              -- not using \d or \d+ because output syntax differs
-- between postgres 9 and postgres 10.
SELECT indexname FROM pg_indexes WHERE tablename = 'hyper_with_index';
\set ON_ERROR_STOP 0
SELECT add_dimension('hyper_with_index', 'device', 4);
\set ON_ERROR_STOP 1

DROP INDEX time_index;
CREATE UNIQUE INDEX time_space_index ON hyper_with_index(time, device);

            

Reported by SQLint.

              SELECT indexname FROM pg_indexes WHERE tablename = 'hyper_with_index';
\set ON_ERROR_STOP 0
SELECT add_dimension('hyper_with_index', 'device', 4);
\set ON_ERROR_STOP 1

DROP INDEX time_index;
CREATE UNIQUE INDEX time_space_index ON hyper_with_index(time, device);
SELECT add_dimension('hyper_with_index', 'device', 4);


            

Reported by SQLint.