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

\c :TEST_DBNAME :ROLE_SUPERUSER

CREATE TABLE conditions(
    time TIMESTAMPTZ NOT NULL,
    device INTEGER,
    temperature FLOAT

            

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_1_1_chunk
GRANT UPDATE ON _timescaledb_internal._hyper_1_1_chunk TO PUBLIC;

            

Reported by SQLint.

              -- Change grants of one chunk explicitly and check that it is possible
\z _timescaledb_internal._hyper_1_1_chunk
GRANT UPDATE ON _timescaledb_internal._hyper_1_1_chunk TO PUBLIC;
\z _timescaledb_internal._hyper_1_1_chunk
REVOKE SELECT ON _timescaledb_internal._hyper_1_1_chunk FROM PUBLIC;
\z _timescaledb_internal._hyper_1_1_chunk

-- Check that revoking a permission first on the chunk and then on the
-- hypertable that was added through the hypertable (INSERT and

            

Reported by SQLint.

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

-- Check that revoking a permission first on the chunk and then on the
-- hypertable that was added through the hypertable (INSERT and
-- SELECT, in this case) still do not copy permissions from the
-- hypertable (so there should not be a select permission to public on

            

Reported by SQLint.

              -- hypertable (so there should not be a select permission to public on
-- the chunk but there should be one on the hypertable).
GRANT INSERT ON conditions TO PUBLIC;
\z conditions
\z _timescaledb_internal._hyper_1_2_chunk
REVOKE SELECT ON _timescaledb_internal._hyper_1_2_chunk FROM PUBLIC;
REVOKE INSERT ON conditions FROM PUBLIC;
\z conditions
\z _timescaledb_internal._hyper_1_2_chunk

            

Reported by SQLint.

              \z _timescaledb_internal._hyper_1_2_chunk
REVOKE SELECT ON _timescaledb_internal._hyper_1_2_chunk FROM PUBLIC;
REVOKE INSERT ON conditions FROM PUBLIC;
\z conditions
\z _timescaledb_internal._hyper_1_2_chunk

-- Check that granting permissions through hypertable does not remove
-- separate grants on chunk.
GRANT UPDATE ON _timescaledb_internal._hyper_1_3_chunk TO PUBLIC;

            

Reported by SQLint.

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

\set ON_ERROR_STOP 0

\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE VIEW hypertable_tablespaces AS
SELECT cls.relname AS hypertable,
       (SELECT spcname FROM pg_tablespace WHERE oid = reltablespace) AS tablespace

            

Reported by SQLint.

              --attach the test name as prefix to allow tests to be executed in
--parallel.
CREATE TABLESPACE tablespace1 OWNER :ROLE_DEFAULT_PERM_USER LOCATION :TEST_TABLESPACE1_PATH;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--assigning a tablespace via the main table should work
CREATE TABLE tspace_2dim(time timestamp, temp float, device text) TABLESPACE tablespace1;
SELECT create_hypertable('tspace_2dim', 'time', 'device', 2);


            

Reported by SQLint.

              --no error if if_not_attached is given
SELECT attach_tablespace('tablespace1', 'tspace_2dim', if_not_attached => true);

\c :TEST_DBNAME :ROLE_SUPERUSER
--Tablespaces are cluster-wide, so we attach the test name as prefix
--to allow tests to be executed in parallel.
CREATE TABLESPACE tablespace2 OWNER :ROLE_DEFAULT_PERM_USER_2 LOCATION :TEST_TABLESPACE2_PATH;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2


            

Reported by SQLint.

              --Tablespaces are cluster-wide, so we attach the test name as prefix
--to allow tests to be executed in parallel.
CREATE TABLESPACE tablespace2 OWNER :ROLE_DEFAULT_PERM_USER_2 LOCATION :TEST_TABLESPACE2_PATH;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2

--attach without permissions on the table should fail
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              --attach without permissions on the table should fail
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--attach without permissions on the tablespace should also fail
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

\c :TEST_DBNAME :ROLE_SUPERUSER

            

Reported by SQLint.

              --attach without permissions on the tablespace should also fail
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

\c :TEST_DBNAME :ROLE_SUPERUSER
GRANT :ROLE_DEFAULT_PERM_USER_2 TO :ROLE_DEFAULT_PERM_USER;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--should work with permissions on both the table and the tablespace
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

            

Reported by SQLint.

              
\c :TEST_DBNAME :ROLE_SUPERUSER
GRANT :ROLE_DEFAULT_PERM_USER_2 TO :ROLE_DEFAULT_PERM_USER;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--should work with permissions on both the table and the tablespace
SELECT attach_tablespace('tablespace2', 'tspace_2dim');

SELECT * FROM _timescaledb_catalog.tablespace;

            

Reported by SQLint.

              --indexes should inherit the tablespace of their chunk
SELECT * FROM test.show_indexesp('_timescaledb_internal._hyper%_chunk');

\x
SELECT * FROM timescaledb_information.hypertables
ORDER BY hypertable_schema, hypertable_name;
SELECT * FROM timescaledb_information.chunks ORDER BY chunk_name;
\x
--

            

Reported by SQLint.

              SELECT * FROM timescaledb_information.hypertables
ORDER BY hypertable_schema, hypertable_name;
SELECT * FROM timescaledb_information.chunks ORDER BY chunk_name;
\x
--
SET ROLE :ROLE_DEFAULT_PERM_USER_2;
CREATE TABLE tspace_1dim(time timestamp, temp float, device text);
SELECT create_hypertable('tspace_1dim', 'time');
--user doesn't have permission on tablespace1 --> error

            

Reported by SQLint.

              SELECT detach_tablespace('tablespace2', 'tspace_2dim');
SELECT detach_tablespaces('tspace_2dim');

\c :TEST_DBNAME :ROLE_SUPERUSER
-- PERM_USER_2 owns tablespace2, and PERM_USER owns the table
-- 'tspace_2dim', which has tablespace2 attached. Revoking PERM_USER_2
-- FROM PERM_USER should therefore fail
REVOKE :ROLE_DEFAULT_PERM_USER_2 FROM :ROLE_DEFAULT_PERM_USER;
SET ROLE :ROLE_DEFAULT_PERM_USER_2;

            

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 _timescaledb_internal.ts_segment_meta_min_max_append(internal, ANYELEMENT)
   RETURNS internal
   AS :TSL_MODULE_PATHNAME, 'ts_segment_meta_min_max_append'
   LANGUAGE C IMMUTABLE PARALLEL SAFE;

            

Reported by SQLint.

                  FINALFUNC_EXTRA
);

\ir include/rand_generator.sql

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE metric (i int);
insert into metric select i from generate_series(1, 10) i;

            

Reported by SQLint.

              CREATE TABLE metric (i int);
insert into metric select i from generate_series(1, 10) i;

\set TYPE int
\set TABLE metric
\ir include/compression_test_segment_meta.sql

----NULL tests
--First

            

Reported by SQLint.

              --First
truncate metric;
insert into metric select NULLIF(i,1) from generate_series(1, 10) i;
\ir include/compression_test_segment_meta.sql

--Last
truncate metric;
insert into metric select NULLIF(i,10) from generate_series(1, 10) i;
\ir include/compression_test_segment_meta.sql

            

Reported by SQLint.

              --Last
truncate metric;
insert into metric select NULLIF(i,10) from generate_series(1, 10) i;
\ir include/compression_test_segment_meta.sql

--Middle
truncate metric;
insert into metric select NULLIF(i,5) from generate_series(1, 10) i;
\ir include/compression_test_segment_meta.sql

            

Reported by SQLint.

              --Middle
truncate metric;
insert into metric select NULLIF(i,5) from generate_series(1, 10) i;
\ir include/compression_test_segment_meta.sql

--All NULLS should return null object
truncate metric;
insert into metric select NULL from generate_series(1, 10) i;
SELECT

            

Reported by SQLint.

                        ORDER BY gen_rand_minstd()
        ) sub;

\set TYPE text
\set TABLE base_texts
\ir include/compression_test_segment_meta.sql

--toasted text
DROP TABLE base_texts;

            

Reported by SQLint.

                    FROM pg_class c
      WHERE relname = 'base_texts';

\ir include/compression_test_segment_meta.sql

--name is a fixed-length pass by reference type
CREATE TABLE base_name AS SELECT
    item::name as i
    FROM

            

Reported by SQLint.

                        ORDER BY gen_rand_minstd()
        ) sub;

