1ALTER SEQUENCE pg_catalog.pg_dist_shardid_seq RESTART 1250000; 2 3\c - - - :master_port 4CREATE TABLE reference_table_test (value_1 int, value_2 float, value_3 text, value_4 timestamp); 5SELECT create_reference_table('reference_table_test'); 6 7INSERT INTO reference_table_test VALUES (1, 1.0, '1', '2016-12-01'); 8INSERT INTO reference_table_test VALUES (2, 2.0, '2', '2016-12-02'); 9INSERT INTO reference_table_test VALUES (3, 3.0, '3', '2016-12-03'); 10INSERT INTO reference_table_test VALUES (4, 4.0, '4', '2016-12-04'); 11INSERT INTO reference_table_test VALUES (5, 5.0, '5', '2016-12-05'); 12 13-- SELECT .. FOR UPDATE should work on coordinator (takes lock on first worker) 14SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 15 16BEGIN; 17SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 18END; 19 20\c - - - :worker_1_port 21 22-- SELECT .. FOR UPDATE should work on first worker (takes lock on self) 23SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 24 25BEGIN; 26SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 27END; 28 29-- run some queries on top of the data 30SELECT 31 * 32FROM 33 reference_table_test; 34 35SELECT 36 * 37FROM 38 reference_table_test 39WHERE 40 value_1 = 1; 41 42SELECT 43 value_1, 44 value_2 45FROM 46 reference_table_test 47ORDER BY 48 2 ASC LIMIT 3; 49 50SELECT 51 value_1, value_3 52FROM 53 reference_table_test 54WHERE 55 value_2 >= 4 56ORDER BY 57 2 LIMIT 3; 58 59SELECT 60 value_1, 15 * value_2 61FROM 62 reference_table_test 63ORDER BY 64 2 ASC 65LIMIT 2; 66 67SELECT 68 value_1, 15 * value_2 69FROM 70 reference_table_test 71ORDER BY 72 2 ASC LIMIT 2 OFFSET 2; 73 74SELECT 75 value_2, value_4 76FROM 77 reference_table_test 78WHERE 79 value_2 = 2 OR value_2 = 3; 80 81SELECT 82 value_2, value_4 83FROM 84 reference_table_test 85WHERE 86 value_2 = 2 AND value_2 = 3; 87 88SELECT 89 value_2, value_4 90FROM 91 reference_table_test 92WHERE 93 value_3 = '2' OR value_1 = 3; 94 95SELECT 96 value_2, value_4 97FROM 98 reference_table_test 99WHERE 100 ( 101 value_3 = '2' OR value_1 = 3 102 ) 103 AND FALSE; 104 105SELECT 106 * 107FROM 108 reference_table_test 109WHERE 110 value_2 IN 111 ( 112 SELECT 113 value_3::FLOAT 114 FROM 115 reference_table_test 116 ) 117 AND value_1 < 3; 118 119SELECT 120 value_4 121FROM 122 reference_table_test 123WHERE 124 value_3 IN 125 ( 126 '1', '2' 127 ); 128 129SELECT 130 date_part('day', value_4) 131FROM 132 reference_table_test 133WHERE 134 value_3 IN 135 ( 136 '5', '2' 137 ); 138 139SELECT 140 value_4 141FROM 142 reference_table_test 143WHERE 144 value_2 <= 2 AND value_2 >= 4; 145 146SELECT 147 value_4 148FROM 149 reference_table_test 150WHERE 151 value_2 <= 20 AND value_2 >= 4; 152 153SELECT 154 value_4 155FROM 156 reference_table_test 157WHERE 158 value_2 >= 5 AND value_2 <= random(); 159 160SELECT 161 value_1 162FROM 163 reference_table_test 164WHERE 165 value_4 BETWEEN '2016-12-01' AND '2016-12-03'; 166 167SELECT 168 value_1 169FROM 170 reference_table_test 171WHERE 172 FALSE; 173SELECT 174 value_1 175FROM 176 reference_table_test 177WHERE 178 int4eq(1, 2); 179 180-- rename output name and do some operations 181SELECT 182 value_1 as id, value_2 * 15 as age 183FROM 184 reference_table_test; 185 186-- queries with CTEs are supported 187WITH some_data AS ( SELECT value_2, value_4 FROM reference_table_test WHERE value_2 >=3) 188SELECT 189 * 190FROM 191 some_data; 192 193-- queries with CTEs are supported even if CTE is not referenced inside query 194WITH some_data AS ( SELECT value_2, value_4 FROM reference_table_test WHERE value_2 >=3) 195SELECT * FROM reference_table_test ORDER BY 1 LIMIT 1; 196 197-- queries which involve functions in FROM clause are supported if it goes to a single worker. 198SELECT 199 * 200FROM 201 reference_table_test, position('om' in 'Thomas') 202WHERE 203 value_1 = 1; 204 205SELECT 206 * 207FROM 208 reference_table_test, position('om' in 'Thomas') 209WHERE 210 value_1 = 1 OR value_1 = 2; 211 212-- set operations are supported 213SELECT * FROM ( 214 SELECT * FROM reference_table_test WHERE value_1 = 1 215 UNION 216 SELECT * FROM reference_table_test WHERE value_1 = 3 217) AS combination 218ORDER BY value_1; 219 220SELECT * FROM ( 221 SELECT * FROM reference_table_test WHERE value_1 = 1 222 EXCEPT 223 SELECT * FROM reference_table_test WHERE value_1 = 3 224) AS combination 225ORDER BY value_1; 226 227SELECT * FROM ( 228 SELECT * FROM reference_table_test WHERE value_1 = 1 229 INTERSECT 230 SELECT * FROM reference_table_test WHERE value_1 = 3 231) AS combination 232ORDER BY value_1; 233 234-- to make the tests more interested for aggregation tests, ingest some more data 235\c - - - :master_port 236INSERT INTO reference_table_test VALUES (1, 1.0, '1', '2016-12-01'); 237INSERT INTO reference_table_test VALUES (2, 2.0, '2', '2016-12-02'); 238INSERT INTO reference_table_test VALUES (3, 3.0, '3', '2016-12-03'); 239\c - - - :worker_1_port 240 241-- some aggregations 242SELECT 243 value_4, SUM(value_2) 244FROM 245 reference_table_test 246GROUP BY 247 value_4 248HAVING 249 SUM(value_2) > 3 250ORDER BY 251 1; 252 253SELECT 254 value_4, 255 value_3, 256 SUM(value_2) 257FROM 258 reference_table_test 259GROUP BY 260 GROUPING sets ((value_4), (value_3)) 261ORDER BY 1, 2, 3; 262 263 264-- distinct clauses also work fine 265SELECT DISTINCT 266 value_4 267FROM 268 reference_table_test 269ORDER BY 270 1; 271 272-- window functions are also supported 273SELECT 274 value_4, RANK() OVER (PARTITION BY value_1 ORDER BY value_4) 275FROM 276 reference_table_test; 277 278-- window functions are also supported 279SELECT 280 value_4, AVG(value_1) OVER (PARTITION BY value_4 ORDER BY value_4) 281FROM 282 reference_table_test; 283 284SELECT 285 count(DISTINCT CASE 286 WHEN 287 value_2 >= 3 288 THEN 289 value_2 290 ELSE 291 NULL 292 END) as c 293 FROM 294 reference_table_test; 295 296SELECT 297 value_1, 298 count(DISTINCT CASE 299 WHEN 300 value_2 >= 3 301 THEN 302 value_2 303 ELSE 304 NULL 305 END) as c 306 FROM 307 reference_table_test 308 GROUP BY 309 value_1 310 ORDER BY 311 1; 312 313-- selects inside a transaction works fine as well 314 315BEGIN; 316SELECT * FROM reference_table_test; 317SELECT * FROM reference_table_test WHERE value_1 = 1; 318END; 319 320-- cursor queries also works fine 321BEGIN; 322DECLARE test_cursor CURSOR FOR 323 SELECT * 324 FROM reference_table_test 325 WHERE value_1 = 1 OR value_1 = 2 326 ORDER BY value_1; 327FETCH test_cursor; 328FETCH ALL test_cursor; 329FETCH test_cursor; -- fetch one row after the last 330FETCH BACKWARD test_cursor; 331END; 332 333-- table creation queries inside can be router plannable 334CREATE TEMP TABLE temp_reference_test as 335 SELECT * 336 FROM reference_table_test 337 WHERE value_1 = 1; 338 339\c - - - :master_port 340-- all kinds of joins are supported among reference tables 341-- first create two more tables 342CREATE TABLE reference_table_test_second (value_1 int, value_2 float, value_3 text, value_4 timestamp); 343SELECT create_reference_table('reference_table_test_second'); 344 345CREATE TABLE reference_table_test_third (value_1 int, value_2 float, value_3 text, value_4 timestamp); 346SELECT create_reference_table('reference_table_test_third'); 347 348-- ingest some data to both tables 349INSERT INTO reference_table_test_second VALUES (1, 1.0, '1', '2016-12-01'); 350INSERT INTO reference_table_test_second VALUES (2, 2.0, '2', '2016-12-02'); 351INSERT INTO reference_table_test_second VALUES (3, 3.0, '3', '2016-12-03'); 352 353INSERT INTO reference_table_test_third VALUES (4, 4.0, '4', '2016-12-04'); 354INSERT INTO reference_table_test_third VALUES (5, 5.0, '5', '2016-12-05'); 355 356\c - - - :worker_2_port 357 358-- SELECT .. FOR UPDATE should work on second worker (takes lock on first worker) 359SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 360 361BEGIN; 362SELECT value_1, value_2 FROM reference_table_test ORDER BY value_1, value_2 LIMIT 1 FOR UPDATE; 363END; 364 365-- some very basic tests 366SELECT 367 DISTINCT t1.value_1 368FROM 369 reference_table_test t1, reference_table_test_second t2 370WHERE 371 t1.value_2 = t2.value_2 372ORDER BY 373 1; 374 375SELECT 376 DISTINCT t1.value_1 377FROM 378 reference_table_test t1, reference_table_test_third t3 379WHERE 380 t1.value_2 = t3.value_2 381ORDER BY 382 1; 383 384SELECT 385 DISTINCT t2.value_1 386FROM 387 reference_table_test_second t2, reference_table_test_third t3 388WHERE 389 t2.value_2 = t3.value_2 390ORDER BY 391 1; 392 393-- join on different columns and different data types via casts 394SELECT 395 DISTINCT t1.value_1 396FROM 397 reference_table_test t1, reference_table_test_second t2 398WHERE 399 t1.value_2 = t2.value_1 400ORDER BY 401 1; 402 403SELECT 404 DISTINCT t1.value_1 405FROM 406 reference_table_test t1, reference_table_test_second t2 407WHERE 408 t1.value_2 = t2.value_3::int 409ORDER BY 410 1; 411 412SELECT 413 DISTINCT t1.value_1 414FROM 415 reference_table_test t1, reference_table_test_second t2 416WHERE 417 t1.value_2 = date_part('day', t2.value_4) 418ORDER BY 419 1; 420 421-- ingest a common row to see more meaningful results with joins involving 3 tables 422\c - - - :master_port 423INSERT INTO reference_table_test_third VALUES (3, 3.0, '3', '2016-12-03'); 424\c - - - :worker_1_port 425 426SELECT 427 DISTINCT t1.value_1 428FROM 429 reference_table_test t1, reference_table_test_second t2, reference_table_test_third t3 430WHERE 431 t1.value_2 = date_part('day', t2.value_4) AND t3.value_2 = t1.value_2 432ORDER BY 433 1; 434 435-- same query on different columns 436SELECT 437 DISTINCT t1.value_1 438FROM 439 reference_table_test t1, reference_table_test_second t2, reference_table_test_third t3 440WHERE 441 t1.value_1 = date_part('day', t2.value_4) AND t3.value_2 = t1.value_1 442ORDER BY 443 1; 444 445-- with the JOIN syntax 446SELECT 447 DISTINCT t1.value_1 448FROM 449 reference_table_test t1 JOIN reference_table_test_second t2 USING (value_1) 450 JOIN reference_table_test_third t3 USING (value_1) 451ORDER BY 452 1; 453 454-- and left/right joins 455SELECT 456 DISTINCT t1.value_1 457FROM 458 reference_table_test t1 LEFT JOIN reference_table_test_second t2 USING (value_1) 459 LEFT JOIN reference_table_test_third t3 USING (value_1) 460ORDER BY 461 1; 462 463SELECT 464 DISTINCT t1.value_1 465FROM 466 reference_table_test t1 RIGHT JOIN reference_table_test_second t2 USING (value_1) 467 RIGHT JOIN reference_table_test_third t3 USING (value_1) 468ORDER BY 469 1; 470 471\c - - - :master_port 472SET citus.shard_count TO 6; 473SET citus.shard_replication_factor TO 1; 474 475CREATE TABLE colocated_table_test (value_1 int, value_2 float, value_3 text, value_4 timestamp); 476SELECT create_distributed_table('colocated_table_test', 'value_1'); 477 478CREATE TABLE colocated_table_test_2 (value_1 int, value_2 float, value_3 text, value_4 timestamp); 479SELECT create_distributed_table('colocated_table_test_2', 'value_1'); 480 481DELETE FROM reference_table_test; 482INSERT INTO reference_table_test VALUES (1, 1.0, '1', '2016-12-01'); 483INSERT INTO reference_table_test VALUES (2, 2.0, '2', '2016-12-02'); 484 485INSERT INTO colocated_table_test VALUES (1, 1.0, '1', '2016-12-01'); 486INSERT INTO colocated_table_test VALUES (2, 2.0, '2', '2016-12-02'); 487 488INSERT INTO colocated_table_test_2 VALUES (1, 1.0, '1', '2016-12-01'); 489INSERT INTO colocated_table_test_2 VALUES (2, 2.0, '2', '2016-12-02'); 490 491\c - - - :worker_1_port 492SET client_min_messages TO DEBUG1; 493SET citus.log_multi_join_order TO TRUE; 494 495SELECT 496 reference_table_test.value_1 497FROM 498 reference_table_test, colocated_table_test 499WHERE 500 colocated_table_test.value_1 = reference_table_test.value_1 501ORDER BY 1; 502 503SELECT 504 colocated_table_test.value_2 505FROM 506 reference_table_test, colocated_table_test 507WHERE 508 colocated_table_test.value_2 = reference_table_test.value_2 509ORDER BY 1; 510 511SELECT 512 colocated_table_test.value_2 513FROM 514 colocated_table_test, reference_table_test 515WHERE 516 reference_table_test.value_1 = colocated_table_test.value_1 517ORDER BY 1; 518 519 520SET citus.enable_repartition_joins = on; 521SELECT 522 colocated_table_test.value_2 523FROM 524 reference_table_test, colocated_table_test, colocated_table_test_2 525WHERE 526 colocated_table_test.value_2 = reference_table_test.value_2 527ORDER BY colocated_table_test.value_2; 528RESET citus.enable_repartition_joins; 529 530SELECT 531 colocated_table_test.value_2 532FROM 533 reference_table_test, colocated_table_test, colocated_table_test_2 534WHERE 535 colocated_table_test.value_1 = colocated_table_test_2.value_1 AND colocated_table_test.value_2 = reference_table_test.value_2 536ORDER BY 1; 537 538SET citus.enable_repartition_joins to ON; 539SELECT 540 colocated_table_test.value_2 541FROM 542 reference_table_test, colocated_table_test, colocated_table_test_2 543WHERE 544 colocated_table_test.value_2 = colocated_table_test_2.value_2 AND colocated_table_test.value_2 = reference_table_test.value_2 545ORDER BY 1; 546 547SELECT 548 reference_table_test.value_2 549FROM 550 reference_table_test, colocated_table_test, colocated_table_test_2 551WHERE 552 colocated_table_test.value_1 = reference_table_test.value_1 AND colocated_table_test_2.value_1 = reference_table_test.value_1 553ORDER BY 1; 554 555 556SET client_min_messages TO NOTICE; 557SET citus.log_multi_join_order TO FALSE; 558 559\c - - - :master_port 560 561-- issue 3766 562CREATE TABLE numbers(a int); 563SELECT create_reference_table('numbers'); 564SET log_min_messages TO debug4; 565INSERT INTO numbers VALUES (1), (2), (3), (4); 566SELECT count(*) FROM numbers; 567RESET log_min_messages; 568 569-- clean up tables 570DROP TABLE reference_table_test, reference_table_test_second, reference_table_test_third, numbers; 571