1-- This file and its contents are licensed under the Timescale License. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-TIMESCALE for a copy of the license. 4-- Need to be super user to create extension and add data nodes 5\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER; 6-- Support for execute_sql_and_filter_server_name_on_error() 7\unset ECHO 8psql:include/remote_exec.sql:5: NOTICE: schema "test" already exists, skipping 9psql:include/filter_exec.sql:5: NOTICE: schema "test" already exists, skipping 10\set MY_DB1 :TEST_DBNAME _1 11\set MY_DB2 :TEST_DBNAME _2 12\set MY_DB3 :TEST_DBNAME _3 13SELECT * FROM add_data_node('data_node_1', host => 'localhost', database => :'MY_DB1'); 14 node_name | host | port | database | node_created | database_created | extension_created 15-------------+-----------+-------+---------------+--------------+------------------+------------------- 16 data_node_1 | localhost | 55432 | db_dist_ddl_1 | t | t | t 17(1 row) 18 19SELECT * FROM add_data_node('data_node_2', host => 'localhost', database => :'MY_DB2'); 20 node_name | host | port | database | node_created | database_created | extension_created 21-------------+-----------+-------+---------------+--------------+------------------+------------------- 22 data_node_2 | localhost | 55432 | db_dist_ddl_2 | t | t | t 23(1 row) 24 25SELECT * FROM add_data_node('data_node_3', host => 'localhost', database => :'MY_DB3'); 26 node_name | host | port | database | node_created | database_created | extension_created 27-------------+-----------+-------+---------------+--------------+------------------+------------------- 28 data_node_3 | localhost | 55432 | db_dist_ddl_3 | t | t | t 29(1 row) 30 31GRANT USAGE ON FOREIGN SERVER data_node_1, data_node_2, data_node_3 TO PUBLIC; 32-- Presence of non-distributed hypertables on data nodes should not cause issues 33CALL distributed_exec('CREATE TABLE local(time timestamptz, measure int)', '{ "data_node_1", "data_node_3" }'); 34CALL distributed_exec($$ SELECT create_hypertable('local', 'time') $$, '{ "data_node_1", "data_node_3" }'); 35-- Import testsupport.sql file to data nodes 36\unset ECHO 37-- This SCHEMA will not be created on data nodes 38CREATE SCHEMA disttable_schema AUTHORIZATION :ROLE_1; 39CREATE SCHEMA some_schema AUTHORIZATION :ROLE_1; 40SET ROLE :ROLE_1; 41CREATE TABLE disttable(time timestamptz, device int, color int CONSTRAINT color_check CHECK (color > 0), temp float); 42CREATE UNIQUE INDEX disttable_pk ON disttable(time, temp); 43-- CREATE TABLE 44SELECT * FROM create_distributed_hypertable('disttable', 'time', 'temp', replication_factor => 3); 45NOTICE: adding not-null constraint to column "time" 46 hypertable_id | schema_name | table_name | created 47---------------+-------------+------------+--------- 48 1 | public | disttable | t 49(1 row) 50 51SELECT * FROM test.show_columns('disttable'); 52 Column | Type | NotNull 53--------+--------------------------+--------- 54 time | timestamp with time zone | t 55 device | integer | f 56 color | integer | f 57 temp | double precision | f 58(4 rows) 59 60SELECT * FROM test.show_constraints('disttable'); 61 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 62-------------+------+---------+-------+-------------+------------+----------+----------- 63 color_check | c | {color} | - | (color > 0) | f | f | t 64(1 row) 65 66SELECT * FROM test.show_indexes('disttable'); 67 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 68-------------------------+-------------+------+--------+---------+-----------+------------ 69 disttable_pk | {time,temp} | | t | f | f | 70 disttable_temp_time_idx | {temp,time} | | f | f | f | 71 disttable_time_idx | {time} | | f | f | f | 72(3 rows) 73 74SELECT * FROM test.show_triggers('disttable'); 75 Trigger | Type | Function 76-------------------+------+-------------------------------------- 77 ts_insert_blocker | 7 | _timescaledb_internal.insert_blocker 78(1 row) 79 80SELECT * FROM test.remote_exec(NULL, $$ 81SELECT * FROM test.show_columns('disttable'); 82SELECT * FROM test.show_constraints('disttable'); 83SELECT * FROM test.show_indexes('disttable'); 84SELECT * FROM test.show_triggers('disttable'); 85$$); 86NOTICE: [data_node_1]: 87SELECT * FROM test.show_columns('disttable') 88NOTICE: [data_node_1]: 89Column|Type |NotNull 90------+------------------------+------- 91time |timestamp with time zone|t 92device|integer |f 93color |integer |f 94temp |double precision |f 95(4 rows) 96 97 98NOTICE: [data_node_1]: 99SELECT * FROM test.show_constraints('disttable') 100NOTICE: [data_node_1]: 101Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 102-----------+----+-------+-----+-----------+----------+--------+--------- 103color_check|c |{color}|- |(color > 0)|f |f |t 104(1 row) 105 106 107NOTICE: [data_node_1]: 108SELECT * FROM test.show_indexes('disttable') 109NOTICE: [data_node_1]: 110Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 111-----------------------+-----------+----+------+-------+---------+---------- 112disttable_pk |{time,temp}| |t |f |f | 113disttable_temp_time_idx|{temp,time}| |f |f |f | 114disttable_time_idx |{time} | |f |f |f | 115(3 rows) 116 117 118NOTICE: [data_node_1]: 119SELECT * FROM test.show_triggers('disttable') 120NOTICE: [data_node_1]: 121Trigger |Type|Function 122-----------------+----+------------------------------------ 123ts_insert_blocker| 7|_timescaledb_internal.insert_blocker 124(1 row) 125 126 127NOTICE: [data_node_2]: 128SELECT * FROM test.show_columns('disttable') 129NOTICE: [data_node_2]: 130Column|Type |NotNull 131------+------------------------+------- 132time |timestamp with time zone|t 133device|integer |f 134color |integer |f 135temp |double precision |f 136(4 rows) 137 138 139NOTICE: [data_node_2]: 140SELECT * FROM test.show_constraints('disttable') 141NOTICE: [data_node_2]: 142Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 143-----------+----+-------+-----+-----------+----------+--------+--------- 144color_check|c |{color}|- |(color > 0)|f |f |t 145(1 row) 146 147 148NOTICE: [data_node_2]: 149SELECT * FROM test.show_indexes('disttable') 150NOTICE: [data_node_2]: 151Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 152-----------------------+-----------+----+------+-------+---------+---------- 153disttable_pk |{time,temp}| |t |f |f | 154disttable_temp_time_idx|{temp,time}| |f |f |f | 155disttable_time_idx |{time} | |f |f |f | 156(3 rows) 157 158 159NOTICE: [data_node_2]: 160SELECT * FROM test.show_triggers('disttable') 161NOTICE: [data_node_2]: 162Trigger |Type|Function 163-----------------+----+------------------------------------ 164ts_insert_blocker| 7|_timescaledb_internal.insert_blocker 165(1 row) 166 167 168NOTICE: [data_node_3]: 169SELECT * FROM test.show_columns('disttable') 170NOTICE: [data_node_3]: 171Column|Type |NotNull 172------+------------------------+------- 173time |timestamp with time zone|t 174device|integer |f 175color |integer |f 176temp |double precision |f 177(4 rows) 178 179 180NOTICE: [data_node_3]: 181SELECT * FROM test.show_constraints('disttable') 182NOTICE: [data_node_3]: 183Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 184-----------+----+-------+-----+-----------+----------+--------+--------- 185color_check|c |{color}|- |(color > 0)|f |f |t 186(1 row) 187 188 189NOTICE: [data_node_3]: 190SELECT * FROM test.show_indexes('disttable') 191NOTICE: [data_node_3]: 192Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 193-----------------------+-----------+----+------+-------+---------+---------- 194disttable_pk |{time,temp}| |t |f |f | 195disttable_temp_time_idx|{temp,time}| |f |f |f | 196disttable_time_idx |{time} | |f |f |f | 197(3 rows) 198 199 200NOTICE: [data_node_3]: 201SELECT * FROM test.show_triggers('disttable') 202NOTICE: [data_node_3]: 203Trigger |Type|Function 204-----------------+----+------------------------------------ 205ts_insert_blocker| 7|_timescaledb_internal.insert_blocker 206(1 row) 207 208 209 remote_exec 210------------- 211 212(1 row) 213 214-- ADD CONSTRAINT 215ALTER TABLE disttable ADD CONSTRAINT device_check CHECK (device > 0); 216SELECT * FROM test.show_constraints('disttable'); 217 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 218--------------+------+----------+-------+--------------+------------+----------+----------- 219 color_check | c | {color} | - | (color > 0) | f | f | t 220 device_check | c | {device} | - | (device > 0) | f | f | t 221(2 rows) 222 223SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_constraints('disttable') $$); 224NOTICE: [data_node_1]: SELECT * FROM test.show_constraints('disttable') 225NOTICE: [data_node_1]: 226Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 227------------+----+--------+-----+------------+----------+--------+--------- 228color_check |c |{color} |- |(color > 0) |f |f |t 229device_check|c |{device}|- |(device > 0)|f |f |t 230(2 rows) 231 232 233NOTICE: [data_node_2]: SELECT * FROM test.show_constraints('disttable') 234NOTICE: [data_node_2]: 235Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 236------------+----+--------+-----+------------+----------+--------+--------- 237color_check |c |{color} |- |(color > 0) |f |f |t 238device_check|c |{device}|- |(device > 0)|f |f |t 239(2 rows) 240 241 242NOTICE: [data_node_3]: SELECT * FROM test.show_constraints('disttable') 243NOTICE: [data_node_3]: 244Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 245------------+----+--------+-----+------------+----------+--------+--------- 246color_check |c |{color} |- |(color > 0) |f |f |t 247device_check|c |{device}|- |(device > 0)|f |f |t 248(2 rows) 249 250 251 remote_exec 252------------- 253 254(1 row) 255 256-- DROP CONSTRAINT 257ALTER TABLE disttable DROP CONSTRAINT device_check; 258SELECT * FROM test.show_constraints('disttable'); 259 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 260-------------+------+---------+-------+-------------+------------+----------+----------- 261 color_check | c | {color} | - | (color > 0) | f | f | t 262(1 row) 263 264SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_constraints('disttable') $$); 265NOTICE: [data_node_1]: SELECT * FROM test.show_constraints('disttable') 266NOTICE: [data_node_1]: 267Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 268-----------+----+-------+-----+-----------+----------+--------+--------- 269color_check|c |{color}|- |(color > 0)|f |f |t 270(1 row) 271 272 273NOTICE: [data_node_2]: SELECT * FROM test.show_constraints('disttable') 274NOTICE: [data_node_2]: 275Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 276-----------+----+-------+-----+-----------+----------+--------+--------- 277color_check|c |{color}|- |(color > 0)|f |f |t 278(1 row) 279 280 281NOTICE: [data_node_3]: SELECT * FROM test.show_constraints('disttable') 282NOTICE: [data_node_3]: 283Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 284-----------+----+-------+-----+-----------+----------+--------+--------- 285color_check|c |{color}|- |(color > 0)|f |f |t 286(1 row) 287 288 289 remote_exec 290------------- 291 292(1 row) 293 294-- DROP CONSTRAINT pre-created 295ALTER TABLE disttable DROP CONSTRAINT color_check; 296SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_constraints('disttable') $$); 297NOTICE: [data_node_1]: SELECT * FROM test.show_constraints('disttable') 298NOTICE: [data_node_1]: 299Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 300----------+----+-------+-----+----+----------+--------+--------- 301(0 rows) 302 303 304NOTICE: [data_node_2]: SELECT * FROM test.show_constraints('disttable') 305NOTICE: [data_node_2]: 306Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 307----------+----+-------+-----+----+----------+--------+--------- 308(0 rows) 309 310 311NOTICE: [data_node_3]: SELECT * FROM test.show_constraints('disttable') 312NOTICE: [data_node_3]: 313Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 314----------+----+-------+-----+----+----------+--------+--------- 315(0 rows) 316 317 318 remote_exec 319------------- 320 321(1 row) 322 323-- DROP COLUMN 324ALTER TABLE disttable DROP COLUMN color; 325SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_columns('disttable') $$); 326NOTICE: [data_node_1]: SELECT * FROM test.show_columns('disttable') 327NOTICE: [data_node_1]: 328Column|Type |NotNull 329------+------------------------+------- 330time |timestamp with time zone|t 331device|integer |f 332temp |double precision |f 333(3 rows) 334 335 336NOTICE: [data_node_2]: SELECT * FROM test.show_columns('disttable') 337NOTICE: [data_node_2]: 338Column|Type |NotNull 339------+------------------------+------- 340time |timestamp with time zone|t 341device|integer |f 342temp |double precision |f 343(3 rows) 344 345 346NOTICE: [data_node_3]: SELECT * FROM test.show_columns('disttable') 347NOTICE: [data_node_3]: 348Column|Type |NotNull 349------+------------------------+------- 350time |timestamp with time zone|t 351device|integer |f 352temp |double precision |f 353(3 rows) 354 355 356 remote_exec 357------------- 358 359(1 row) 360 361-- ADD COLUMN 362ALTER TABLE disttable ADD COLUMN description text; 363SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_columns('disttable') $$); 364NOTICE: [data_node_1]: SELECT * FROM test.show_columns('disttable') 365NOTICE: [data_node_1]: 366Column |Type |NotNull 367-----------+------------------------+------- 368time |timestamp with time zone|t 369device |integer |f 370temp |double precision |f 371description|text |f 372(4 rows) 373 374 375NOTICE: [data_node_2]: SELECT * FROM test.show_columns('disttable') 376NOTICE: [data_node_2]: 377Column |Type |NotNull 378-----------+------------------------+------- 379time |timestamp with time zone|t 380device |integer |f 381temp |double precision |f 382description|text |f 383(4 rows) 384 385 386NOTICE: [data_node_3]: SELECT * FROM test.show_columns('disttable') 387NOTICE: [data_node_3]: 388Column |Type |NotNull 389-----------+------------------------+------- 390time |timestamp with time zone|t 391device |integer |f 392temp |double precision |f 393description|text |f 394(4 rows) 395 396 397 remote_exec 398------------- 399 400(1 row) 401 402-- CREATE INDEX 403CREATE INDEX disttable_description_idx ON disttable (description); 404SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('disttable') $$); 405NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('disttable') 406NOTICE: [data_node_1]: 407Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 408-------------------------+-------------+----+------+-------+---------+---------- 409disttable_description_idx|{description}| |f |f |f | 410disttable_pk |{time,temp} | |t |f |f | 411disttable_temp_time_idx |{temp,time} | |f |f |f | 412disttable_time_idx |{time} | |f |f |f | 413(4 rows) 414 415 416NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('disttable') 417NOTICE: [data_node_2]: 418Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 419-------------------------+-------------+----+------+-------+---------+---------- 420disttable_description_idx|{description}| |f |f |f | 421disttable_pk |{time,temp} | |t |f |f | 422disttable_temp_time_idx |{temp,time} | |f |f |f | 423disttable_time_idx |{time} | |f |f |f | 424(4 rows) 425 426 427NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('disttable') 428NOTICE: [data_node_3]: 429Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 430-------------------------+-------------+----+------+-------+---------+---------- 431disttable_description_idx|{description}| |f |f |f | 432disttable_pk |{time,temp} | |t |f |f | 433disttable_temp_time_idx |{temp,time} | |f |f |f | 434disttable_time_idx |{time} | |f |f |f | 435(4 rows) 436 437 438 remote_exec 439------------- 440 441(1 row) 442 443-- TRUNCATE 444CREATE TABLE non_disttable1(time timestamptz); 445CREATE TABLE non_disttable2(time timestamptz); 446SELECT create_hypertable('non_disttable2', 'time'); 447NOTICE: adding not-null constraint to column "time" 448 create_hypertable 449----------------------------- 450 (2,public,non_disttable2,t) 451(1 row) 452 453-- Truncating two non-distribued hypertables should be OK. 454TRUNCATE non_disttable1, non_disttable2; 455-- Truncating one distributed hypertable should be OK 456TRUNCATE disttable; 457-- Test unsupported operations on distributed hypertable 458\set ON_ERROR_STOP 0 459-- test set_replication_factor on non-hypertable 460SELECT * FROM set_replication_factor('non_disttable1', 1); 461ERROR: table "non_disttable1" is not a hypertable 462-- test set_replication_factor on non-distributed 463SELECT * FROM set_replication_factor('non_disttable2', 1); 464ERROR: hypertable "non_disttable2" is not distributed 465-- test set_replication_factor on NULL hypertable 466SELECT * FROM set_replication_factor(NULL, 1); 467ERROR: invalid hypertable: cannot be NULL 468-- Combining one distributed hypertable with any other tables should 469-- be blocked since not all nodes might have all tables and we 470-- currently don't rewrite the command. 471TRUNCATE disttable, non_disttable1; 472ERROR: operation not supported on distributed hypertable 473TRUNCATE disttable, non_disttable2; 474ERROR: operation not supported on distributed hypertable 475CLUSTER disttable USING disttable_description_idx; 476ERROR: operation not supported on distributed hypertable 477ALTER TABLE disttable RENAME TO disttable2; 478ERROR: operation not supported on distributed hypertable 479ALTER TABLE disttable SET SCHEMA some_unexist_schema; 480ERROR: schema "some_unexist_schema" does not exist 481ALTER TABLE disttable SET SCHEMA some_schema; 482ERROR: operation not supported on distributed hypertable 483DROP TABLE non_disttable1, disttable; 484ERROR: cannot drop a hypertable along with other objects 485DROP TABLE disttable, non_disttable2; 486ERROR: cannot drop a hypertable along with other objects 487DROP TABLE disttable, disttable; 488ERROR: cannot drop a hypertable along with other objects 489\set ON_ERROR_STOP 1 490---------------------------------------------------------------------------------------- 491-- Test column type change, renaming columns, constraints, indexes, and REINDEX command. 492---------------------------------------------------------------------------------------- 493ALTER TABLE disttable ALTER COLUMN description TYPE VARCHAR(10); 494ALTER TABLE disttable ADD COLUMN float_col float; 495ALTER TABLE disttable ALTER COLUMN float_col TYPE INT USING float_col::int; 496\set ON_ERROR_STOP 0 497-- Changing the type of a hash-partitioned column should not be supported 498ALTER TABLE disttable ALTER COLUMN temp TYPE numeric; 499ERROR: cannot change the type of a hash-partitioned column 500\set ON_ERROR_STOP 1 501-- Should be able to change if not hash partitioned though 502ALTER TABLE disttable ALTER COLUMN time TYPE timestamp; 503INSERT INTO disttable VALUES 504 ('2017-01-01 06:01', 1, 1.2, 'test'), 505 ('2017-01-01 09:11', 3, 4.3, 'test'), 506 ('2017-01-01 08:01', 1, 7.3, 'test'), 507 ('2017-01-02 08:01', 2, 0.23, 'test'), 508 ('2018-07-02 08:01', 87, 0.0, 'test'), 509 ('2018-07-01 06:01', 13, 3.1, 'test'), 510 ('2018-07-01 09:11', 90, 10303.12, 'test'), 511 ('2018-07-01 08:01', 29, 64, 'test'); 512SELECT * FROM show_chunks('disttable'); 513 show_chunks 514--------------------------------------------- 515 _timescaledb_internal._dist_hyper_1_1_chunk 516 _timescaledb_internal._dist_hyper_1_2_chunk 517 _timescaledb_internal._dist_hyper_1_3_chunk 518 _timescaledb_internal._dist_hyper_1_4_chunk 519 _timescaledb_internal._dist_hyper_1_5_chunk 520(5 rows) 521 522-- Rename column 523ALTER TABLE disttable RENAME COLUMN description TO descr; 524SELECT * FROM test.show_columns('disttable') 525WHERE "Column"='descr'; 526 Column | Type | NotNull 527--------+-------------------+--------- 528 descr | character varying | f 529(1 row) 530 531SELECT * FROM test.remote_exec('{ data_node_1 }', $$ 532 SELECT chunk.relid AS chunk_relid, 533 (SELECT "Column" AS col FROM test.show_columns(chunk.relid) WHERE "Column"='descr') 534 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 535$$); 536NOTICE: [data_node_1]: 537 SELECT chunk.relid AS chunk_relid, 538 (SELECT "Column" AS col FROM test.show_columns(chunk.relid) WHERE "Column"='descr') 539 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 540 541NOTICE: [data_node_1]: 542chunk_relid |col 543-------------------------------------------+----- 544_timescaledb_internal._dist_hyper_1_1_chunk|descr 545(1 row) 546 547 548 remote_exec 549------------- 550 551(1 row) 552 553-- Rename constraint 554ALTER TABLE disttable ADD CONSTRAINT device_check CHECK (device > 0); 555ALTER TABLE disttable RENAME CONSTRAINT device_check TO device_chk; 556SELECT * FROM test.show_constraints('disttable') 557WHERE "Constraint"='device_chk'; 558 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 559------------+------+----------+-------+--------------+------------+----------+----------- 560 device_chk | c | {device} | - | (device > 0) | f | f | t 561(1 row) 562 563SELECT * FROM test.remote_exec('{ data_node_1 }', $$ 564 SELECT chunk.relid AS chunk_relid, 565 (SELECT "Constraint" AS constr FROM test.show_constraints(chunk.relid) WHERE "Constraint"='device_chk') 566 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 567$$); 568NOTICE: [data_node_1]: 569 SELECT chunk.relid AS chunk_relid, 570 (SELECT "Constraint" AS constr FROM test.show_constraints(chunk.relid) WHERE "Constraint"='device_chk') 571 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 572 573NOTICE: [data_node_1]: 574chunk_relid |constr 575-------------------------------------------+---------- 576_timescaledb_internal._dist_hyper_1_1_chunk|device_chk 577(1 row) 578 579 580 remote_exec 581------------- 582 583(1 row) 584 585-- Rename index 586ALTER INDEX disttable_description_idx RENAME to disttable_descr_idx; 587SELECT * FROM test.show_indexes('disttable') 588WHERE "Index"='disttable_descr_idx'::regclass; 589 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 590---------------------+---------------+------+--------+---------+-----------+------------ 591 disttable_descr_idx | {description} | | f | f | f | 592(1 row) 593 594SELECT * FROM test.remote_exec('{ data_node_1 }', $$ 595 SELECT chunk.relid AS chunk_relid, (test.show_indexes(chunk.relid)).* 596 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 597$$); 598NOTICE: [data_node_1]: 599 SELECT chunk.relid AS chunk_relid, (test.show_indexes(chunk.relid)).* 600 FROM (SELECT "Child" AS relid FROM test.show_subtables('disttable') LIMIT 1) chunk 601 602NOTICE: [data_node_1]: 603chunk_relid |Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 604-------------------------------------------+-------------------------------------------------------------------+-------------+----+------+-------+---------+---------- 605_timescaledb_internal._dist_hyper_1_1_chunk|_timescaledb_internal._dist_hyper_1_1_chunk_disttable_descr_idx |{description}| |f |f |f | 606_timescaledb_internal._dist_hyper_1_1_chunk|_timescaledb_internal._dist_hyper_1_1_chunk_disttable_pk |{time,temp} | |t |f |f | 607_timescaledb_internal._dist_hyper_1_1_chunk|_timescaledb_internal._dist_hyper_1_1_chunk_disttable_temp_time_idx|{temp,time} | |f |f |f | 608_timescaledb_internal._dist_hyper_1_1_chunk|_timescaledb_internal._dist_hyper_1_1_chunk_disttable_time_idx |{time} | |f |f |f | 609(4 rows) 610 611 612 remote_exec 613------------- 614 615(1 row) 616 617-- Test REINDEX command with distributed hypertable 618\c :MY_DB1 619SELECT * FROM test.show_indexes('_timescaledb_internal._dist_hyper_1_1_chunk'); 620 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 621---------------------------------------------------------------------+---------------+------+--------+---------+-----------+------------ 622 _timescaledb_internal._dist_hyper_1_1_chunk_disttable_descr_idx | {description} | | f | f | f | 623 _timescaledb_internal._dist_hyper_1_1_chunk_disttable_pk | {time,temp} | | t | f | f | 624 _timescaledb_internal._dist_hyper_1_1_chunk_disttable_temp_time_idx | {temp,time} | | f | f | f | 625 _timescaledb_internal._dist_hyper_1_1_chunk_disttable_time_idx | {time} | | f | f | f | 626(4 rows) 627 628SELECT pg_relation_filepath('_timescaledb_internal._dist_hyper_1_1_chunk_disttable_pk'::regclass::oid) AS oid_before_reindex \gset 629\c :TEST_DBNAME :ROLE_SUPERUSER; 630SET ROLE :ROLE_1; 631REINDEX TABLE disttable; 632REINDEX (VERBOSE) TABLE disttable; 633\c :MY_DB1 634SELECT pg_relation_filepath('_timescaledb_internal._dist_hyper_1_1_chunk_disttable_pk'::regclass::oid) AS oid_after_reindex \gset 635\c :TEST_DBNAME :ROLE_SUPERUSER; 636SET ROLE :ROLE_1; 637-- expect chunk index oid to change after the reindex operation 638SELECT :'oid_before_reindex' <> :'oid_after_reindex'; 639 ?column? 640---------- 641 t 642(1 row) 643 644-- CREATE/DROP TRIGGER 645CREATE OR REPLACE FUNCTION test_trigger() 646RETURNS TRIGGER LANGUAGE PLPGSQL AS 647$BODY$ 648BEGIN 649RETURN OLD; 650END 651$BODY$; 652CREATE TRIGGER disttable_trigger_test 653BEFORE INSERT ON disttable 654FOR EACH ROW EXECUTE FUNCTION test_trigger(); 655DROP TRIGGER disttable_trigger_test on disttable; 656DROP FUNCTION test_trigger; 657CALL distributed_exec($$ DROP FUNCTION test_trigger $$); 658-- DROP INDEX 659\set ON_ERROR_STOP 0 660DROP INDEX disttable_description_idx, disttable_pk; 661ERROR: cannot drop a hypertable index along with other objects 662\set ON_ERROR_STOP 1 663DROP INDEX disttable_descr_idx; 664DROP INDEX disttable_pk; 665SELECT * FROM test.show_indexes('disttable'); 666 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 667-------------------------+-------------+------+--------+---------+-----------+------------ 668 disttable_temp_time_idx | {temp,time} | | f | f | f | 669 disttable_time_idx | {time} | | f | f | f | 670(2 rows) 671 672SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('disttable') $$); 673NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('disttable') 674NOTICE: [data_node_1]: 675Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 676-----------------------+-----------+----+------+-------+---------+---------- 677disttable_temp_time_idx|{temp,time}| |f |f |f | 678disttable_time_idx |{time} | |f |f |f | 679(2 rows) 680 681 682NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('disttable') 683NOTICE: [data_node_2]: 684Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 685-----------------------+-----------+----+------+-------+---------+---------- 686disttable_temp_time_idx|{temp,time}| |f |f |f | 687disttable_time_idx |{time} | |f |f |f | 688(2 rows) 689 690 691NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('disttable') 692NOTICE: [data_node_3]: 693Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 694-----------------------+-----------+----+------+-------+---------+---------- 695disttable_temp_time_idx|{temp,time}| |f |f |f | 696disttable_time_idx |{time} | |f |f |f | 697(2 rows) 698 699 700 remote_exec 701------------- 702 703(1 row) 704 705-- DROP TABLE 706DROP TABLE disttable; 707SELECT * FROM test.remote_exec(NULL, $$ SELECT 1 FROM pg_tables WHERE tablename = 'disttable' $$); 708NOTICE: [data_node_1]: SELECT 1 FROM pg_tables WHERE tablename = 'disttable' 709NOTICE: [data_node_1]: 710?column? 711-------- 712(0 rows) 713 714 715NOTICE: [data_node_2]: SELECT 1 FROM pg_tables WHERE tablename = 'disttable' 716NOTICE: [data_node_2]: 717?column? 718-------- 719(0 rows) 720 721 722NOTICE: [data_node_3]: SELECT 1 FROM pg_tables WHERE tablename = 'disttable' 723NOTICE: [data_node_3]: 724?column? 725-------- 726(0 rows) 727 728 729 remote_exec 730------------- 731 732(1 row) 733 734DROP TABLE non_disttable1; 735DROP TABLE non_disttable2; 736-- Test current SCHEMA limitations 737-- CREATE TABLE should fail, since remote data nodes has no schema 738\set ON_ERROR_STOP 0 739CREATE TABLE disttable_schema.disttable(time timestamptz, device int, color int, temp float); 740SELECT test.execute_sql_and_filter_data_node_name_on_error($$ 741SELECT * FROM create_hypertable('disttable_schema.disttable', 'time', replication_factor => 3) 742$$); 743NOTICE: adding not-null constraint to column "time" 744ERROR: [data_node_x]: schema "disttable_schema" does not exist 745SELECT * FROM test.remote_exec(NULL, $$ SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'disttable' $$); 746NOTICE: [data_node_1]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'disttable' 747NOTICE: [data_node_1]: 748schemaname|tablename 749----------+--------- 750(0 rows) 751 752 753NOTICE: [data_node_2]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'disttable' 754NOTICE: [data_node_2]: 755schemaname|tablename 756----------+--------- 757(0 rows) 758 759 760NOTICE: [data_node_3]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'disttable' 761NOTICE: [data_node_3]: 762schemaname|tablename 763----------+--------- 764(0 rows) 765 766 767 remote_exec 768------------- 769 770(1 row) 771 772-- CREATE and DROP SCHEMA CASCADE 773\c :MY_DB1 774CREATE SCHEMA some_schema AUTHORIZATION :ROLE_1; 775\c :MY_DB2 776CREATE SCHEMA some_schema AUTHORIZATION :ROLE_1; 777\c :MY_DB3 778CREATE SCHEMA some_schema AUTHORIZATION :ROLE_1; 779\c :TEST_DBNAME :ROLE_SUPERUSER; 780SET ROLE :ROLE_1; 781CREATE TABLE some_schema.some_dist_table(time timestamptz, device int, color int, temp float); 782SELECT * FROM create_hypertable('some_schema.some_dist_table', 'time', replication_factor => 3); 783NOTICE: adding not-null constraint to column "time" 784 hypertable_id | schema_name | table_name | created 785---------------+-------------+-----------------+--------- 786 4 | some_schema | some_dist_table | t 787(1 row) 788 789SELECT * FROM test.remote_exec(NULL, $$ SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' $$); 790NOTICE: [data_node_1]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 791NOTICE: [data_node_1]: 792schemaname |tablename 793-----------+--------------- 794some_schema|some_dist_table 795(1 row) 796 797 798NOTICE: [data_node_2]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 799NOTICE: [data_node_2]: 800schemaname |tablename 801-----------+--------------- 802some_schema|some_dist_table 803(1 row) 804 805 806NOTICE: [data_node_3]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 807NOTICE: [data_node_3]: 808schemaname |tablename 809-----------+--------------- 810some_schema|some_dist_table 811(1 row) 812 813 814 remote_exec 815------------- 816 817(1 row) 818 819DROP SCHEMA some_schema CASCADE; 820NOTICE: drop cascades to table some_schema.some_dist_table 821SELECT * FROM test.remote_exec(NULL, $$ SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' $$); 822NOTICE: [data_node_1]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 823NOTICE: [data_node_1]: 824schemaname|tablename 825----------+--------- 826(0 rows) 827 828 829NOTICE: [data_node_2]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 830NOTICE: [data_node_2]: 831schemaname|tablename 832----------+--------- 833(0 rows) 834 835 836NOTICE: [data_node_3]: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'some_dist_table' 837NOTICE: [data_node_3]: 838schemaname|tablename 839----------+--------- 840(0 rows) 841 842 843 remote_exec 844------------- 845 846(1 row) 847 848-- DROP column cascades to index drop 849CREATE TABLE some_dist_table(time timestamptz, device int, color int, temp float); 850SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 851NOTICE: adding not-null constraint to column "time" 852 hypertable_id | schema_name | table_name | created 853---------------+-------------+-----------------+--------- 854 5 | public | some_dist_table | t 855(1 row) 856 857CREATE INDEX some_dist_device_idx ON some_dist_table (device); 858SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('some_dist_table') $$); 859NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('some_dist_table') 860NOTICE: [data_node_1]: 861Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 862------------------------+--------+----+------+-------+---------+---------- 863some_dist_device_idx |{device}| |f |f |f | 864some_dist_table_time_idx|{time} | |f |f |f | 865(2 rows) 866 867 868NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('some_dist_table') 869NOTICE: [data_node_2]: 870Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 871------------------------+--------+----+------+-------+---------+---------- 872some_dist_device_idx |{device}| |f |f |f | 873some_dist_table_time_idx|{time} | |f |f |f | 874(2 rows) 875 876 877NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('some_dist_table') 878NOTICE: [data_node_3]: 879Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 880------------------------+--------+----+------+-------+---------+---------- 881some_dist_device_idx |{device}| |f |f |f | 882some_dist_table_time_idx|{time} | |f |f |f | 883(2 rows) 884 885 886 remote_exec 887------------- 888 889(1 row) 890 891ALTER TABLE some_dist_table DROP COLUMN device; 892SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('some_dist_table') $$); 893NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('some_dist_table') 894NOTICE: [data_node_1]: 895Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 896------------------------+-------+----+------+-------+---------+---------- 897some_dist_table_time_idx|{time} | |f |f |f | 898(1 row) 899 900 901NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('some_dist_table') 902NOTICE: [data_node_2]: 903Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 904------------------------+-------+----+------+-------+---------+---------- 905some_dist_table_time_idx|{time} | |f |f |f | 906(1 row) 907 908 909NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('some_dist_table') 910NOTICE: [data_node_3]: 911Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 912------------------------+-------+----+------+-------+---------+---------- 913some_dist_table_time_idx|{time} | |f |f |f | 914(1 row) 915 916 917 remote_exec 918------------- 919 920(1 row) 921 922DROP TABLE some_dist_table; 923-- Creation of foreign key on distributed hypertable table will lead 924-- to error, since non_htable is local 925CREATE TABLE non_htable (id int PRIMARY KEY); 926CREATE TABLE some_dist_table(time timestamptz, device int REFERENCES non_htable(id)); 927\set ON_ERROR_STOP 0 928SELECT test.execute_sql_and_filter_data_node_name_on_error($$ 929SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 930$$); 931WARNING: distributed hypertable "some_dist_table" has a foreign key to a non-distributed table 932NOTICE: adding not-null constraint to column "time" 933ERROR: [data_node_x]: relation "non_htable" does not exist 934\set ON_ERROR_STOP 1 935DROP TABLE some_dist_table; 936DROP TABLE non_htable; 937-- Transactional DDL tests 938-- Single-statement transactions 939-- BEGIN/COMMIT 940CREATE TABLE some_dist_table(time timestamptz, device int); 941SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 942NOTICE: adding not-null constraint to column "time" 943 hypertable_id | schema_name | table_name | created 944---------------+-------------+-----------------+--------- 945 7 | public | some_dist_table | t 946(1 row) 947 948BEGIN; 949CREATE INDEX some_dist_device_idx ON some_dist_table (device); 950COMMIT; 951SELECT * FROM test.show_indexes('some_dist_table'); 952 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 953--------------------------+----------+------+--------+---------+-----------+------------ 954 some_dist_device_idx | {device} | | f | f | f | 955 some_dist_table_time_idx | {time} | | f | f | f | 956(2 rows) 957 958SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('some_dist_table') $$); 959NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('some_dist_table') 960NOTICE: [data_node_1]: 961Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 962------------------------+--------+----+------+-------+---------+---------- 963some_dist_device_idx |{device}| |f |f |f | 964some_dist_table_time_idx|{time} | |f |f |f | 965(2 rows) 966 967 968NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('some_dist_table') 969NOTICE: [data_node_2]: 970Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 971------------------------+--------+----+------+-------+---------+---------- 972some_dist_device_idx |{device}| |f |f |f | 973some_dist_table_time_idx|{time} | |f |f |f | 974(2 rows) 975 976 977NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('some_dist_table') 978NOTICE: [data_node_3]: 979Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 980------------------------+--------+----+------+-------+---------+---------- 981some_dist_device_idx |{device}| |f |f |f | 982some_dist_table_time_idx|{time} | |f |f |f | 983(2 rows) 984 985 986 remote_exec 987------------- 988 989(1 row) 990 991DROP TABLE some_dist_table; 992-- BEGIN/ROLLBACK 993CREATE TABLE some_dist_table(time timestamptz, device int); 994SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 995NOTICE: adding not-null constraint to column "time" 996 hypertable_id | schema_name | table_name | created 997---------------+-------------+-----------------+--------- 998 8 | public | some_dist_table | t 999(1 row) 1000 1001BEGIN; 1002CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1003ROLLBACK; 1004SELECT * FROM test.show_indexes('some_dist_table'); 1005 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1006--------------------------+---------+------+--------+---------+-----------+------------ 1007 some_dist_table_time_idx | {time} | | f | f | f | 1008(1 row) 1009 1010SELECT * FROM test.remote_exec(NULL, $$ SELECT * FROM test.show_indexes('some_dist_table') $$); 1011NOTICE: [data_node_1]: SELECT * FROM test.show_indexes('some_dist_table') 1012NOTICE: [data_node_1]: 1013Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1014------------------------+-------+----+------+-------+---------+---------- 1015some_dist_table_time_idx|{time} | |f |f |f | 1016(1 row) 1017 1018 1019NOTICE: [data_node_2]: SELECT * FROM test.show_indexes('some_dist_table') 1020NOTICE: [data_node_2]: 1021Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1022------------------------+-------+----+------+-------+---------+---------- 1023some_dist_table_time_idx|{time} | |f |f |f | 1024(1 row) 1025 1026 1027NOTICE: [data_node_3]: SELECT * FROM test.show_indexes('some_dist_table') 1028NOTICE: [data_node_3]: 1029Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1030------------------------+-------+----+------+-------+---------+---------- 1031some_dist_table_time_idx|{time} | |f |f |f | 1032(1 row) 1033 1034 1035 remote_exec 1036------------- 1037 1038(1 row) 1039 1040DROP TABLE some_dist_table; 1041-- DDL with multiple sub-commands (ALTER) 1042BEGIN; 1043CREATE TABLE some_dist_table(time timestamptz, device int); 1044SELECT * FROM create_distributed_hypertable('some_dist_table', 'time'); 1045NOTICE: adding not-null constraint to column "time" 1046 hypertable_id | schema_name | table_name | created 1047---------------+-------------+-----------------+--------- 1048 9 | public | some_dist_table | t 1049(1 row) 1050 1051\set ON_ERROR_STOP 0 1052-- Mixing SET and other options not supported. This is to protect 1053-- against mixing custom (compression) options with other 1054-- sub-commands. 1055ALTER TABLE some_dist_table SET (fillfactor = 10), 1056ADD CONSTRAINT device_check CHECK (device > 0); 1057ERROR: ALTER TABLE <hypertable> SET does not support multiple clauses 1058\set ON_ERROR_STOP 1 1059ROLLBACK; 1060-- Multi-statement transactions 1061-- BEGIN/COMMIT 1062CREATE TABLE some_dist_table(time timestamptz, device int); 1063SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1064NOTICE: adding not-null constraint to column "time" 1065 hypertable_id | schema_name | table_name | created 1066---------------+-------------+-----------------+--------- 1067 10 | public | some_dist_table | t 1068(1 row) 1069 1070BEGIN; 1071CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1072ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1073COMMIT; 1074SELECT * FROM test.show_indexes('some_dist_table'); 1075 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1076--------------------------+----------+------+--------+---------+-----------+------------ 1077 some_dist_device_idx | {device} | | f | f | f | 1078 some_dist_table_time_idx | {time} | | f | f | f | 1079(2 rows) 1080 1081SELECT * FROM test.show_constraints('some_dist_table'); 1082 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1083--------------+------+----------+-------+--------------+------------+----------+----------- 1084 device_check | c | {device} | - | (device > 0) | f | f | t 1085(1 row) 1086 1087SELECT * FROM test.remote_exec(NULL, $$ 1088SELECT * FROM test.show_indexes('some_dist_table'); 1089SELECT * FROM test.show_constraints('some_dist_table'); 1090$$); 1091NOTICE: [data_node_1]: 1092SELECT * FROM test.show_indexes('some_dist_table') 1093NOTICE: [data_node_1]: 1094Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1095------------------------+--------+----+------+-------+---------+---------- 1096some_dist_device_idx |{device}| |f |f |f | 1097some_dist_table_time_idx|{time} | |f |f |f | 1098(2 rows) 1099 1100 1101NOTICE: [data_node_1]: 1102SELECT * FROM test.show_constraints('some_dist_table') 1103NOTICE: [data_node_1]: 1104Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1105------------+----+--------+-----+------------+----------+--------+--------- 1106device_check|c |{device}|- |(device > 0)|f |f |t 1107(1 row) 1108 1109 1110NOTICE: [data_node_2]: 1111SELECT * FROM test.show_indexes('some_dist_table') 1112NOTICE: [data_node_2]: 1113Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1114------------------------+--------+----+------+-------+---------+---------- 1115some_dist_device_idx |{device}| |f |f |f | 1116some_dist_table_time_idx|{time} | |f |f |f | 1117(2 rows) 1118 1119 1120NOTICE: [data_node_2]: 1121SELECT * FROM test.show_constraints('some_dist_table') 1122NOTICE: [data_node_2]: 1123Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1124------------+----+--------+-----+------------+----------+--------+--------- 1125device_check|c |{device}|- |(device > 0)|f |f |t 1126(1 row) 1127 1128 1129NOTICE: [data_node_3]: 1130SELECT * FROM test.show_indexes('some_dist_table') 1131NOTICE: [data_node_3]: 1132Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1133------------------------+--------+----+------+-------+---------+---------- 1134some_dist_device_idx |{device}| |f |f |f | 1135some_dist_table_time_idx|{time} | |f |f |f | 1136(2 rows) 1137 1138 1139NOTICE: [data_node_3]: 1140SELECT * FROM test.show_constraints('some_dist_table') 1141NOTICE: [data_node_3]: 1142Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1143------------+----+--------+-----+------------+----------+--------+--------- 1144device_check|c |{device}|- |(device > 0)|f |f |t 1145(1 row) 1146 1147 1148 remote_exec 1149------------- 1150 1151(1 row) 1152 1153DROP TABLE some_dist_table; 1154-- BEGIN/ROLLBACK 1155CREATE TABLE some_dist_table(time timestamptz, device int); 1156SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1157NOTICE: adding not-null constraint to column "time" 1158 hypertable_id | schema_name | table_name | created 1159---------------+-------------+-----------------+--------- 1160 11 | public | some_dist_table | t 1161(1 row) 1162 1163BEGIN; 1164CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1165ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1166ROLLBACK; 1167SELECT * FROM test.show_indexes('some_dist_table'); 1168 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1169--------------------------+---------+------+--------+---------+-----------+------------ 1170 some_dist_table_time_idx | {time} | | f | f | f | 1171(1 row) 1172 1173SELECT * FROM test.show_constraints('some_dist_table'); 1174 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1175------------+------+---------+-------+------+------------+----------+----------- 1176(0 rows) 1177 1178SELECT * FROM test.remote_exec(NULL, $$ 1179SELECT * FROM test.show_indexes('some_dist_table'); 1180SELECT * FROM test.show_constraints('some_dist_table'); 1181$$); 1182NOTICE: [data_node_1]: 1183SELECT * FROM test.show_indexes('some_dist_table') 1184NOTICE: [data_node_1]: 1185Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1186------------------------+-------+----+------+-------+---------+---------- 1187some_dist_table_time_idx|{time} | |f |f |f | 1188(1 row) 1189 1190 1191NOTICE: [data_node_1]: 1192SELECT * FROM test.show_constraints('some_dist_table') 1193NOTICE: [data_node_1]: 1194Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1195----------+----+-------+-----+----+----------+--------+--------- 1196(0 rows) 1197 1198 1199NOTICE: [data_node_2]: 1200SELECT * FROM test.show_indexes('some_dist_table') 1201NOTICE: [data_node_2]: 1202Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1203------------------------+-------+----+------+-------+---------+---------- 1204some_dist_table_time_idx|{time} | |f |f |f | 1205(1 row) 1206 1207 1208NOTICE: [data_node_2]: 1209SELECT * FROM test.show_constraints('some_dist_table') 1210NOTICE: [data_node_2]: 1211Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1212----------+----+-------+-----+----+----------+--------+--------- 1213(0 rows) 1214 1215 1216NOTICE: [data_node_3]: 1217SELECT * FROM test.show_indexes('some_dist_table') 1218NOTICE: [data_node_3]: 1219Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1220------------------------+-------+----+------+-------+---------+---------- 1221some_dist_table_time_idx|{time} | |f |f |f | 1222(1 row) 1223 1224 1225NOTICE: [data_node_3]: 1226SELECT * FROM test.show_constraints('some_dist_table') 1227NOTICE: [data_node_3]: 1228Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1229----------+----+-------+-----+----+----------+--------+--------- 1230(0 rows) 1231 1232 1233 remote_exec 1234------------- 1235 1236(1 row) 1237 1238DROP TABLE some_dist_table; 1239-- Nested transactions 1240-- BEGIN/BEGIN/COMMIT/COMMIT 1241CREATE TABLE some_dist_table(time timestamptz, device int); 1242SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1243NOTICE: adding not-null constraint to column "time" 1244 hypertable_id | schema_name | table_name | created 1245---------------+-------------+-----------------+--------- 1246 12 | public | some_dist_table | t 1247(1 row) 1248 1249BEGIN; 1250SAVEPOINT a; 1251CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1252SAVEPOINT b; 1253ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1254COMMIT; 1255SELECT * FROM test.show_indexes('some_dist_table'); 1256 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1257--------------------------+----------+------+--------+---------+-----------+------------ 1258 some_dist_device_idx | {device} | | f | f | f | 1259 some_dist_table_time_idx | {time} | | f | f | f | 1260(2 rows) 1261 1262SELECT * FROM test.show_constraints('some_dist_table'); 1263 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1264--------------+------+----------+-------+--------------+------------+----------+----------- 1265 device_check | c | {device} | - | (device > 0) | f | f | t 1266(1 row) 1267 1268SELECT * FROM test.remote_exec(NULL, $$ 1269SELECT * FROM test.show_indexes('some_dist_table'); 1270SELECT * FROM test.show_constraints('some_dist_table'); 1271$$); 1272NOTICE: [data_node_1]: 1273SELECT * FROM test.show_indexes('some_dist_table') 1274NOTICE: [data_node_1]: 1275Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1276------------------------+--------+----+------+-------+---------+---------- 1277some_dist_device_idx |{device}| |f |f |f | 1278some_dist_table_time_idx|{time} | |f |f |f | 1279(2 rows) 1280 1281 1282NOTICE: [data_node_1]: 1283SELECT * FROM test.show_constraints('some_dist_table') 1284NOTICE: [data_node_1]: 1285Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1286------------+----+--------+-----+------------+----------+--------+--------- 1287device_check|c |{device}|- |(device > 0)|f |f |t 1288(1 row) 1289 1290 1291NOTICE: [data_node_2]: 1292SELECT * FROM test.show_indexes('some_dist_table') 1293NOTICE: [data_node_2]: 1294Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1295------------------------+--------+----+------+-------+---------+---------- 1296some_dist_device_idx |{device}| |f |f |f | 1297some_dist_table_time_idx|{time} | |f |f |f | 1298(2 rows) 1299 1300 1301NOTICE: [data_node_2]: 1302SELECT * FROM test.show_constraints('some_dist_table') 1303NOTICE: [data_node_2]: 1304Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1305------------+----+--------+-----+------------+----------+--------+--------- 1306device_check|c |{device}|- |(device > 0)|f |f |t 1307(1 row) 1308 1309 1310NOTICE: [data_node_3]: 1311SELECT * FROM test.show_indexes('some_dist_table') 1312NOTICE: [data_node_3]: 1313Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1314------------------------+--------+----+------+-------+---------+---------- 1315some_dist_device_idx |{device}| |f |f |f | 1316some_dist_table_time_idx|{time} | |f |f |f | 1317(2 rows) 1318 1319 1320NOTICE: [data_node_3]: 1321SELECT * FROM test.show_constraints('some_dist_table') 1322NOTICE: [data_node_3]: 1323Constraint |Type|Columns |Index|Expr |Deferrable|Deferred|Validated 1324------------+----+--------+-----+------------+----------+--------+--------- 1325device_check|c |{device}|- |(device > 0)|f |f |t 1326(1 row) 1327 1328 1329 remote_exec 1330------------- 1331 1332(1 row) 1333 1334DROP TABLE some_dist_table; 1335-- BEGIN/BEGIN/ROLLBACK/COMMIT 1336CREATE TABLE some_dist_table(time timestamptz, device int); 1337SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1338NOTICE: adding not-null constraint to column "time" 1339 hypertable_id | schema_name | table_name | created 1340---------------+-------------+-----------------+--------- 1341 13 | public | some_dist_table | t 1342(1 row) 1343 1344BEGIN; 1345SAVEPOINT a; 1346CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1347SAVEPOINT b; 1348ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1349ROLLBACK TO SAVEPOINT b; 1350COMMIT; 1351SELECT * FROM test.show_indexes('some_dist_table'); 1352 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1353--------------------------+----------+------+--------+---------+-----------+------------ 1354 some_dist_device_idx | {device} | | f | f | f | 1355 some_dist_table_time_idx | {time} | | f | f | f | 1356(2 rows) 1357 1358SELECT * FROM test.show_constraints('some_dist_table'); 1359 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1360------------+------+---------+-------+------+------------+----------+----------- 1361(0 rows) 1362 1363SELECT * FROM test.remote_exec(NULL, $$ 1364SELECT * FROM test.show_indexes('some_dist_table'); 1365SELECT * FROM test.show_constraints('some_dist_table'); 1366$$); 1367NOTICE: [data_node_1]: 1368SELECT * FROM test.show_indexes('some_dist_table') 1369NOTICE: [data_node_1]: 1370Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1371------------------------+--------+----+------+-------+---------+---------- 1372some_dist_device_idx |{device}| |f |f |f | 1373some_dist_table_time_idx|{time} | |f |f |f | 1374(2 rows) 1375 1376 1377NOTICE: [data_node_1]: 1378SELECT * FROM test.show_constraints('some_dist_table') 1379NOTICE: [data_node_1]: 1380Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1381----------+----+-------+-----+----+----------+--------+--------- 1382(0 rows) 1383 1384 1385NOTICE: [data_node_2]: 1386SELECT * FROM test.show_indexes('some_dist_table') 1387NOTICE: [data_node_2]: 1388Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1389------------------------+--------+----+------+-------+---------+---------- 1390some_dist_device_idx |{device}| |f |f |f | 1391some_dist_table_time_idx|{time} | |f |f |f | 1392(2 rows) 1393 1394 1395NOTICE: [data_node_2]: 1396SELECT * FROM test.show_constraints('some_dist_table') 1397NOTICE: [data_node_2]: 1398Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1399----------+----+-------+-----+----+----------+--------+--------- 1400(0 rows) 1401 1402 1403NOTICE: [data_node_3]: 1404SELECT * FROM test.show_indexes('some_dist_table') 1405NOTICE: [data_node_3]: 1406Index |Columns |Expr|Unique|Primary|Exclusion|Tablespace 1407------------------------+--------+----+------+-------+---------+---------- 1408some_dist_device_idx |{device}| |f |f |f | 1409some_dist_table_time_idx|{time} | |f |f |f | 1410(2 rows) 1411 1412 1413NOTICE: [data_node_3]: 1414SELECT * FROM test.show_constraints('some_dist_table') 1415NOTICE: [data_node_3]: 1416Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1417----------+----+-------+-----+----+----------+--------+--------- 1418(0 rows) 1419 1420 1421 remote_exec 1422------------- 1423 1424(1 row) 1425 1426DROP TABLE some_dist_table; 1427-- BEGIN/BEGIN/COMMIT/ROLLBACK 1428CREATE TABLE some_dist_table(time timestamptz, device int); 1429SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1430NOTICE: adding not-null constraint to column "time" 1431 hypertable_id | schema_name | table_name | created 1432---------------+-------------+-----------------+--------- 1433 14 | public | some_dist_table | t 1434(1 row) 1435 1436BEGIN; 1437SAVEPOINT a; 1438CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1439SAVEPOINT b; 1440ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1441ROLLBACK TO SAVEPOINT a; 1442ROLLBACK; 1443SELECT * FROM test.show_indexes('some_dist_table'); 1444 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1445--------------------------+---------+------+--------+---------+-----------+------------ 1446 some_dist_table_time_idx | {time} | | f | f | f | 1447(1 row) 1448 1449SELECT * FROM test.show_constraints('some_dist_table'); 1450 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1451------------+------+---------+-------+------+------------+----------+----------- 1452(0 rows) 1453 1454SELECT * FROM test.remote_exec(NULL, $$ 1455SELECT * FROM test.show_indexes('some_dist_table'); 1456SELECT * FROM test.show_constraints('some_dist_table'); 1457$$); 1458NOTICE: [data_node_1]: 1459SELECT * FROM test.show_indexes('some_dist_table') 1460NOTICE: [data_node_1]: 1461Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1462------------------------+-------+----+------+-------+---------+---------- 1463some_dist_table_time_idx|{time} | |f |f |f | 1464(1 row) 1465 1466 1467NOTICE: [data_node_1]: 1468SELECT * FROM test.show_constraints('some_dist_table') 1469NOTICE: [data_node_1]: 1470Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1471----------+----+-------+-----+----+----------+--------+--------- 1472(0 rows) 1473 1474 1475NOTICE: [data_node_2]: 1476SELECT * FROM test.show_indexes('some_dist_table') 1477NOTICE: [data_node_2]: 1478Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1479------------------------+-------+----+------+-------+---------+---------- 1480some_dist_table_time_idx|{time} | |f |f |f | 1481(1 row) 1482 1483 1484NOTICE: [data_node_2]: 1485SELECT * FROM test.show_constraints('some_dist_table') 1486NOTICE: [data_node_2]: 1487Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1488----------+----+-------+-----+----+----------+--------+--------- 1489(0 rows) 1490 1491 1492NOTICE: [data_node_3]: 1493SELECT * FROM test.show_indexes('some_dist_table') 1494NOTICE: [data_node_3]: 1495Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1496------------------------+-------+----+------+-------+---------+---------- 1497some_dist_table_time_idx|{time} | |f |f |f | 1498(1 row) 1499 1500 1501NOTICE: [data_node_3]: 1502SELECT * FROM test.show_constraints('some_dist_table') 1503NOTICE: [data_node_3]: 1504Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1505----------+----+-------+-----+----+----------+--------+--------- 1506(0 rows) 1507 1508 1509 remote_exec 1510------------- 1511 1512(1 row) 1513 1514DROP TABLE some_dist_table; 1515-- BEGIN/BEGIN/ROLLBACK/ROLLBACK 1516CREATE TABLE some_dist_table(time timestamptz, device int); 1517SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1518NOTICE: adding not-null constraint to column "time" 1519 hypertable_id | schema_name | table_name | created 1520---------------+-------------+-----------------+--------- 1521 15 | public | some_dist_table | t 1522(1 row) 1523 1524BEGIN; 1525SAVEPOINT a; 1526CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1527SAVEPOINT b; 1528ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1529ROLLBACK TO SAVEPOINT b; 1530ROLLBACK TO SAVEPOINT a; 1531ROLLBACK; 1532SELECT * FROM test.show_indexes('some_dist_table'); 1533 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1534--------------------------+---------+------+--------+---------+-----------+------------ 1535 some_dist_table_time_idx | {time} | | f | f | f | 1536(1 row) 1537 1538SELECT * FROM test.show_constraints('some_dist_table'); 1539 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1540------------+------+---------+-------+------+------------+----------+----------- 1541(0 rows) 1542 1543SELECT * FROM test.remote_exec(NULL, $$ 1544SELECT * FROM test.show_indexes('some_dist_table'); 1545SELECT * FROM test.show_constraints('some_dist_table'); 1546$$); 1547NOTICE: [data_node_1]: 1548SELECT * FROM test.show_indexes('some_dist_table') 1549NOTICE: [data_node_1]: 1550Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1551------------------------+-------+----+------+-------+---------+---------- 1552some_dist_table_time_idx|{time} | |f |f |f | 1553(1 row) 1554 1555 1556NOTICE: [data_node_1]: 1557SELECT * FROM test.show_constraints('some_dist_table') 1558NOTICE: [data_node_1]: 1559Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1560----------+----+-------+-----+----+----------+--------+--------- 1561(0 rows) 1562 1563 1564NOTICE: [data_node_2]: 1565SELECT * FROM test.show_indexes('some_dist_table') 1566NOTICE: [data_node_2]: 1567Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1568------------------------+-------+----+------+-------+---------+---------- 1569some_dist_table_time_idx|{time} | |f |f |f | 1570(1 row) 1571 1572 1573NOTICE: [data_node_2]: 1574SELECT * FROM test.show_constraints('some_dist_table') 1575NOTICE: [data_node_2]: 1576Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1577----------+----+-------+-----+----+----------+--------+--------- 1578(0 rows) 1579 1580 1581NOTICE: [data_node_3]: 1582SELECT * FROM test.show_indexes('some_dist_table') 1583NOTICE: [data_node_3]: 1584Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1585------------------------+-------+----+------+-------+---------+---------- 1586some_dist_table_time_idx|{time} | |f |f |f | 1587(1 row) 1588 1589 1590NOTICE: [data_node_3]: 1591SELECT * FROM test.show_constraints('some_dist_table') 1592NOTICE: [data_node_3]: 1593Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1594----------+----+-------+-----+----+----------+--------+--------- 1595(0 rows) 1596 1597 1598 remote_exec 1599------------- 1600 1601(1 row) 1602 1603DROP TABLE some_dist_table; 1604-- BEGIN/BEGIN/ABORT/ROLLBACK 1605CREATE TABLE some_dist_table(time timestamptz, device int); 1606SELECT * FROM create_hypertable('some_dist_table', 'time', replication_factor => 3); 1607NOTICE: adding not-null constraint to column "time" 1608 hypertable_id | schema_name | table_name | created 1609---------------+-------------+-----------------+--------- 1610 16 | public | some_dist_table | t 1611(1 row) 1612 1613BEGIN; 1614SAVEPOINT a; 1615CREATE INDEX some_dist_device_idx ON some_dist_table (device); 1616SAVEPOINT b; 1617ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1618\set ON_ERROR_STOP 0 1619ALTER TABLE some_dist_table ADD CONSTRAINT device_check CHECK (device > 0); 1620ERROR: constraint "device_check" for relation "some_dist_table" already exists 1621\set ON_ERROR_STOP 1 1622ROLLBACK TO SAVEPOINT b; 1623ROLLBACK TO SAVEPOINT a; 1624ROLLBACK; 1625SELECT * FROM test.show_indexes('some_dist_table'); 1626 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1627--------------------------+---------+------+--------+---------+-----------+------------ 1628 some_dist_table_time_idx | {time} | | f | f | f | 1629(1 row) 1630 1631SELECT * FROM test.show_constraints('some_dist_table'); 1632 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1633------------+------+---------+-------+------+------------+----------+----------- 1634(0 rows) 1635 1636SELECT * FROM test.remote_exec(NULL, $$ 1637SELECT * FROM test.show_indexes('some_dist_table'); 1638SELECT * FROM test.show_constraints('some_dist_table'); 1639$$); 1640NOTICE: [data_node_1]: 1641SELECT * FROM test.show_indexes('some_dist_table') 1642NOTICE: [data_node_1]: 1643Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1644------------------------+-------+----+------+-------+---------+---------- 1645some_dist_table_time_idx|{time} | |f |f |f | 1646(1 row) 1647 1648 1649NOTICE: [data_node_1]: 1650SELECT * FROM test.show_constraints('some_dist_table') 1651NOTICE: [data_node_1]: 1652Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1653----------+----+-------+-----+----+----------+--------+--------- 1654(0 rows) 1655 1656 1657NOTICE: [data_node_2]: 1658SELECT * FROM test.show_indexes('some_dist_table') 1659NOTICE: [data_node_2]: 1660Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1661------------------------+-------+----+------+-------+---------+---------- 1662some_dist_table_time_idx|{time} | |f |f |f | 1663(1 row) 1664 1665 1666NOTICE: [data_node_2]: 1667SELECT * FROM test.show_constraints('some_dist_table') 1668NOTICE: [data_node_2]: 1669Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1670----------+----+-------+-----+----+----------+--------+--------- 1671(0 rows) 1672 1673 1674NOTICE: [data_node_3]: 1675SELECT * FROM test.show_indexes('some_dist_table') 1676NOTICE: [data_node_3]: 1677Index |Columns|Expr|Unique|Primary|Exclusion|Tablespace 1678------------------------+-------+----+------+-------+---------+---------- 1679some_dist_table_time_idx|{time} | |f |f |f | 1680(1 row) 1681 1682 1683NOTICE: [data_node_3]: 1684SELECT * FROM test.show_constraints('some_dist_table') 1685NOTICE: [data_node_3]: 1686Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1687----------+----+-------+-----+----+----------+--------+--------- 1688(0 rows) 1689 1690 1691 remote_exec 1692------------- 1693 1694(1 row) 1695 1696DROP TABLE some_dist_table; 1697-- Test chunks updates 1698CREATE TABLE disttable(time timestamptz, device int, color int CONSTRAINT color_check CHECK (color > 0), temp float); 1699CREATE UNIQUE INDEX disttable_pk ON disttable(time); 1700SELECT * FROM create_hypertable('disttable', 'time', replication_factor => 3); 1701NOTICE: adding not-null constraint to column "time" 1702 hypertable_id | schema_name | table_name | created 1703---------------+-------------+------------+--------- 1704 17 | public | disttable | t 1705(1 row) 1706 1707INSERT INTO disttable VALUES ('2017-01-01 06:01', 0, 1, 0.0); 1708SELECT show_chunks('disttable'); 1709 show_chunks 1710---------------------------------------------- 1711 _timescaledb_internal._dist_hyper_17_6_chunk 1712(1 row) 1713 1714SELECT * FROM test.show_constraints('disttable'); 1715 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1716-------------+------+---------+-------+-------------+------------+----------+----------- 1717 color_check | c | {color} | - | (color > 0) | f | f | t 1718(1 row) 1719 1720SELECT (test.show_constraints(chunk)).* 1721FROM show_chunks('disttable') AS chunk; 1722 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1723--------------+------+---------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+----------- 1724 color_check | c | {color} | - | (color > 0) | f | f | t 1725 constraint_6 | c | {time} | - | (("time" >= 'Wed Dec 28 16:00:00 2016 PST'::timestamp with time zone) AND ("time" < 'Wed Jan 04 16:00:00 2017 PST'::timestamp with time zone)) | f | f | t 1726(2 rows) 1727 1728ALTER TABLE disttable DROP CONSTRAINT color_check; 1729SELECT * FROM test.show_constraints('disttable'); 1730 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1731------------+------+---------+-------+------+------------+----------+----------- 1732(0 rows) 1733 1734SELECT (test.show_constraints(chunk)).* 1735FROM show_chunks('disttable') AS chunk; 1736 Constraint | Type | Columns | Index | Expr | Deferrable | Deferred | Validated 1737--------------+------+---------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+----------- 1738 constraint_6 | c | {time} | - | (("time" >= 'Wed Dec 28 16:00:00 2016 PST'::timestamp with time zone) AND ("time" < 'Wed Jan 04 16:00:00 2017 PST'::timestamp with time zone)) | f | f | t 1739(1 row) 1740 1741SELECT * FROM test.remote_exec(NULL, $$ 1742SELECT show_chunks('disttable'); 1743SELECT * FROM test.show_constraints('disttable'); 1744SELECT (test.show_constraints(chunk)).* 1745FROM show_chunks('disttable') AS chunk; 1746$$); 1747NOTICE: [data_node_1]: 1748SELECT show_chunks('disttable') 1749NOTICE: [data_node_1]: 1750show_chunks 1751-------------------------------------------- 1752_timescaledb_internal._dist_hyper_17_6_chunk 1753(1 row) 1754 1755 1756NOTICE: [data_node_1]: 1757SELECT * FROM test.show_constraints('disttable') 1758NOTICE: [data_node_1]: 1759Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1760----------+----+-------+-----+----+----------+--------+--------- 1761(0 rows) 1762 1763 1764NOTICE: [data_node_1]: 1765SELECT (test.show_constraints(chunk)).* 1766FROM show_chunks('disttable') AS chunk 1767NOTICE: [data_node_1]: 1768Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 1769------------+----+-------+-----+----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+--------- 1770constraint_6|c |{time} |- |(("time" >= 'Wed Dec 28 16:00:00 2016 PST'::timestamp with time zone) AND ("time" < 'Wed Jan 04 16:00:00 2017 PST'::timestamp with time zone))|f |f |t 1771(1 row) 1772 1773 1774NOTICE: [data_node_2]: 1775SELECT show_chunks('disttable') 1776NOTICE: [data_node_2]: 1777show_chunks 1778-------------------------------------------- 1779_timescaledb_internal._dist_hyper_17_6_chunk 1780(1 row) 1781 1782 1783NOTICE: [data_node_2]: 1784SELECT * FROM test.show_constraints('disttable') 1785NOTICE: [data_node_2]: 1786Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1787----------+----+-------+-----+----+----------+--------+--------- 1788(0 rows) 1789 1790 1791NOTICE: [data_node_2]: 1792SELECT (test.show_constraints(chunk)).* 1793FROM show_chunks('disttable') AS chunk 1794NOTICE: [data_node_2]: 1795Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 1796------------+----+-------+-----+----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+--------- 1797constraint_6|c |{time} |- |(("time" >= 'Wed Dec 28 16:00:00 2016 PST'::timestamp with time zone) AND ("time" < 'Wed Jan 04 16:00:00 2017 PST'::timestamp with time zone))|f |f |t 1798(1 row) 1799 1800 1801NOTICE: [data_node_3]: 1802SELECT show_chunks('disttable') 1803NOTICE: [data_node_3]: 1804show_chunks 1805-------------------------------------------- 1806_timescaledb_internal._dist_hyper_17_6_chunk 1807(1 row) 1808 1809 1810NOTICE: [data_node_3]: 1811SELECT * FROM test.show_constraints('disttable') 1812NOTICE: [data_node_3]: 1813Constraint|Type|Columns|Index|Expr|Deferrable|Deferred|Validated 1814----------+----+-------+-----+----+----------+--------+--------- 1815(0 rows) 1816 1817 1818NOTICE: [data_node_3]: 1819SELECT (test.show_constraints(chunk)).* 1820FROM show_chunks('disttable') AS chunk 1821NOTICE: [data_node_3]: 1822Constraint |Type|Columns|Index|Expr |Deferrable|Deferred|Validated 1823------------+----+-------+-----+----------------------------------------------------------------------------------------------------------------------------------------------+----------+--------+--------- 1824constraint_6|c |{time} |- |(("time" >= 'Wed Dec 28 16:00:00 2016 PST'::timestamp with time zone) AND ("time" < 'Wed Jan 04 16:00:00 2017 PST'::timestamp with time zone))|f |f |t 1825(1 row) 1826 1827 1828 remote_exec 1829------------- 1830 1831(1 row) 1832 1833DROP TABLE disttable; 1834-- Test event triggers behaviour 1835CREATE OR REPLACE FUNCTION test_event_trigger_sql_drop_function() RETURNS event_trigger 1836LANGUAGE plpgsql AS $$ 1837BEGIN 1838 EXECUTE 'DROP TABLE IF EXISTS unexist_table'; 1839END 1840$$; 1841\c :TEST_DBNAME :ROLE_SUPERUSER; 1842CREATE EVENT TRIGGER test_event_trigger_sqldrop ON sql_drop 1843 WHEN TAG IN ('drop table') 1844 EXECUTE FUNCTION test_event_trigger_sql_drop_function(); 1845SET ROLE :ROLE_1; 1846-- Test DROP inside event trigger on local table (should not crash) 1847CREATE TABLE non_htable (id int PRIMARY KEY); 1848DROP TABLE non_htable; 1849NOTICE: table "unexist_table" does not exist, skipping 1850\c :TEST_DBNAME :ROLE_SUPERUSER; 1851DROP EVENT TRIGGER test_event_trigger_sqldrop; 1852SET ROLE :ROLE_1; 1853-- Test DDL blocking from non-frontend session 1854-- 1855-- We test only special corner cases since most of this functionality already 1856-- been tested before. 1857-- 1858CREATE TABLE disttable(time timestamptz, device int); 1859SELECT * FROM create_hypertable('disttable', 'time', replication_factor => 3); 1860NOTICE: adding not-null constraint to column "time" 1861 hypertable_id | schema_name | table_name | created 1862---------------+-------------+------------+--------- 1863 18 | public | disttable | t 1864(1 row) 1865 1866CREATE INDEX disttable_device_idx ON disttable (device); 1867-- Test alter replication factor on empty table 1868SELECT replication_factor FROM _timescaledb_catalog.hypertable ORDER BY id; 1869 replication_factor 1870-------------------- 1871 3 1872(1 row) 1873 1874SELECT * FROM set_replication_factor('disttable', 1); 1875 set_replication_factor 1876------------------------ 1877 1878(1 row) 1879 1880SELECT replication_factor FROM _timescaledb_catalog.hypertable ORDER BY id; 1881 replication_factor 1882-------------------- 1883 1 1884(1 row) 1885 1886SELECT * FROM set_replication_factor('disttable', 1); 1887 set_replication_factor 1888------------------------ 1889 1890(1 row) 1891 1892SELECT replication_factor FROM _timescaledb_catalog.hypertable ORDER BY id; 1893 replication_factor 1894-------------------- 1895 1 1896(1 row) 1897 1898SELECT * FROM set_replication_factor('disttable', 2); 1899 set_replication_factor 1900------------------------ 1901 1902(1 row) 1903 1904SELECT replication_factor FROM _timescaledb_catalog.hypertable ORDER BY id; 1905 replication_factor 1906-------------------- 1907 2 1908(1 row) 1909 1910\set ON_ERROR_STOP 0 1911SELECT * FROM set_replication_factor('disttable', 4); 1912ERROR: replication factor too large for hypertable "disttable" 1913SELECT * FROM set_replication_factor('disttable', 0); 1914ERROR: invalid replication factor 1915SELECT * FROM set_replication_factor('disttable', NULL); 1916ERROR: invalid replication factor 1917\set ON_ERROR_STOP 1 1918SELECT replication_factor FROM _timescaledb_catalog.hypertable ORDER BY id; 1919 replication_factor 1920-------------------- 1921 2 1922(1 row) 1923 1924\c :MY_DB1 1925SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'disttable'; 1926 schemaname | tablename 1927------------+----------- 1928 public | disttable 1929(1 row) 1930 1931SELECT * FROM test.show_indexes('disttable'); 1932 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1933----------------------+----------+------+--------+---------+-----------+------------ 1934 disttable_device_idx | {device} | | f | f | f | 1935 disttable_time_idx | {time} | | f | f | f | 1936(2 rows) 1937 1938\set ON_ERROR_STOP 0 1939-- fail to alter replication factor for the table on data node 1940SELECT * FROM set_replication_factor('disttable', 1); 1941ERROR: hypertable "disttable" is not distributed 1942-- Test TRUNCATE blocked on data node 1943TRUNCATE disttable; 1944ERROR: operation is blocked on a distributed hypertable member 1945-- Test ALTER by non-frontend session 1946ALTER TABLE disttable ADD CONSTRAINT device_check CHECK (device > 0); 1947ERROR: operation is blocked on a distributed hypertable member 1948-- Test path for delayed relid resolving 1949ALTER TABLE disttable RENAME TO disttable2; 1950ERROR: operation is blocked on a distributed hypertable member 1951-- Test for hypertables collected during drop 1952DROP INDEX disttable_device_idx; 1953ERROR: operation is blocked on a distributed hypertable member 1954DROP TABLE disttable; 1955ERROR: operation is blocked on a distributed hypertable member 1956\set ON_ERROR_STOP 1 1957-- Explicitly allow execution 1958SET timescaledb.enable_client_ddl_on_data_nodes TO true; 1959DROP INDEX disttable_device_idx; 1960SELECT * FROM test.show_indexes('disttable'); 1961 Index | Columns | Expr | Unique | Primary | Exclusion | Tablespace 1962--------------------+---------+------+--------+---------+-----------+------------ 1963 disttable_time_idx | {time} | | f | f | f | 1964(1 row) 1965 1966\c :TEST_DBNAME :ROLE_SUPERUSER; 1967SET ROLE :ROLE_1; 1968-- Should fail because of the inconsistency 1969\set ON_ERROR_STOP 0 1970DROP INDEX disttable_device_idx; 1971ERROR: [data_node_1]: index "disttable_device_idx" does not exist 1972\set ON_ERROR_STOP 1 1973DROP TABLE disttable; 1974-- cleanup 1975\c :TEST_DBNAME :ROLE_CLUSTER_SUPERUSER; 1976DROP SCHEMA disttable_schema CASCADE; 1977NOTICE: drop cascades to table disttable_schema.disttable 1978DROP DATABASE :MY_DB1; 1979DROP DATABASE :MY_DB2; 1980DROP DATABASE :MY_DB3; 1981