\set TYPE name
\set TABLE base_name
\ir include/compression_test_segment_meta.sql


--array

            

Reported by SQLint.

                        ORDER BY gen_rand_minstd()
        ) sub;

\set TYPE text[]
\set TABLE text_array
\ir include/compression_test_segment_meta.sql

--Points doesn't have an ordering so make sure it errors
CREATE TABLE points AS SELECT

            

Reported by SQLint.

              SELECT * FROM test.set_memory_cache_size('2GB');

-- test NULL handling
\set ON_ERROR_STOP 0
SELECT * FROM set_adaptive_chunking(NULL,NULL);
\set ON_ERROR_STOP 1

CREATE TABLE test_adaptive(time timestamptz, temp float, location int);


            

Reported by SQLint.

              -- test NULL handling
\set ON_ERROR_STOP 0
SELECT * FROM set_adaptive_chunking(NULL,NULL);
\set ON_ERROR_STOP 1

CREATE TABLE test_adaptive(time timestamptz, temp float, location int);

\set ON_ERROR_STOP 0
-- Bad signature of sizing func should fail

            

Reported by SQLint.

              
CREATE TABLE test_adaptive(time timestamptz, temp float, location int);

\set ON_ERROR_STOP 0
-- Bad signature of sizing func should fail
SELECT create_hypertable('test_adaptive', 'time',
                         chunk_target_size => '1MB',
                         chunk_sizing_func => 'bad_calculate_chunk_interval');
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              SELECT create_hypertable('test_adaptive', 'time',
                         chunk_target_size => '1MB',
                         chunk_sizing_func => 'bad_calculate_chunk_interval');
