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

\d+ _timescaledb_catalog.hypertable
\d+ _timescaledb_catalog.chunk
\d+ _timescaledb_catalog.dimension
\d+ _timescaledb_catalog.dimension_slice
\d+ _timescaledb_catalog.chunk_constraint
\d+ _timescaledb_catalog.chunk_index

            

Reported by SQLint.

                AND nspname IN ('_timescaledb_catalog', '_timescaledb_config')
ORDER BY schema, name, initpriv;

\di _timescaledb_catalog.*
\ds+ _timescaledb_catalog.*;
\df _timescaledb_internal.*;
\df+ _timescaledb_internal.*;
\df public.*;
\df+ public.*;

            

Reported by SQLint.

              
\di _timescaledb_catalog.*
\ds+ _timescaledb_catalog.*;
\df _timescaledb_internal.*;
\df+ _timescaledb_internal.*;
\df public.*;
\df+ public.*;

\dy

            

Reported by SQLint.

              \di _timescaledb_catalog.*
\ds+ _timescaledb_catalog.*;
\df _timescaledb_internal.*;
\df+ _timescaledb_internal.*;
\df public.*;
\df+ public.*;

\dy
\d+ public.*

            

Reported by SQLint.

              \ds+ _timescaledb_catalog.*;
\df _timescaledb_internal.*;
\df+ _timescaledb_internal.*;
\df public.*;
\df+ public.*;

\dy
\d+ public.*


            

Reported by SQLint.

              \df _timescaledb_internal.*;
\df+ _timescaledb_internal.*;
\df public.*;
\df+ public.*;

\dy
\d+ public.*

\dx+ timescaledb

            

Reported by SQLint.

              \df public.*;
\df+ public.*;

\dy
\d+ public.*

\dx+ timescaledb
SELECT count(*)
  FROM pg_depend

            

Reported by SQLint.

              SELECT * FROM _timescaledb_catalog.chunk_constraint ORDER BY chunk_id, dimension_slice_id, constraint_name;
SELECT index_name FROM _timescaledb_catalog.chunk_index ORDER BY index_name;

\d+ _timescaledb_internal._hyper*

            

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
\ir include/compression_utils.sql
CREATE TYPE customtype;

CREATE OR REPLACE FUNCTION customtype_in(cstring) RETURNS customtype

            

Reported by SQLint.

                FOR TYPE customtype
  USING hash AS OPERATOR 1 =;

\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
  $$
  SELECT * FROM test1 ORDER BY "Time"
  $$ AS "QUERY" \gset

SELECT 'test1' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE timestamptz

            

Reported by SQLint.

              SELECT
  $$
  SELECT * FROM test2 ORDER BY c, "Time"
  $$ AS "QUERY" \gset

SELECT 'test2' AS "HYPERTABLE_NAME" \gset
\ir include/compression_test_hypertable.sql

\set TYPE int

            

Reported by SQLint.

              from test4
group by location ORDER BY location;

SELECT $$ SELECT * FROM test4 ORDER BY timec $$ AS "QUERY" \gset

SELECT 'test4' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE TIMESTAMPTZ

            

Reported by SQLint.

              select generate_series('2018-01-01 00:00'::timestamp, '2018-01-10 00:00'::timestamp, '2 hour'), NULL, gen_rand_minstd();


SELECT $$ SELECT * FROM test5 ORDER BY device_id, time $$ AS "QUERY" \gset

SELECT 'test5' AS "HYPERTABLE_NAME" \gset

\ir include/compression_test_hypertable.sql
\set TYPE TEXT

            

Reported by SQLint.

              INSERT INTO test6 SELECT t, NULL, customtype_in(t::TEXT::cstring)
  FROM generate_series(1, 200) t;

\set QUERY 'SELECT * FROM test6 ORDER BY device_id, time'

\set HYPERTABLE_NAME 'test6'

\ir include/compression_test_hypertable.sql
\set TYPE INT

            

Reported by SQLint.

                FROM generate_series(10, 20) t,
       generate_series('2019/03/01'::DATE, '2019/03/10', '1d') d;

\set QUERY 'SELECT * FROM test7 ORDER BY time, c1'

\set HYPERTABLE_NAME 'test7'

\ir include/compression_test_hypertable.sql
\set TYPE INT

            