\set ON_ERROR_STOP 1

-- Setting sizing func with correct signature should work
SELECT create_hypertable('test_adaptive', 'time',
                         chunk_target_size => '1MB',
                         chunk_sizing_func => 'calculate_chunk_interval');

            

Reported by SQLint.

              SELECT table_name, chunk_sizing_func_schema, chunk_sizing_func_name, chunk_target_size
FROM _timescaledb_catalog.hypertable;

\set ON_ERROR_STOP 0
-- Setting NULL func should fail
SELECT * FROM set_adaptive_chunking('test_adaptive', '1MB', NULL);
\set ON_ERROR_STOP 1

-- Setting NULL size disables adaptive chunking

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- Setting NULL func should fail
SELECT * FROM set_adaptive_chunking('test_adaptive', '1MB', NULL);
\set ON_ERROR_STOP 1

-- Setting NULL size disables adaptive chunking
SELECT * FROM set_adaptive_chunking('test_adaptive', NULL);
SELECT table_name, chunk_sizing_func_schema, chunk_sizing_func_name, chunk_target_size
FROM _timescaledb_catalog.hypertable;

            

Reported by SQLint.

                              '2017-03-07T18:18:03+00'::timestamptz,
                '2 minutes') as time;

\x
SELECT chunk_name, range_start, range_end 
FROM  timescaledb_information.chunks
WHERE hypertable_name = 'test_adaptive_space' ORDER BY chunk_name;
SELECT *
FROM  timescaledb_information.dimensions

            

Reported by SQLint.

              SELECT *
FROM  timescaledb_information.dimensions
WHERE hypertable_name = 'test_adaptive_space' ORDER BY dimension_number;
\x
SELECT *
FROM chunks_detailed_size('test_adaptive_space') ORDER BY chunk_name;
SELECT id, hypertable_id, interval_length FROM _timescaledb_catalog.dimension;

-- A previous version stopped working as soon as hypertable_id stopped being

            

Reported by SQLint.

                                       create_default_indexes => true);
INSERT INTO test_adaptive_after_multiple_dims VALUES('2018-01-01T00:00:00+00'::timestamptz, 0.0, 5);

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
\set ON_ERROR_STOP 0
SELECT * FROM set_adaptive_chunking('test_adaptive', '2MB');
\set ON_ERROR_STOP 1

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              \c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
\set ON_ERROR_STOP 0
SELECT * FROM set_adaptive_chunking('test_adaptive', '2MB');
\set ON_ERROR_STOP 1

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- Now make sure renaming schema gets propagated to the func_schema
DROP TABLE test_adaptive;


            

Reported by SQLint.

              END
$$;

\set ON_ERROR_STOP 0
-- test bad input
SELECT add_job(NULL, '1h');
SELECT add_job(0, '1h');
-- this will return an error about Oid 4294967295
-- while regproc is unsigned int postgres has an implicit cast from int to regproc

            

Reported by SQLint.

              SELECT add_job('invalid_func', '1h');
SELECT add_job('custom_func', NULL);
SELECT add_job('custom_func', 'invalid interval');
\set ON_ERROR_STOP 1

SELECT add_job('custom_func','1h', config:='{"type":"function"}'::jsonb);
SELECT add_job('custom_proc','1h', config:='{"type":"procedure"}'::jsonb);
SELECT add_job('custom_proc2','1h', config:= '{"type":"procedure"}'::jsonb);


            

Reported by SQLint.

              -- check for corrects counts in telemetry
SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_user_defined_actions');

\set ON_ERROR_STOP 0
-- test bad input
CALL run_job(NULL);
CALL run_job(-1);
\set ON_ERROR_STOP 1


            

Reported by SQLint.

              -- test bad input
CALL run_job(NULL);
CALL run_job(-1);
\set ON_ERROR_STOP 1

CALL run_job(1000);
CALL run_job(1001);
CALL run_job(1002);
CALL run_job(1003);

            

Reported by SQLint.

              SELECT * FROM custom_log ORDER BY job_id, extra;


\set ON_ERROR_STOP 0
-- test bad input
SELECT delete_job(NULL);
SELECT delete_job(-1);
\set ON_ERROR_STOP 1


            

Reported by SQLint.

              -- test bad input
SELECT delete_job(NULL);
SELECT delete_job(-1);
\set ON_ERROR_STOP 1

SELECT delete_job(1000);
SELECT delete_job(1001);
SELECT delete_job(1002);
SELECT delete_job(1003);

            

Reported by SQLint.

              -- check jobs got removed
SELECT count(*) FROM timescaledb_information.jobs WHERE job_id >= 1000;

\c :TEST_DBNAME :ROLE_SUPERUSER

\set ON_ERROR_STOP 0
-- test bad input
SELECT alter_job(NULL, if_exists => false);
SELECT alter_job(-1, if_exists => false);

            

Reported by SQLint.

              -- test bad input
SELECT alter_job(NULL, if_exists => false);
SELECT alter_job(-1, if_exists => false);
\set ON_ERROR_STOP 1
-- test bad input but don't fail
SELECT alter_job(NULL, if_exists => true);
SELECT alter_job(-1, if_exists => true);

-- test altering job with NULL config

            

Reported by SQLint.

              SELECT * FROM timescaledb_information.jobs WHERE job_id = 1;

--test for #2793
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- background workers are disabled, so the job will not run --
SELECT add_job( proc=>'custom_func',
     schedule_interval=>'1h', initial_start =>'2018-01-01 10:00:00-05');

SELECT job_id, next_start, scheduled, schedule_interval 

            

Reported by SQLint.

              
SELECT job_id, next_start, scheduled, schedule_interval 
FROM timescaledb_information.jobs WHERE job_id > 1000;
\x
SELECT * FROM timescaledb_information.job_stats WHERE job_id > 1000;
\x

            