Reported by SQLint.

              #define PRINT_MATINTERNAL_NAME(buf, prefix, hypertable_id)                                         \
	do                                                                                             \
	{                                                                                              \
		int ret = snprintf(buf, NAMEDATALEN, prefix, hypertable_id);                               \
		if (ret < 0 || ret > NAMEDATALEN)                                                          \
		{                                                                                          \
			ereport(ERROR,                                                                         \
					(errcode(ERRCODE_INTERNAL_ERROR),                                              \
					 errmsg(" bad materialization internal name")));                               \

            

Reported by FlawFinder.

              mattablecolumninfo_addentry(MatTableColumnInfo *out, Node *input, int original_query_resno)
{
	int matcolno = list_length(out->matcollist) + 1;
	char colbuf[NAMEDATALEN];
	char *colname;
	TargetEntry *part_te = NULL;
	ColumnDef *col;
	Var *var;
	Oid coltype, colcollation;

            

Reported by FlawFinder.

              			CAggTimebucketInfo *origquery_ht, WithClauseResult *with_clause_options)
{
	ObjectAddress mataddress;
	char relnamebuf[NAMEDATALEN];
	MatTableColumnInfo mattblinfo;
	FinalizeQueryInfo finalqinfo;
	CatalogSecurityContext sec_ctx;
	bool is_create_mattbl_index;


            

Reported by FlawFinder.

              	Oid nspid;
	RangeVar *part_rel = NULL, *mat_rel = NULL, *dum_rel = NULL;
	int32 materialize_hypertable_id;
	char trigarg[NAMEDATALEN];
	int ret;
	bool materialized_only =
		DatumGetBool(with_clause_options[ContinuousViewOptionMaterializedOnly].parsed);

	/* assign the column_name aliases in CREATE VIEW to the query. No other modifications to

            

Reported by FlawFinder.

              	do                                                                                             \
	{                                                                                              \
		if (schemaname &&                                                                          \
			strncmp(schemaname, INTERNAL_SCHEMA_NAME, strlen(INTERNAL_SCHEMA_NAME)) == 0)          \
			newuid = ts_catalog_database_info_get()->owner_uid;                                    \
		else                                                                                       \
			newuid = InvalidOid;                                                                   \
		if (newuid != InvalidOid)                                                                  \
		{                                                                                          \

            

Reported by FlawFinder.

              			const char *funcschema = NameStr(part_dimension->fd.integer_now_func_schema);
			const char *funcname = NameStr(part_dimension->fd.integer_now_func);

			if (strlen(funcschema) == 0 || strlen(funcname) == 0)
				ereport(ERROR,
						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
						 errmsg("custom time function required on hypertable \"%s\"",
								get_rel_name(ht->main_table_relid)),
						 errdetail("An integer-based hypertable requires a custom time"

            

Reported by FlawFinder.

              			const char *funcschema = NameStr(part_dimension->fd.integer_now_func_schema);
			const char *funcname = NameStr(part_dimension->fd.integer_now_func);

			if (strlen(funcschema) == 0 || strlen(funcname) == 0)
				ereport(ERROR,
						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
						 errmsg("custom time function required on hypertable \"%s\"",
								get_rel_name(ht->main_table_relid)),
						 errdetail("An integer-based hypertable requires a custom time"

            

Reported by FlawFinder.

              	{
		TargetEntry *te = (TargetEntry *) lfirst(lc);
		TargetEntry *modte = (TargetEntry *) lfirst(lc2);
		if (equal(node, te->expr))
		{
			return (Node *) modte->expr;
		}
	}
	/* didn't find a match in targetlist. If it is an aggregate, create a partialize column for

            

Reported by FlawFinder.

              SELECT * FROM upsert_test;

-- Referencing constraints by name does not yet work on Hypertables. Check for proper error message.
\set ON_ERROR_STOP 0
INSERT INTO upsert_test VALUES ('2017-01-20T09:00:01', 12.3, 'yellow') ON CONFLICT ON CONSTRAINT upsert_test_pkey
DO UPDATE SET temp = 12.3 RETURNING time, temp, color;

-- Test that update generates error on conflicts
INSERT INTO upsert_test VALUES ('2017-01-21T09:00:01', 22.5, 'yellow') RETURNING *;

            

Reported by SQLint.

              -- Test that update generates error on conflicts
INSERT INTO upsert_test VALUES ('2017-01-21T09:00:01', 22.5, 'yellow') RETURNING *;
UPDATE upsert_test SET time = '2017-01-20T09:00:01';
\set ON_ERROR_STOP 1

-- Test with UNIQUE index on multiple columns instead of PRIMARY KEY constraint
CREATE TABLE upsert_test_unique(time timestamp, temp float, color text);
SELECT create_hypertable('upsert_test_unique', 'time');
CREATE UNIQUE INDEX time_color_idx ON upsert_test_unique (time, color);

            

Reported by SQLint.

              INSERT INTO upsert_test_multi_unique VALUES ('2017-01-21T09:00:01', 45.7, 'yellow') ON CONFLICT (time, color)
DO UPDATE SET temp = 45.7;
SELECT * FROM upsert_test_multi_unique ORDER BY time, color DESC;
\set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique VALUES ('2017-01-20T09:00:01', 23.5, 'purple') ON CONFLICT (time, color)
DO UPDATE set temp = 23.5;
\set ON_ERROR_STOP 1

CREATE TABLE upsert_test_space(time timestamp, device_id_1 char(20), to_drop int, temp float, color text);

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique VALUES ('2017-01-20T09:00:01', 23.5, 'purple') ON CONFLICT (time, color)
DO UPDATE set temp = 23.5;
\set ON_ERROR_STOP 1

CREATE TABLE upsert_test_space(time timestamp, device_id_1 char(20), to_drop int, temp float, color text);
--drop two columns; create one.
ALTER TABLE upsert_test_space DROP to_drop;
ALTER TABLE upsert_test_space DROP device_id_1, ADD device_id char(20);

            

Reported by SQLint.

              RETURNING *;

--errors out if used as an arbiter index
\set ON_ERROR_STOP 0
INSERT INTO upsert_test_multi_unique
VALUES ('2017-01-20T09:00:01', 25.9, 'purple')
ON CONFLICT (time, color) DO NOTHING
RETURNING *;
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              VALUES ('2017-01-20T09:00:01', 25.9, 'purple')
ON CONFLICT (time, color) DO NOTHING
RETURNING *;
\set ON_ERROR_STOP 1

--create table with one chunk that has a tup_conv_map and one that does not
--to ensure this, create a chunk before altering the table this chunk will not have a tup_conv_map
CREATE TABLE upsert_test_diffchunk(time timestamp, device_id char(20), to_drop int, temp float, color text);
SELECT create_hypertable('upsert_test_diffchunk', 'time', chunk_time_interval=> interval '1 month');

            

Reported by SQLint.

              --this insert will create an invalid tuple in source_data
--so that future calls to prep_insert_select will fail
INSERT INTO source_data VALUES('2000-01-02',-0.5);
\set ON_ERROR_STOP 0
EXECUTE prep_insert_select;
EXECUTE prep_insert_select;
\set ON_ERROR_STOP 1
DELETE FROM source_data WHERE value <= 0;
EXECUTE prep_insert_select;

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
EXECUTE prep_insert_select;
EXECUTE prep_insert_select;
\set ON_ERROR_STOP 1
DELETE FROM source_data WHERE value <= 0;
EXECUTE prep_insert_select;

PREPARE prep_insert AS INSERT INTO prepared_test VALUES('2000-01-01',0.5) ON CONFLICT (time) DO UPDATE SET value = EXCLUDED.value;


            

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

              CREATE OR REPLACE FUNCTION ts_bgw_params_reset_time(set_time BIGINT = 0, wait BOOLEAN = false) 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;

            

Reported by SQLint.

              DELETE FROM _timescaledb_config.bgw_job;
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.

              SELECT * FROM _timescaledb_config.bgw_job;
SELECT * FROM timescaledb_information.job_stats;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

------------------------------
-- test reorder policy runs --
------------------------------


            

Reported by SQLint.

              SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_reorder_table';

SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies');
select add_reorder_policy('test_reorder_table', 'test_reorder_table_time_idx') as reorder_job_id \gset
SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies');

-- job was created
SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id;


            

Reported by SQLint.

              


\c :TEST_DBNAME :ROLE_SUPERUSER
TRUNCATE bgw_log;
TRUNCATE _timescaledb_internal.bgw_job_stat;
DELETE FROM _timescaledb_config.bgw_job;
SELECT ts_bgw_params_reset_time();
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              TRUNCATE _timescaledb_internal.bgw_job_stat;
DELETE FROM _timescaledb_config.bgw_job;
SELECT ts_bgw_params_reset_time();
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-----------------------------------
-- test drop chunnks policy runs --
-----------------------------------


            

Reported by SQLint.

              SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_drop_chunks_table';

SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies');
SELECT add_retention_policy('test_drop_chunks_table', INTERVAL '4 months') as drop_chunks_job_id \gset
SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies');

SELECT alter_job(:drop_chunks_job_id, schedule_interval => INTERVAL '1 second');

SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id;

            

Reported by SQLint.

              GRANT TRIGGER, INSERT, SELECT, UPDATE ON location TO :ROLE_DEFAULT_PERM_USER_2;
GRANT SELECT, INSERT, UPDATE ON color TO :ROLE_DEFAULT_PERM_USER_2;
GRANT SELECT, INSERT, UPDATE ON vehicles TO :ROLE_DEFAULT_PERM_USER_2;
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2;

CREATE TRIGGER create_vehicle_trigger
    BEFORE INSERT OR UPDATE ON location
    FOR EACH ROW EXECUTE FUNCTION create_vehicle_trigger_fn();


            

Reported by SQLint.

              SELECT * FROM color;

-- switch back to default user to run some dropping tests
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER; 

\set ON_ERROR_STOP 0
-- test that disable trigger is disallowed 
ALTER TABLE location DISABLE TRIGGER create_vehicle_trigger;
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              -- switch back to default user to run some dropping tests
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER; 

\set ON_ERROR_STOP 0
-- test that disable trigger is disallowed 
ALTER TABLE location DISABLE TRIGGER create_vehicle_trigger;
\set ON_ERROR_STOP 1

-- test that drop trigger works 

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
-- test that disable trigger is disallowed 
ALTER TABLE location DISABLE TRIGGER create_vehicle_trigger;
\set ON_ERROR_STOP 1

-- test that drop trigger works 
DROP TRIGGER create_color_trigger ON location;
DROP TRIGGER create_vehicle_trigger ON location;


            

Reported by SQLint.

              CREATE TABLE transition_test(time timestamptz NOT NULL);
CREATE TRIGGER t1 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger();

\set ON_ERROR_STOP 0
SELECT create_hypertable('transition_test','time');
\set ON_ERROR_STOP 1
DROP TRIGGER t1 ON transition_test;
SELECT create_hypertable('transition_test','time');


            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
SELECT create_hypertable('transition_test','time');
\set ON_ERROR_STOP 1
DROP TRIGGER t1 ON transition_test;
SELECT create_hypertable('transition_test','time');

-- test creating trigger with transition tables on existing hypertable
\set ON_ERROR_STOP 0

            

Reported by SQLint.

              SELECT create_hypertable('transition_test','time');

-- test creating trigger with transition tables on existing hypertable
\set ON_ERROR_STOP 0
CREATE TRIGGER t2 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger();
CREATE TRIGGER t3 AFTER UPDATE ON transition_test REFERENCING NEW TABLE AS new_trans OLD TABLE AS old_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger();
CREATE TRIGGER t4 AFTER DELETE ON transition_test REFERENCING OLD TABLE AS old_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger();

CREATE TRIGGER t2 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH ROW EXECUTE FUNCTION test_trigger();

            

Reported by SQLint.

              CREATE TRIGGER t2 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH ROW EXECUTE FUNCTION test_trigger();
CREATE TRIGGER t3 AFTER UPDATE ON transition_test REFERENCING NEW TABLE AS new_trans OLD TABLE AS old_trans FOR EACH ROW EXECUTE FUNCTION test_trigger();
CREATE TRIGGER t4 AFTER DELETE ON transition_test REFERENCING OLD TABLE AS old_trans FOR EACH ROW EXECUTE FUNCTION test_trigger();
\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.

\set ON_ERROR_STOP 0

\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 tablespace2 OWNER :ROLE_DEFAULT_PERM_USER_2 LOCATION :TEST_TABLESPACE2_PATH;

--Running some of the same tests as we do for reorder/cluster because that's how move is implemented
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

\set ON_ERROR_STOP 1

\ir include/cluster_test_setup.sql


            

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 move a chunk with no reorder index on first call 
SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1');
-- cannot move a chunk without a destination tablespace set
SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>NULL, index_destination_tablespace=>'tablespace1', reorder_index=>'_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx');
-- cannot move a chunk without an index_destination_tablespace set

            

Reported by SQLint.

              SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace2', reorder_index=>'_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx');
END;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2 
-- must be hypertable owner to move a chunk
SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>'tablespace2', index_destination_tablespace=>'tablespace2', reorder_index=>'_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx', verbose=>TRUE);
\set ON_ERROR_STOP 1

-- grant create permissions on tablespace2 so we can use it later

            

Reported by SQLint.

              \c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2 
-- must be hypertable owner to move a chunk
SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>'tablespace2', index_destination_tablespace=>'tablespace2', reorder_index=>'_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx', verbose=>TRUE);
\set ON_ERROR_STOP 1

-- grant create permissions on tablespace2 so we can use it later
GRANT CREATE ON TABLESPACE tablespace2 TO :ROLE_DEFAULT_PERM_USER;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

            

Reported by SQLint.

              -- grant create permissions on tablespace2 so we can use it later
GRANT CREATE ON TABLESPACE tablespace2 TO :ROLE_DEFAULT_PERM_USER;

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

-- move with chunk index for reorder
SELECT move_chunk(chunk=>'_timescaledb_internal._hyper_1_2_chunk', destination_tablespace=>'tablespace1', index_destination_tablespace=>'tablespace1', reorder_index=>'_timescaledb_internal._hyper_1_2_chunk_cluster_test_time_idx', verbose=>TRUE);
SELECT * FROM test.show_subtables('cluster_test');
SELECT * FROM test.show_indexesp('_timescaledb_internal._hyper%_chunk');

            

Reported by SQLint.

              SELECT * FROM test.show_subtables('cluster_test');
SELECT * FROM test.show_indexesp('_timescaledb_internal._hyper%_chunk');
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 enable_seqscan=Default;
SET enable_indexscan=Default;

            

Reported by SQLint.

                GROUP BY bucket, location
  HAVING min(location) >= 'NYC' and avg(temperature) > 20
  $$ AS "QUERY_TEMPLATE"
\gset

SELECT
  replace(:'QUERY_TEMPLATE', 'TABLE', 'conditions_before') AS "QUERY_BEFORE",
  replace(:'QUERY_TEMPLATE', 'TABLE', 'conditions_after') AS "QUERY_AFTER"
\gset

            

Reported by SQLint.

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

SELECT count(*) FROM conditions_before;
SELECT count(*) FROM conditions_after;

--dump & restore

            

Reported by SQLint.

              SELECT count(*) FROM conditions_after;

--dump & restore
\c postgres :ROLE_SUPERUSER
\! utils/pg_dump_aux_dump.sh dump/pg_dump.sql

\c :TEST_DBNAME
SET client_min_messages = ERROR;
CREATE EXTENSION timescaledb CASCADE;

            

Reported by SQLint.

              
--\! cp dump/pg_dump.sql /tmp/dump.sql
SELECT timescaledb_pre_restore();
\! utils/pg_dump_aux_restore.sh dump/pg_dump.sql
SELECT timescaledb_post_restore();
SELECT _timescaledb_internal.stop_background_workers();

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER


            

Reported by SQLint.

              SELECT timescaledb_post_restore();
SELECT _timescaledb_internal.stop_background_workers();

\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER

--make sure the appropriate DROP are still blocked.
\set ON_ERROR_STOP 0
DROP table :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME";
DROP VIEW :"PART_VIEW_SCHEMA".:"PART_VIEW_NAME";

            

Reported by SQLint.

              \set ON_ERROR_STOP 0
DROP table :"MAT_SCHEMA_NAME".:"MAT_TABLE_NAME";
DROP VIEW :"PART_VIEW_SCHEMA".:"PART_VIEW_NAME";
\set ON_ERROR_STOP 1

--materialize mat_after
CALL refresh_continuous_aggregate('mat_after', NULL, NULL);
SELECT count(*) FROM mat_after;


            

Reported by SQLint.

              --compare results
SELECT count(*) FROM conditions_before;
SELECT count(*) FROM conditions_after;
\set VIEW_NAME mat_before
\set QUERY :QUERY_BEFORE
\set ECHO errors
\ir include/cont_agg_test_equal.sql
\set ECHO all
\set VIEW_NAME mat_after

            

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'

\c postgres :ROLE_SUPERUSER
DROP DATABASE :TEST_DBNAME;
CREATE DATABASE :TEST_DBNAME;


            

Reported by SQLint.

              DROP DATABASE :TEST_DBNAME;
CREATE DATABASE :TEST_DBNAME;

\c :TEST_DBNAME
CREATE SCHEMA "testSchema0";
SET client_min_messages=error;
CREATE EXTENSION IF NOT EXISTS timescaledb SCHEMA "testSchema0";
RESET client_min_messages;


            

Reported by SQLint.

              
-- testing drop_chunks START
-- show_chunks and drop_chunks output should be the same
\set QUERY1 'SELECT "testSchema0".show_chunks(older_than => \'2017-03-01\'::timestamp, relation => \'test_ts\')::REGCLASS::TEXT'
\set QUERY2 'SELECT "testSchema0".drop_chunks(\'test_ts\', \'2017-03-01\'::timestamp)::TEXT'
\set ECHO errors
\ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all
SELECT * FROM test_ts ORDER BY time;

            

Reported by SQLint.

              \ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all
SELECT * FROM test_ts ORDER BY time;
\set QUERY1 'SELECT "testSchema0".show_chunks(older_than => interval \'1 minutes\', relation => \'test_tz\')::REGCLASS::TEXT'
\set QUERY2 'SELECT "testSchema0".drop_chunks(\'test_tz\', interval \'1 minutes\')::TEXT'
\set ECHO errors
\ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all
SELECT * FROM test_tz ORDER BY time;

            

Reported by SQLint.

              \ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all
SELECT * FROM test_tz ORDER BY time;
\set QUERY1 'SELECT "testSchema0".show_chunks(older_than => interval \'1 minutes\', relation => \'test_dt\')::REGCLASS::TEXT'
\set QUERY2 'SELECT "testSchema0".drop_chunks(\'test_dt\', interval \'1 minutes\')::TEXT'
\set ECHO errors
\ir  :QUERY_RESULT_TEST_EQUAL_RELPATH
\set ECHO all
SELECT * FROM test_dt ORDER BY time;

            

Reported by SQLint.

              
CREATE SCHEMA "testSchema";

\set ON_ERROR_STOP 0
ALTER EXTENSION timescaledb SET SCHEMA "testSchema";
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              
\set ON_ERROR_STOP 0
ALTER EXTENSION timescaledb SET SCHEMA "testSchema";
\set ON_ERROR_STOP 1

            

Reported by SQLint.

              -- usage:
-- psql [your connect flags] -d your_timescale_db < dump_meta_data.sql > dumpfile.txt

\echo 'TimescaleDB meta data dump'
\echo '<exclude_from_test>'
\echo 'Date, git commit, and extension version can change without it being an error.'
\echo 'Adding this tag allows us to run regression tests on this script file.'
\echo `date`
\echo 'Postgres version'

            

Reported by SQLint.

              \echo 'Postgres version'
select version();

\echo 'Build tag'
\set ON_ERROR_STOP 0
SELECT * FROM _timescaledb_internal.get_git_commit();
\set ON_ERROR_STOP 1
\dx


            

Reported by SQLint.

              \echo 'Build tag'
\set ON_ERROR_STOP 0
SELECT * FROM _timescaledb_internal.get_git_commit();
\set ON_ERROR_STOP 1
\dx

\echo '</exclude_from_test>'

\echo 'List of tables'

            

Reported by SQLint.

              \echo 'List of hypertables'
SELECT * FROM _timescaledb_catalog.hypertable;

\echo 'List of chunk indexes'
SELECT * FROM _timescaledb_catalog.chunk_index;

\echo 'Size of hypertables'
SELECT hypertable,
       table_bytes,

            

Reported by SQLint.

              \echo 'List of chunk indexes'
SELECT * FROM _timescaledb_catalog.chunk_index;

\echo 'Size of hypertables'
SELECT hypertable,
       table_bytes,
       index_bytes,
       toast_bytes,
       total_bytes

            

Reported by SQLint.

                            ) sub1
       ) sub2;

\echo 'Chunk sizes:'
SELECT chunk_id,
chunk_table,
partitioning_columns,
partitioning_column_types,
partitioning_hash_functions,

            

Reported by SQLint.

                     ) sub1
) sub2;

\echo 'Hypertable index sizes'
SELECT h.schema_name || '.' || h.table_name AS hypertable,
       h.schema_name || '.' || ci.hypertable_index_name AS index_name,
       sum(pg_relation_size(c.oid))::bigint AS index_bytes
FROM
pg_class c,

            

Reported by SQLint.