Reported by SQLint.

              		memset(NameStr(default_associated_table_prefix), '\0', NAMEDATALEN);
		Assert(replication_factor >= 0);
		if (replication_factor == 0)
			snprintf(NameStr(default_associated_table_prefix),
					 NAMEDATALEN,
					 DEFAULT_ASSOCIATED_TABLE_PREFIX_FORMAT,
					 fd.id);
		else
			snprintf(NameStr(default_associated_table_prefix),

            

Reported by FlawFinder.

              					 DEFAULT_ASSOCIATED_TABLE_PREFIX_FORMAT,
					 fd.id);
		else
			snprintf(NameStr(default_associated_table_prefix),
					 NAMEDATALEN,
					 DEFAULT_ASSOCIATED_DISTRIBUTED_TABLE_PREFIX_FORMAT,
					 fd.id);
		namestrcpy(&fd.associated_table_prefix, NameStr(default_associated_table_prefix));
	}

            

Reported by FlawFinder.

              	Assert(!nulls[AttrNumberGetAttrOffset(Anum_hypertable_compression_state)]);

	fd->id = DatumGetInt32(values[AttrNumberGetAttrOffset(Anum_hypertable_id)]);
	memcpy(&fd->schema_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_schema_name)]),
		   NAMEDATALEN);
	memcpy(&fd->table_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_table_name)]),
		   NAMEDATALEN);

            

Reported by FlawFinder.

              	memcpy(&fd->schema_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_schema_name)]),
		   NAMEDATALEN);
	memcpy(&fd->table_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_table_name)]),
		   NAMEDATALEN);
	memcpy(&fd->associated_schema_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_associated_schema_name)]),
		   NAMEDATALEN);

            

Reported by FlawFinder.

              	memcpy(&fd->table_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_table_name)]),
		   NAMEDATALEN);
	memcpy(&fd->associated_schema_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_associated_schema_name)]),
		   NAMEDATALEN);
	memcpy(&fd->associated_table_prefix,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_associated_table_prefix)]),
		   NAMEDATALEN);

            

Reported by FlawFinder.

              	memcpy(&fd->associated_schema_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_associated_schema_name)]),
		   NAMEDATALEN);
	memcpy(&fd->associated_table_prefix,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_associated_table_prefix)]),
		   NAMEDATALEN);

	fd->num_dimensions =
		DatumGetInt16(values[AttrNumberGetAttrOffset(Anum_hypertable_num_dimensions)]);

            

Reported by FlawFinder.

              	fd->num_dimensions =
		DatumGetInt16(values[AttrNumberGetAttrOffset(Anum_hypertable_num_dimensions)]);

	memcpy(&fd->chunk_sizing_func_schema,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_chunk_sizing_func_schema)]),
		   NAMEDATALEN);
	memcpy(&fd->chunk_sizing_func_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_chunk_sizing_func_name)]),
		   NAMEDATALEN);

            

Reported by FlawFinder.

              	memcpy(&fd->chunk_sizing_func_schema,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_chunk_sizing_func_schema)]),
		   NAMEDATALEN);
	memcpy(&fd->chunk_sizing_func_name,
		   DatumGetName(values[AttrNumberGetAttrOffset(Anum_hypertable_chunk_sizing_func_name)]),
		   NAMEDATALEN);

	fd->chunk_target_size =
		DatumGetInt64(values[AttrNumberGetAttrOffset(Anum_hypertable_chunk_target_size)]);

            

Reported by FlawFinder.

              void
ts_hypertables_rename_schema_name(const char *old_name, const char *new_name)
{
	const char *schema_names[2] = { old_name, new_name };
	Catalog *catalog = ts_catalog_get();

	ScannerCtx scanctx = {
		.table = catalog_get_table_id(catalog, HYPERTABLE),
		.index = InvalidOid,

            

Reported by FlawFinder.

              {
	HypertableDataNode *copy = palloc(sizeof(HypertableDataNode));

	memcpy(copy, node, sizeof(HypertableDataNode));
	return copy;
}

static List *
get_hypertable_data_node_values(const Hypertable *ht, hypertable_data_node_filter filter,

            

Reported by FlawFinder.

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

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

CREATE VIEW cagg_info AS

            

Reported by SQLint.

                FROM pg_class JOIN caggs ON pg_class.oid = caggs.mat_relid;
GRANT SELECT ON cagg_info TO PUBLIC;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE conditions (
      timec       INT       NOT NULL,
      location    TEXT              NOT NULL,
      temperature DOUBLE PRECISION  NULL,

            

Reported by SQLint.

              
CALL refresh_continuous_aggregate(' mat_refresh_test', NULL, NULL);

SELECT id as cagg_job_id FROM _timescaledb_config.bgw_job order by id desc limit 1 \gset
SELECT format('%I.%I', materialization_hypertable_schema, materialization_hypertable_name ) as materialization_hypertable 
FROM timescaledb_information.continuous_aggregates 
WHERE view_name = 'mat_refresh_test' \gset

SELECT mat_hypertable_id FROM _timescaledb_catalog.continuous_agg WHERE user_view_name = 'mat_refresh_test' \gset

            

Reported by SQLint.

              --need both select and trigger permissions to create a materialized view on top of it.
GRANT SELECT, TRIGGER ON conditions_for_perm_check_w_grant TO public;

\c  :TEST_DBNAME :ROLE_SUPERUSER

create schema custom_schema;

CREATE FUNCTION get_constant() RETURNS INTEGER LANGUAGE SQL IMMUTABLE AS
$BODY$

            

Reported by SQLint.

              
REVOKE EXECUTE ON FUNCTION get_constant() FROM PUBLIC;

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
\set ON_ERROR_STOP 0
select from alter_job(:cagg_job_id, max_runtime => NULL);

--make sure that commands fail


            

Reported by SQLint.

              CALL refresh_continuous_aggregate('mat_perm_view_test', NULL, NULL);
SELECT * FROM mat_perm_view_test;

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
--revoke select permissions from role with mat view
REVOKE SELECT ON conditions_for_perm_check_w_grant FROM public;

insert into conditions_for_perm_check_w_grant
select generate_series(100, 130, 10), 'POR', 65, 85, 30, 90, NULL;

            

Reported by SQLint.

              insert into conditions_for_perm_check_w_grant
select generate_series(100, 130, 10), 'POR', 65, 85, 30, 90, NULL;

\c  :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2
--refresh mat view should now fail due to lack of permissions
CALL refresh_continuous_aggregate('mat_perm_view_test', NULL, NULL);

--but the old data will still be there
SELECT * FROM mat_perm_view_test;

            

Reported by SQLint.

              --but the old data will still be there
SELECT * FROM mat_perm_view_test;

\set VERBOSITY default

-- Test that grants and revokes are propagated to the implementation
-- objects, that is, the user view, the partial view, the direct view,
-- and the materialization table.
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              SELECT time, (random() * 30)::int, random() * 80
FROM generate_series('2020-02-01 00:00:00'::timestamptz, '2020-03-01 00:00:00', '1 hour') AS time;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2

CREATE MATERIALIZED VIEW devices_summary
WITH (timescaledb.continuous, timescaledb.materialized_only=true)
AS SELECT time_bucket('1 day', time) AS bucket, device, MAX(temp)
FROM devices GROUP BY bucket, device WITH NO DATA;

            

Reported by SQLint.

              AS SELECT time_bucket('1 day', time) AS bucket, device, MAX(temp)
FROM devices GROUP BY bucket, device WITH NO DATA;

\x on
SELECT * FROM cagg_info WHERE user_view::text = 'devices_summary';
GRANT ALL ON devices_summary TO :ROLE_DEFAULT_PERM_USER;
SELECT * FROM cagg_info WHERE user_view::text = 'devices_summary';
REVOKE SELECT, UPDATE ON devices_summary FROM :ROLE_DEFAULT_PERM_USER;
SELECT * FROM cagg_info WHERE user_view::text = 'devices_summary';

            

Reported by SQLint.

              --
-- Setup for testing bgw jobs ---
--
\c :TEST_DBNAME :ROLE_SUPERUSER
CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(timeout INT = -1) RETURNS VOID
AS :MODULE_PATHNAME LANGUAGE C VOLATILE;

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

            

Reported by SQLint.

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

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

-- Remove any default jobs, e.g., telemetry
DELETE FROM _timescaledb_config.bgw_job WHERE TRUE;

            

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,
    msg TEXT

            

Reported by SQLint.

              -- test drop chunks policy runs for materialized hypertables created for
-- cont. aggregates
-----------------------------------
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

CREATE TABLE drop_chunks_table(time BIGINT, data INTEGER);
SELECT hypertable_id AS drop_chunks_table_nid
    FROM create_hypertable('drop_chunks_table', 'time', chunk_time_interval => 1) \gset


            

Reported by SQLint.

              
CREATE TABLE drop_chunks_table(time BIGINT, data INTEGER);
SELECT hypertable_id AS drop_chunks_table_nid
    FROM create_hypertable('drop_chunks_table', 'time', chunk_time_interval => 1) \gset

CREATE OR REPLACE FUNCTION integer_now_test2() returns bigint LANGUAGE SQL STABLE as $$ SELECT 40::bigint  $$;

SELECT set_integer_now_func('drop_chunks_table', 'integer_now_test2');


            

Reported by SQLint.

              --raw hypertable will have 40 chunks and the mat. hypertable will have 2 and 4
-- chunks respectively
SELECT set_chunk_time_interval('_timescaledb_internal._materialized_hypertable_2', 10);
\set ON_ERROR_STOP 0
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(1, 39) AS i;
\set ON_ERROR_STOP 1
CALL refresh_continuous_aggregate('drop_chunks_view1', NULL, NULL);

--TEST1  specify drop chunks policy on mat. hypertable by

            

Reported by SQLint.

              SELECT set_chunk_time_interval('_timescaledb_internal._materialized_hypertable_2', 10);
\set ON_ERROR_STOP 0
INSERT INTO drop_chunks_table SELECT i, i FROM generate_series(1, 39) AS i;
\set ON_ERROR_STOP 1
CALL refresh_continuous_aggregate('drop_chunks_view1', NULL, NULL);

--TEST1  specify drop chunks policy on mat. hypertable by
-- directly does not work


            

Reported by SQLint.

              --TEST1  specify drop chunks policy on mat. hypertable by
-- directly does not work

\set ON_ERROR_STOP 0
SELECT add_retention_policy( '_timescaledb_internal._materialized_hypertable_2', drop_after => -50) as drop_chunks_job_id1 \gset
\set ON_ERROR_STOP 1

--TEST2  specify drop chunks policy on cont. aggregate
-- integer_now func on raw hypertable is used by the drop

            

Reported by SQLint.

              FROM timescaledb_information.chunks
WHERE hypertable_name = '_materialized_hypertable_2' ORDER BY range_start_integer;

SELECT add_retention_policy( 'drop_chunks_view1', drop_after => 10) as drop_chunks_job_id1 \gset
SELECT alter_job(:drop_chunks_job_id1, schedule_interval => INTERVAL '1 second');
SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(2000000);
SELECT count(c) from show_chunks('drop_chunks_view1') as c ;
SELECT remove_retention_policy('drop_chunks_view1');


            

Reported by SQLint.

              SELECT count(c) from show_chunks('drop_chunks_view1') as c ;
SELECT remove_retention_policy('drop_chunks_view1');

\set ON_ERROR_STOP 0
SELECT remove_retention_policy('unknown');
SELECT remove_retention_policy('1');
\set ON_ERROR_STOP 1

            

Reported by SQLint.