1\set VERBOSITY terse 2SET citus.next_shard_id TO 1509000; 3SET citus.shard_replication_factor TO 1; 4SET citus.enable_local_execution TO ON; 5SET citus.log_local_commands TO ON; 6CREATE SCHEMA citus_local_table_queries; 7SET search_path TO citus_local_table_queries; 8-- ensure that coordinator is added to pg_dist_node 9SET client_min_messages to ERROR; 10SELECT 1 FROM master_add_node('localhost', :master_port, groupId => 0); 11 ?column? 12--------------------------------------------------------------------- 13 1 14(1 row) 15 16RESET client_min_messages; 17CREATE TABLE dummy_reference_table(a int unique, b int); 18SELECT create_reference_table('dummy_reference_table'); 19 create_reference_table 20--------------------------------------------------------------------- 21 22(1 row) 23 24CREATE TABLE citus_local_table(a int, b int); 25ALTER TABLE citus_local_table ADD CONSTRAINT fkey_to_dummy_1 FOREIGN KEY (a) REFERENCES dummy_reference_table(a); 26NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1509001, 'citus_local_table_queries', 1509000, 'citus_local_table_queries', 'ALTER TABLE citus_local_table ADD CONSTRAINT fkey_to_dummy_1 FOREIGN KEY (a) REFERENCES dummy_reference_table(a);') 27CREATE TABLE citus_local_table_2(a int, b int); 28ALTER TABLE citus_local_table_2 ADD CONSTRAINT fkey_to_dummy_2 FOREIGN KEY (a) REFERENCES dummy_reference_table(a); 29NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1509002, 'citus_local_table_queries', 1509000, 'citus_local_table_queries', 'ALTER TABLE citus_local_table_2 ADD CONSTRAINT fkey_to_dummy_2 FOREIGN KEY (a) REFERENCES dummy_reference_table(a);') 30CREATE TABLE reference_table(a int, b int); 31SELECT create_reference_table('reference_table'); 32 create_reference_table 33--------------------------------------------------------------------- 34 35(1 row) 36 37CREATE TABLE distributed_table(a int, b int); 38SELECT create_distributed_table('distributed_table', 'a'); 39 create_distributed_table 40--------------------------------------------------------------------- 41 42(1 row) 43 44CREATE TABLE postgres_local_table(a int, b int); 45-- Define a helper function to truncate & insert some data into our test tables 46-- We should call this function at some places in this test file to prevent 47-- test to take a long time. 48-- We shouldn't use LIMIT in INSERT SELECT queries to make the test faster as 49-- LIMIT would force planner to wrap SELECT query in an intermediate result and 50-- this might reduce the coverage of the test cases. 51CREATE FUNCTION clear_and_init_test_tables() RETURNS void AS $$ 52 BEGIN 53 SET client_min_messages to ERROR; 54 55 TRUNCATE postgres_local_table, citus_local_table, reference_table, distributed_table, dummy_reference_table, citus_local_table_2; 56 57 INSERT INTO dummy_reference_table SELECT i, i FROM generate_series(0, 5) i; 58 INSERT INTO citus_local_table SELECT i, i FROM generate_series(0, 5) i; 59 INSERT INTO citus_local_table_2 SELECT i, i FROM generate_series(0, 5) i; 60 INSERT INTO postgres_local_table SELECT i, i FROM generate_series(0, 5) i; 61 INSERT INTO distributed_table SELECT i, i FROM generate_series(0, 5) i; 62 INSERT INTO reference_table SELECT i, i FROM generate_series(0, 5) i; 63 64 RESET client_min_messages; 65 END; 66$$ LANGUAGE plpgsql; 67--------------------------------------------------------------------- 68---- SELECT ---- 69--------------------------------------------------------------------- 70SELECT clear_and_init_test_tables(); 71 clear_and_init_test_tables 72--------------------------------------------------------------------- 73 74(1 row) 75 76-- join between citus local tables and reference tables would succeed 77SELECT count(*) FROM citus_local_table, reference_table WHERE citus_local_table.a = reference_table.a; 78NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.reference_table_1509003 reference_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) 79 count 80--------------------------------------------------------------------- 81 6 82(1 row) 83 84SELECT * FROM citus_local_table, reference_table WHERE citus_local_table.a = reference_table.a ORDER BY 1,2,3,4 FOR UPDATE; 85NOTICE: executing the command locally: SELECT citus_local_table.a, citus_local_table.b, reference_table.a, reference_table.b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.reference_table_1509003 reference_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) ORDER BY citus_local_table.a, citus_local_table.b, reference_table.a, reference_table.b FOR UPDATE OF citus_local_table FOR UPDATE OF reference_table 86 a | b | a | b 87--------------------------------------------------------------------- 88 0 | 0 | 0 | 0 89 1 | 1 | 1 | 1 90 2 | 2 | 2 | 2 91 3 | 3 | 3 | 3 92 4 | 4 | 4 | 4 93 5 | 5 | 5 | 5 94(6 rows) 95 96-- should work 97WITH cte_1 AS 98 (SELECT * FROM citus_local_table, reference_table WHERE citus_local_table.a = reference_table.a ORDER BY 1,2,3,4 FOR UPDATE) 99SELECT count(*) FROM cte_1; 100NOTICE: executing the command locally: WITH cte_1 AS (SELECT citus_local_table.a, citus_local_table.b, reference_table.a, reference_table.b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.reference_table_1509003 reference_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) ORDER BY citus_local_table.a, citus_local_table.b, reference_table.a, reference_table.b FOR UPDATE OF citus_local_table FOR UPDATE OF reference_table) SELECT count(*) AS count FROM cte_1 cte_1(a, b, a_1, b_1) 101 count 102--------------------------------------------------------------------- 103 6 104(1 row) 105 106-- should work as joins are between ctes 107WITH cte_citus_local_table AS 108 (SELECT * FROM citus_local_table), 109cte_postgres_local_table AS 110 (SELECT * FROM postgres_local_table), 111cte_distributed_table AS 112 (SELECT * FROM distributed_table) 113SELECT count(*) FROM cte_distributed_table, cte_citus_local_table, cte_postgres_local_table 114WHERE cte_citus_local_table.a = 1 AND cte_distributed_table.a = 1; 115NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 116 count 117--------------------------------------------------------------------- 118 6 119(1 row) 120 121-- should fail as we don't support direct joins between distributed/local tables 122SELECT count(*) FROM distributed_table d1, distributed_table d2, citus_local_table; 123ERROR: complex joins are only supported when all distributed tables are co-located and joined on their distribution columns 124-- local table inside subquery should just work 125SELECT count(*) FROM 126( 127 SELECT * FROM (SELECT * FROM citus_local_table) as subquery_inner 128) as subquery_top; 129NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT subquery_inner.a, subquery_inner.b FROM (SELECT citus_local_table.a, citus_local_table.b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table) subquery_inner) subquery_top 130 count 131--------------------------------------------------------------------- 132 6 133(1 row) 134 135SELECT clear_and_init_test_tables(); 136 clear_and_init_test_tables 137--------------------------------------------------------------------- 138 139(1 row) 140 141-- join between citus/postgres local tables should just work 142SELECT count(*) FROM 143( 144 SELECT * FROM (SELECT count(*) FROM citus_local_table, postgres_local_table) as subquery_inner 145) as subquery_top; 146NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT subquery_inner.count FROM (SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.postgres_local_table) subquery_inner) subquery_top 147 count 148--------------------------------------------------------------------- 149 1 150(1 row) 151 152-- should fail as we don't support direct joins between distributed/local tables 153SELECT count(*) FROM 154( 155 SELECT *, random() FROM (SELECT *, random() FROM citus_local_table, distributed_table) as subquery_inner 156) as subquery_top; 157NOTICE: executing the command locally: SELECT NULL::integer AS "dummy-1" FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 158 count 159--------------------------------------------------------------------- 160 36 161(1 row) 162 163-- should fail as we don't support direct joins between distributed/local tables 164SELECT count(*) FROM 165( 166 SELECT *, random() 167 FROM ( 168 WITH cte_1 AS (SELECT *, random() FROM citus_local_table, distributed_table) SELECT * FROM cte_1) as subquery_inner 169) as subquery_top; 170NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 171NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT subquery_inner.a, subquery_inner.b, subquery_inner.a_1 AS a, subquery_inner.b_1 AS b, subquery_inner.random, random() AS random FROM (SELECT cte_1.a, cte_1.b, cte_1.a_1 AS a, cte_1.b_1 AS b, cte_1.random FROM (SELECT intermediate_result.a, intermediate_result.b, intermediate_result.a_1 AS a, intermediate_result.b_1 AS b, intermediate_result.random FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer, a_1 integer, b_1 integer, random double precision)) cte_1(a, b, a_1, b_1, random)) subquery_inner(a, b, a_1, b_1, random)) subquery_top(a, b, a_1, b_1, random, random_1) 172 count 173--------------------------------------------------------------------- 174 36 175(1 row) 176 177-- should be fine 178SELECT count(*) FROM 179( 180 SELECT *, random() 181 FROM ( 182 WITH cte_1 AS (SELECT *, random() FROM citus_local_table), cte_2 AS (SELECT * FROM distributed_table) SELECT count(*) FROM cte_1, cte_2 183 ) as subquery_inner 184) as subquery_top; 185NOTICE: executing the command locally: SELECT a, b, random() AS random FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 186NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT subquery_inner.count, random() AS random FROM (SELECT intermediate_result.count FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(count bigint)) subquery_inner) subquery_top 187 count 188--------------------------------------------------------------------- 189 1 190(1 row) 191 192SELECT clear_and_init_test_tables(); 193 clear_and_init_test_tables 194--------------------------------------------------------------------- 195 196(1 row) 197 198-- prepared statement 199PREPARE citus_local_only AS SELECT count(*) FROM citus_local_table; 200-- execute 6 times, local tables without params 201EXECUTE citus_local_only; 202NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 203 count 204--------------------------------------------------------------------- 205 6 206(1 row) 207 208EXECUTE citus_local_only; 209NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 210 count 211--------------------------------------------------------------------- 212 6 213(1 row) 214 215EXECUTE citus_local_only; 216NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 217 count 218--------------------------------------------------------------------- 219 6 220(1 row) 221 222EXECUTE citus_local_only; 223NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 224 count 225--------------------------------------------------------------------- 226 6 227(1 row) 228 229EXECUTE citus_local_only; 230NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 231 count 232--------------------------------------------------------------------- 233 6 234(1 row) 235 236EXECUTE citus_local_only; 237NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 238 count 239--------------------------------------------------------------------- 240 6 241(1 row) 242 243-- execute 6 times, with param 244PREPARE citus_local_only_p(int) AS SELECT count(*) FROM citus_local_table WHERE a = $1; 245EXECUTE citus_local_only_p(1); 246NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 247 count 248--------------------------------------------------------------------- 249 1 250(1 row) 251 252EXECUTE citus_local_only_p(1); 253NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 254 count 255--------------------------------------------------------------------- 256 1 257(1 row) 258 259EXECUTE citus_local_only_p(1); 260NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 261 count 262--------------------------------------------------------------------- 263 1 264(1 row) 265 266EXECUTE citus_local_only_p(1); 267NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 268 count 269--------------------------------------------------------------------- 270 1 271(1 row) 272 273EXECUTE citus_local_only_p(1); 274NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 275 count 276--------------------------------------------------------------------- 277 1 278(1 row) 279 280EXECUTE citus_local_only_p(1); 281NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 282 count 283--------------------------------------------------------------------- 284 1 285(1 row) 286 287-- do not evalute the function 288-- show the logs 289EXECUTE citus_local_only_p(random()); 290NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 291 count 292--------------------------------------------------------------------- 293 1 294(1 row) 295 296EXECUTE citus_local_only_p(random()); 297NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) $1) 298 count 299--------------------------------------------------------------------- 300 1 301(1 row) 302 303PREPARE mixed_query(int, int, int) AS 304 WITH cte_citus_local_table AS 305 (SELECT * FROM citus_local_table WHERE a = $1), 306 cte_postgres_local_table AS 307 (SELECT * FROM postgres_local_table WHERE a = $2), 308 cte_distributed_table AS 309 (SELECT * FROM distributed_table WHERE a = $3), 310 cte_mixes AS (SELECT * FROM cte_distributed_table, cte_citus_local_table, cte_postgres_local_table) 311 SELECT count(*) FROM cte_mixes; 312EXECUTE mixed_query(1,2,3); 313NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 314 count 315--------------------------------------------------------------------- 316 1 317(1 row) 318 319EXECUTE mixed_query(1,2,3); 320NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 321 count 322--------------------------------------------------------------------- 323 1 324(1 row) 325 326EXECUTE mixed_query(1,2,3); 327NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 328 count 329--------------------------------------------------------------------- 330 1 331(1 row) 332 333EXECUTE mixed_query(1,2,3); 334NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 335 count 336--------------------------------------------------------------------- 337 1 338(1 row) 339 340EXECUTE mixed_query(1,2,3); 341NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 342 count 343--------------------------------------------------------------------- 344 1 345(1 row) 346 347EXECUTE mixed_query(1,2,3); 348NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 349 count 350--------------------------------------------------------------------- 351 1 352(1 row) 353 354EXECUTE mixed_query(1,2,3); 355NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) 1) 356 count 357--------------------------------------------------------------------- 358 1 359(1 row) 360 361SELECT clear_and_init_test_tables(); 362 clear_and_init_test_tables 363--------------------------------------------------------------------- 364 365(1 row) 366 367-- anonymous columns 368WITH a AS (SELECT a, '' FROM citus_local_table GROUP BY a) SELECT a.a FROM a ORDER BY 1 LIMIT 5; 369NOTICE: executing the command locally: SELECT a FROM (SELECT citus_local_table.a, ''::text FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table GROUP BY citus_local_table.a) a(a, "?column?") ORDER BY a LIMIT 5 370 a 371--------------------------------------------------------------------- 372 0 373 1 374 2 375 3 376 4 377(5 rows) 378 379WITH a AS (SELECT b, '' FROM citus_local_table WHERE a = 1) SELECT * FROM a, a b ORDER BY 1 LIMIT 5; 380NOTICE: executing the command locally: WITH a AS (SELECT citus_local_table.b, ''::text FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) 1)) SELECT a.b, a."?column?", b.b, b."?column?" FROM a a(b, "?column?"), a b(b, "?column?") ORDER BY a.b LIMIT 5 381 b | ?column? | b | ?column? 382--------------------------------------------------------------------- 383 1 | | 1 | 384(1 row) 385 386-- weird expression on citus/pg table joins should be fine 387SELECT * FROM citus_local_table, postgres_local_table 388WHERE citus_local_table.a - postgres_local_table.a = 0 389ORDER BY 1,2,3,4 390LIMIT 10; 391NOTICE: executing the command locally: SELECT citus_local_table.a, citus_local_table.b, postgres_local_table.a, postgres_local_table.b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.postgres_local_table WHERE ((citus_local_table.a OPERATOR(pg_catalog.-) postgres_local_table.a) OPERATOR(pg_catalog.=) 0) ORDER BY citus_local_table.a, citus_local_table.b, postgres_local_table.a, postgres_local_table.b LIMIT 10 392 a | b | a | b 393--------------------------------------------------------------------- 394 0 | 0 | 0 | 0 395 1 | 1 | 1 | 1 396 2 | 2 | 2 | 2 397 3 | 3 | 3 | 3 398 4 | 4 | 4 | 4 399 5 | 5 | 5 | 5 400(6 rows) 401 402-- set operations should just work 403SELECT * FROM citus_local_table UNION SELECT * FROM postgres_local_table UNION SELECT * FROM distributed_table ORDER BY 1,2; 404NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 405NOTICE: executing the command locally: SELECT intermediate_result.a, intermediate_result.b FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer) UNION SELECT intermediate_result.a, intermediate_result.b FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer) UNION SELECT intermediate_result.a, intermediate_result.b FROM read_intermediate_result('XXX_3'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer) ORDER BY 1, 2 406 a | b 407--------------------------------------------------------------------- 408 0 | 0 409 1 | 1 410 2 | 2 411 3 | 3 412 4 | 4 413 5 | 5 414(6 rows) 415 416(SELECT * FROM citus_local_table ORDER BY 1,2 LIMIT 5) INTERSECT (SELECT i, i FROM generate_series(0, 100) i) ORDER BY 1, 2; 417NOTICE: executing the command locally: (SELECT citus_local_table.a, citus_local_table.b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table ORDER BY citus_local_table.a, citus_local_table.b LIMIT 5) INTERSECT SELECT i.i, i.i FROM generate_series(0, 100) i(i) ORDER BY 1, 2 418 a | b 419--------------------------------------------------------------------- 420 0 | 0 421 1 | 1 422 2 | 2 423 3 | 3 424 4 | 4 425(5 rows) 426 427-- should just work as recursive planner kicks in 428SELECT count(*) FROM distributed_table WHERE a IN (SELECT a FROM citus_local_table); 429NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 430 count 431--------------------------------------------------------------------- 432 6 433(1 row) 434 435SELECT count(*) FROM citus_local_table WHERE a IN (SELECT a FROM distributed_table); 436NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer))) 437 count 438--------------------------------------------------------------------- 439 6 440(1 row) 441 442SELECT count(*) FROM reference_table WHERE a IN (SELECT a FROM citus_local_table); 443NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.reference_table_1509003 reference_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT citus_local_table.a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table)) 444 count 445--------------------------------------------------------------------- 446 6 447(1 row) 448 449SELECT count(*) FROM citus_local_table WHERE a IN (SELECT a FROM reference_table); 450NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT reference_table.a FROM citus_local_table_queries.reference_table_1509003 reference_table)) 451 count 452--------------------------------------------------------------------- 453 6 454(1 row) 455 456-- nested recursive queries should just work 457SELECT count(*) FROM citus_local_table 458 WHERE a IN 459 (SELECT a FROM distributed_table WHERE a IN 460 (SELECT b FROM citus_local_table WHERE b IN (SELECT b FROM postgres_local_table))); 461NOTICE: executing the command locally: SELECT b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (b OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.b FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(b integer))) 462NOTICE: executing the command locally: SELECT count(*) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.a FROM read_intermediate_result('XXX_3'::text, 'binary'::citus_copy_format) intermediate_result(a integer))) 463 count 464--------------------------------------------------------------------- 465 6 466(1 row) 467 468-- local outer joins 469SELECT count(*) FROM citus_local_table LEFT JOIN reference_table ON (true); 470NOTICE: executing the command locally: SELECT count(*) AS count FROM (citus_local_table_queries.citus_local_table_1509001 citus_local_table LEFT JOIN citus_local_table_queries.reference_table_1509003 reference_table ON (true)) 471 count 472--------------------------------------------------------------------- 473 36 474(1 row) 475 476SELECT count(*) FROM reference_table 477 LEFT JOIN citus_local_table ON (true) 478 LEFT JOIN postgres_local_table ON (true) 479 LEFT JOIN reference_table r2 ON (true); 480NOTICE: executing the command locally: SELECT count(*) AS count FROM (((citus_local_table_queries.reference_table_1509003 reference_table LEFT JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) LEFT JOIN citus_local_table_queries.postgres_local_table ON (true)) LEFT JOIN citus_local_table_queries.reference_table_1509003 r2 ON (true)) 481 count 482--------------------------------------------------------------------- 483 1296 484(1 row) 485 486-- not supported direct outer join 487SELECT count(*) FROM citus_local_table LEFT JOIN distributed_table ON (true); 488ERROR: cannot pushdown the subquery 489-- distinct in subquery on CTE 490WITH one_row AS ( 491 SELECT a from citus_local_table WHERE b = 1 492) 493SELECT 494 * 495FROM 496 distributed_table 497WHERE 498 b IN (SELECT DISTINCT a FROM one_row) 499ORDER BY 500 1, 2 501LIMIT 502 1; 503NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (b OPERATOR(pg_catalog.=) 1) 504 a | b 505--------------------------------------------------------------------- 506 1 | 1 507(1 row) 508 509WITH one_row_2 AS ( 510 SELECT a from distributed_table WHERE b = 1 511) 512SELECT 513 * 514FROM 515 citus_local_table 516WHERE 517 b IN (SELECT DISTINCT a FROM one_row_2) 518ORDER BY 519 1 ,2 520LIMIT 521 1; 522NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (b OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer))) ORDER BY a, b LIMIT 1 523 a | b 524--------------------------------------------------------------------- 525 1 | 1 526(1 row) 527 528-- join between citus local tables and distributed tables would fail 529SELECT count(*) FROM citus_local_table, distributed_table; 530NOTICE: executing the command locally: SELECT NULL::integer AS "dummy-1" FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 531 count 532--------------------------------------------------------------------- 533 36 534(1 row) 535 536SELECT * FROM citus_local_table, distributed_table ORDER BY 1,2,3,4 FOR UPDATE; 537ERROR: could not run distributed query with FOR UPDATE/SHARE commands 538-- join between citus local tables and postgres local tables are okey 539SELECT count(citus_local_table.b), count(postgres_local_table.a) 540FROM citus_local_table, postgres_local_table 541WHERE citus_local_table.a = postgres_local_table.b; 542NOTICE: executing the command locally: SELECT count(citus_local_table.b) AS count, count(postgres_local_table.a) AS count FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table, citus_local_table_queries.postgres_local_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) postgres_local_table.b) 543 count | count 544--------------------------------------------------------------------- 545 6 | 6 546(1 row) 547 548-- select for update is just OK 549SELECT * FROM citus_local_table ORDER BY 1,2 FOR UPDATE; 550NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table ORDER BY a, b FOR UPDATE OF citus_local_table 551 a | b 552--------------------------------------------------------------------- 553 0 | 0 554 1 | 1 555 2 | 2 556 3 | 3 557 4 | 4 558 5 | 5 559(6 rows) 560 561--------------------------------------------------------------------- 562----- INSERT SELECT ----- 563--------------------------------------------------------------------- 564-- simple INSERT SELECT is OK 565SELECT clear_and_init_test_tables(); 566 clear_and_init_test_tables 567--------------------------------------------------------------------- 568 569(1 row) 570 571INSERT INTO citus_local_table 572SELECT * from reference_table; 573NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a, b) SELECT a, b FROM citus_local_table_queries.reference_table_1509003 reference_table 574INSERT INTO reference_table 575SELECT * from citus_local_table; 576NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 577NOTICE: executing the copy locally for shard xxxxx 578INSERT INTO citus_local_table 579SELECT * from distributed_table; 580NOTICE: executing the copy locally for shard xxxxx 581INSERT INTO distributed_table 582SELECT * from citus_local_table; 583NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 584INSERT INTO citus_local_table 585SELECT * from citus_local_table_2; 586NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a, b) SELECT a, b FROM citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2 587INSERT INTO citus_local_table 588SELECT * from citus_local_table_2 589ORDER BY 1,2 590LIMIT 10; 591NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2 ORDER BY a, b LIMIT 10 592NOTICE: executing the copy locally for shard xxxxx 593INSERT INTO citus_local_table 594SELECT * from postgres_local_table; 595NOTICE: executing the copy locally for shard xxxxx 596INSERT INTO postgres_local_table 597SELECT * from citus_local_table; 598NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 599-- INSERT SELECT with local joins are OK 600SELECT clear_and_init_test_tables(); 601 clear_and_init_test_tables 602--------------------------------------------------------------------- 603 604(1 row) 605 606INSERT INTO citus_local_table 607SELECT reference_table.* FROM reference_table 608JOIN citus_local_table ON (true); 609NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a, b) SELECT reference_table.a, reference_table.b FROM (citus_local_table_queries.reference_table_1509003 reference_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 610INSERT INTO reference_table 611SELECT reference_table.* FROM reference_table 612JOIN citus_local_table ON (true); 613NOTICE: executing the command locally: SELECT reference_table.a, reference_table.b FROM (citus_local_table_queries.reference_table_1509003 reference_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 614NOTICE: executing the copy locally for shard xxxxx 615INSERT INTO reference_table 616SELECT reference_table.* FROM reference_table, postgres_local_table 617JOIN citus_local_table ON (true); 618NOTICE: executing the command locally: SELECT reference_table.a, reference_table.b FROM citus_local_table_queries.reference_table_1509003 reference_table, (citus_local_table_queries.postgres_local_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 619NOTICE: executing the copy locally for shard xxxxx 620SELECT clear_and_init_test_tables(); 621 clear_and_init_test_tables 622--------------------------------------------------------------------- 623 624(1 row) 625 626INSERT INTO distributed_table 627SELECT reference_table.* FROM reference_table 628JOIN citus_local_table ON (true); 629NOTICE: executing the command locally: SELECT reference_table.a, reference_table.b FROM (citus_local_table_queries.reference_table_1509003 reference_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 630INSERT INTO distributed_table 631SELECT reference_table.* FROM reference_table, postgres_local_table 632JOIN citus_local_table ON (true); 633NOTICE: executing the command locally: SELECT reference_table.a, reference_table.b FROM citus_local_table_queries.reference_table_1509003 reference_table, (citus_local_table_queries.postgres_local_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 634INSERT INTO postgres_local_table 635SELECT reference_table.* FROM reference_table 636JOIN citus_local_table ON (true); 637NOTICE: executing the command locally: SELECT reference_table.a, reference_table.b FROM (citus_local_table_queries.reference_table_1509003 reference_table JOIN citus_local_table_queries.citus_local_table_1509001 citus_local_table ON (true)) 638-- INSERT SELECT that joins reference and distributed tables is also OK 639SELECT clear_and_init_test_tables(); 640 clear_and_init_test_tables 641--------------------------------------------------------------------- 642 643(1 row) 644 645INSERT INTO citus_local_table 646SELECT reference_table.* FROM reference_table 647JOIN distributed_table ON (true); 648NOTICE: executing the copy locally for shard xxxxx 649INSERT INTO citus_local_table 650SELECT reference_table.* 651FROM reference_table, distributed_table; 652NOTICE: executing the copy locally for shard xxxxx 653-- INSERT SELECT that joins citus local and distributed table directly will fail .. 654INSERT INTO citus_local_table 655SELECT distributed_table.* FROM distributed_table 656JOIN citus_local_table ON (true); 657NOTICE: executing the command locally: SELECT NULL::integer AS "dummy-1" FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 658NOTICE: executing the copy locally for shard xxxxx 659-- .. but when wrapped into a CTE, join works fine 660INSERT INTO citus_local_table 661SELECT distributed_table.* FROM distributed_table 662JOIN (WITH cte AS (SELECT * FROM citus_local_table) SELECT * FROM cte) as foo ON (true); 663NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 664NOTICE: executing the copy locally for shard xxxxx 665-- multi row insert is OK 666INSERT INTO citus_local_table VALUES (1, 2), (3, 4); 667NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a, b) VALUES (1,2), (3,4) 668--------------------------------------------------------------------- 669----- DELETE / UPDATE ----- 670--------------------------------------------------------------------- 671-- modifications using citus local tables and postgres local tables 672-- are not supported, see below four tests 673SELECT clear_and_init_test_tables(); 674 clear_and_init_test_tables 675--------------------------------------------------------------------- 676 677(1 row) 678 679DELETE FROM citus_local_table 680USING postgres_local_table 681WHERE citus_local_table.b = postgres_local_table.b; 682NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table USING citus_local_table_queries.postgres_local_table WHERE (citus_local_table.b OPERATOR(pg_catalog.=) postgres_local_table.b) 683UPDATE citus_local_table 684SET b = 5 685FROM postgres_local_table 686WHERE citus_local_table.a = 3 AND citus_local_table.b = postgres_local_table.b; 687NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 citus_local_table SET b = 5 FROM citus_local_table_queries.postgres_local_table WHERE ((citus_local_table.a OPERATOR(pg_catalog.=) 3) AND (citus_local_table.b OPERATOR(pg_catalog.=) postgres_local_table.b)) 688DELETE FROM postgres_local_table 689USING citus_local_table 690WHERE citus_local_table.b = postgres_local_table.b; 691NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.postgres_local_table USING citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (citus_local_table.b OPERATOR(pg_catalog.=) postgres_local_table.b) 692UPDATE postgres_local_table 693SET b = 5 694FROM citus_local_table 695WHERE citus_local_table.a = 3 AND citus_local_table.b = postgres_local_table.b; 696NOTICE: executing the command locally: UPDATE citus_local_table_queries.postgres_local_table SET b = 5 FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE ((citus_local_table.a OPERATOR(pg_catalog.=) 3) AND (citus_local_table.b OPERATOR(pg_catalog.=) postgres_local_table.b)) 697-- no direct joins supported 698UPDATE distributed_table 699SET b = 6 700FROM citus_local_table 701WHERE citus_local_table.a = distributed_table.a; 702NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 703UPDATE reference_table 704SET b = 6 705FROM citus_local_table 706WHERE citus_local_table.a = reference_table.a; 707NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 708NOTICE: executing the command locally: UPDATE citus_local_table_queries.reference_table_1509003 reference_table SET b = 6 FROM (SELECT citus_local_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) citus_local_table_1) citus_local_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) 709-- should not work, add HINT use CTEs 710UPDATE citus_local_table 711SET b = 6 712FROM distributed_table 713WHERE citus_local_table.a = distributed_table.a; 714NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 citus_local_table SET b = 6 FROM (SELECT distributed_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) distributed_table_1) distributed_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) distributed_table.a) 715-- should work, add HINT use CTEs 716UPDATE citus_local_table 717SET b = 6 718FROM reference_table 719WHERE citus_local_table.a = reference_table.a; 720NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.reference_table_1509003 reference_table WHERE true 721NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 citus_local_table SET b = 6 FROM (SELECT reference_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) reference_table_1) reference_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) 722-- should not work, add HINT use CTEs 723DELETE FROM distributed_table 724USING citus_local_table 725WHERE citus_local_table.a = distributed_table.a; 726NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 727-- should not work, add HINT use CTEs 728DELETE FROM citus_local_table 729USING distributed_table 730WHERE citus_local_table.a = distributed_table.a; 731NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table USING (SELECT distributed_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) distributed_table_1) distributed_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) distributed_table.a) 732DELETE FROM reference_table 733USING citus_local_table 734WHERE citus_local_table.a = reference_table.a; 735NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 736NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.reference_table_1509003 reference_table USING (SELECT citus_local_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) citus_local_table_1) citus_local_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) 737-- should work, add HINT use CTEs 738DELETE FROM citus_local_table 739USING reference_table 740WHERE citus_local_table.a = reference_table.a; 741NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.reference_table_1509003 reference_table WHERE true 742NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table USING (SELECT reference_table_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) reference_table_1) reference_table WHERE (citus_local_table.a OPERATOR(pg_catalog.=) reference_table.a) 743-- just works 744DELETE FROM citus_local_table 745WHERE citus_local_table.a IN (SELECT a FROM distributed_table); 746NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer))) 747-- just works 748DELETE FROM citus_local_table 749WHERE citus_local_table.a IN (SELECT a FROM reference_table); 750NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.reference_table_1509003 reference_table 751NOTICE: executing the command locally: DELETE FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE (a OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer))) 752-- just works 753WITH distributed_table_cte AS (SELECT * FROM distributed_table) 754UPDATE citus_local_table 755SET b = 6 756FROM distributed_table_cte 757WHERE citus_local_table.a = distributed_table_cte.a; 758NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 citus_local_table SET b = 6 FROM (SELECT intermediate_result.a, intermediate_result.b FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer)) distributed_table_cte WHERE (citus_local_table.a OPERATOR(pg_catalog.=) distributed_table_cte.a) 759SET citus.log_local_commands to off; 760-- just works 761WITH reference_table_cte AS (SELECT * FROM reference_table) 762UPDATE citus_local_table 763SET b = 6 764FROM reference_table_cte 765WHERE citus_local_table.a = reference_table_cte.a; 766set citus.log_local_commands to on; 767--------------------------------------------------------------------- 768----- VIEW QUERIES ----- 769--------------------------------------------------------------------- 770CREATE MATERIALIZED VIEW mat_view_4 AS 771SELECT count(*) 772FROM citus_local_table 773JOIN reference_table 774USING (a); 775NOTICE: executing the command locally: SELECT count(*) AS count FROM (citus_local_table_queries.citus_local_table_1509001 citus_local_table(a, b) JOIN citus_local_table_queries.reference_table_1509003 reference_table(a, b) USING (a)) 776-- ok 777SELECT count(*) FROM mat_view_4; 778 count 779--------------------------------------------------------------------- 780 1 781(1 row) 782 783-- should work 784SELECT count(*) FROM distributed_table WHERE b in 785(SELECT count FROM mat_view_4); 786 count 787--------------------------------------------------------------------- 788 1 789(1 row) 790 791CREATE VIEW view_2 AS 792SELECT count(*) 793FROM citus_local_table 794JOIN citus_local_table_2 USING (a) 795JOIN distributed_table USING (a); 796-- should fail as view contains direct local dist join 797SELECT count(*) FROM view_2; 798NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table WHERE true 799NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2 WHERE true 800NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT intermediate_result.count FROM read_intermediate_result('XXX_3'::text, 'binary'::citus_copy_format) intermediate_result(count bigint)) view_2 801 count 802--------------------------------------------------------------------- 803 1 804(1 row) 805 806CREATE VIEW view_3 807AS SELECT count(*) 808FROM citus_local_table_2 809JOIN reference_table 810USING (a); 811-- ok 812SELECT count(*) FROM view_3; 813NOTICE: executing the command locally: SELECT count(*) AS count FROM (SELECT count(*) AS count FROM (citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2(a, b) JOIN citus_local_table_queries.reference_table_1509003 reference_table(a, b) USING (a))) view_3 814 count 815--------------------------------------------------------------------- 816 1 817(1 row) 818 819-- view treated as subquery, so should work 820SELECT count(*) FROM view_3, distributed_table; 821NOTICE: executing the command locally: SELECT a FROM citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2 WHERE true 822NOTICE: executing the command locally: SELECT count(*) AS count FROM ((SELECT citus_local_table_2_1.a, NULL::integer AS b FROM (SELECT intermediate_result.a FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer)) citus_local_table_2_1) citus_local_table_2 JOIN citus_local_table_queries.reference_table_1509003 reference_table(a, b) USING (a)) 823 count 824--------------------------------------------------------------------- 825 6 826(1 row) 827 828--------------------------------------------------------------------- 829-- Some other tests with subqueries & CTE's -- 830--------------------------------------------------------------------- 831SELECT clear_and_init_test_tables(); 832 clear_and_init_test_tables 833--------------------------------------------------------------------- 834 835(1 row) 836 837SELECT count(*) AS a, count(*) AS b 838FROM reference_table 839JOIN (SELECT count(*) as a, count(*) as b 840 FROM citus_local_table_2 841 JOIN (SELECT count(*) as a, count(*) as b 842 FROM postgres_local_table 843 JOIN (SELECT count(*) as a, count(*) as b 844 FROM reference_table as table_4677) subquery5108 845 USING (a)) subquery7132 846 USING (b)) subquery7294 847USING (a); 848NOTICE: executing the command locally: SELECT count(*) AS a, count(*) AS b FROM (citus_local_table_queries.reference_table_1509003 reference_table(a, b) JOIN (SELECT count(*) AS a, count(*) AS b FROM (citus_local_table_queries.citus_local_table_2_1509002 citus_local_table_2(a, b) JOIN (SELECT count(*) AS a, count(*) AS b FROM (citus_local_table_queries.postgres_local_table JOIN (SELECT count(*) AS a, count(*) AS b FROM citus_local_table_queries.reference_table_1509003 table_4677) subquery5108 USING (a))) subquery7132 USING (b))) subquery7294 USING (a)) 849 a | b 850--------------------------------------------------------------------- 851 1 | 1 852(1 row) 853 854-- direct join inside CTE not supported 855WITH cte AS ( 856UPDATE citus_local_table lt SET a = mt.a 857FROM distributed_table mt WHERE mt.b = lt.b 858RETURNING lt.b, lt.a 859) SELECT * FROM cte JOIN distributed_table mt ON mt.b = cte.b ORDER BY 1,2,3,4; 860NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 lt SET a = mt.a FROM (SELECT mt_1.a, mt_1.b FROM (SELECT intermediate_result.a, intermediate_result.b FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(a integer, b integer)) mt_1) mt WHERE (mt.b OPERATOR(pg_catalog.=) lt.b) RETURNING lt.b, lt.a 861 b | a | a | b 862--------------------------------------------------------------------- 863 0 | 0 | 0 | 0 864 1 | 1 | 1 | 1 865 2 | 2 | 2 | 2 866 3 | 3 | 3 | 3 867 4 | 4 | 4 | 4 868 5 | 5 | 5 | 5 869(6 rows) 870 871-- join with CTE just works 872UPDATE citus_local_table 873SET a=5 874FROM (SELECT avg(distributed_table.b) as avg_b 875 FROM distributed_table) as foo 876WHERE 877foo.avg_b = citus_local_table.b; 878NOTICE: executing the command locally: UPDATE citus_local_table_queries.citus_local_table_1509001 citus_local_table SET a = 5 FROM (SELECT intermediate_result.avg_b FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(avg_b numeric)) foo WHERE (foo.avg_b OPERATOR(pg_catalog.=) (citus_local_table.b)::numeric) 879-- should work 880UPDATE distributed_table 881SET b = avg_a 882FROM (SELECT avg(citus_local_table.a) as avg_a FROM citus_local_table) as foo 883WHERE foo.avg_a = distributed_table.a 884RETURNING distributed_table.*; 885NOTICE: executing the command locally: SELECT avg(a) AS avg_a FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 886 a | b 887--------------------------------------------------------------------- 888(0 rows) 889 890-- it is unfortunate that recursive planner cannot detect this 891-- but expected to not work 892UPDATE citus_local_table 893SET a=5 894FROM (SELECT b FROM distributed_table) AS foo 895WHERE foo.b = citus_local_table.b; 896ERROR: local table citus_local_table cannot be joined with these distributed tables 897--------------------------------------------------------------------- 898-- test different execution paths -- 899--------------------------------------------------------------------- 900-- a bit different explain output than for postgres local tables 901EXPLAIN (COSTS FALSE) 902INSERT INTO citus_local_table 903SELECT * FROM distributed_table 904ORDER BY distributed_table.* 905LIMIT 10; 906 QUERY PLAN 907--------------------------------------------------------------------- 908 Custom Scan (Citus INSERT ... SELECT) 909 INSERT/SELECT method: pull to coordinator 910 -> Limit 911 -> Sort 912 Sort Key: remote_scan.worker_column_3 913 -> Custom Scan (Citus Adaptive) 914 Task Count: 4 915 Tasks Shown: One of 4 916 -> Task 917 Node: host=localhost port=xxxxx dbname=regression 918 -> Limit 919 -> Sort 920 Sort Key: distributed_table.* 921 -> Seq Scan on distributed_table_1509004 distributed_table 922(14 rows) 923 924-- show that we do not pull to coordinator 925EXPLAIN (COSTS FALSE) 926INSERT INTO citus_local_table 927SELECT * FROM citus_local_table; 928 QUERY PLAN 929--------------------------------------------------------------------- 930 Custom Scan (Citus Adaptive) 931 Task Count: 1 932 Tasks Shown: All 933 -> Task 934 Node: host=localhost port=xxxxx dbname=regression 935 -> Insert on citus_local_table_1509001 citus_table_alias 936 -> Seq Scan on citus_local_table_1509001 citus_local_table 937(7 rows) 938 939EXPLAIN (COSTS FALSE) 940INSERT INTO citus_local_table 941SELECT reference_table.* FROM reference_table; 942 QUERY PLAN 943--------------------------------------------------------------------- 944 Custom Scan (Citus Adaptive) 945 Task Count: 1 946 Tasks Shown: All 947 -> Task 948 Node: host=localhost port=xxxxx dbname=regression 949 -> Insert on citus_local_table_1509001 citus_table_alias 950 -> Seq Scan on reference_table_1509003 reference_table 951(7 rows) 952 953EXPLAIN (COSTS FALSE) 954INSERT INTO citus_local_table 955SELECT reference_table.* FROM reference_table, postgres_local_table; 956 QUERY PLAN 957--------------------------------------------------------------------- 958 Custom Scan (Citus INSERT ... SELECT) 959 INSERT/SELECT method: pull to coordinator 960 -> Custom Scan (Citus Adaptive) 961 Task Count: 1 962 Tasks Shown: All 963 -> Task 964 Node: host=localhost port=xxxxx dbname=regression 965 -> Nested Loop 966 -> Seq Scan on reference_table_1509003 reference_table 967 -> Materialize 968 -> Seq Scan on postgres_local_table 969(11 rows) 970 971-- show that we pull to coordinator when a distributed table is involved 972EXPLAIN (COSTS FALSE) 973INSERT INTO citus_local_table 974SELECT reference_table.* FROM reference_table, distributed_table; 975 QUERY PLAN 976--------------------------------------------------------------------- 977 Custom Scan (Citus INSERT ... SELECT) 978 INSERT/SELECT method: pull to coordinator 979 -> Custom Scan (Citus Adaptive) 980 Task Count: 4 981 Tasks Shown: One of 4 982 -> Task 983 Node: host=localhost port=xxxxx dbname=regression 984 -> Nested Loop 985 -> Seq Scan on distributed_table_1509004 distributed_table 986 -> Materialize 987 -> Seq Scan on reference_table_1509003 reference_table 988(11 rows) 989 990-- truncate tables & add unique constraints to be able to define foreign keys 991TRUNCATE reference_table, citus_local_table, distributed_table; 992NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.reference_table_xxxxx CASCADE 993NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.citus_local_table_xxxxx CASCADE 994ALTER TABLE reference_table ADD CONSTRAINT pkey_ref PRIMARY KEY (a); 995NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1509003, 'citus_local_table_queries', 'ALTER TABLE reference_table ADD CONSTRAINT pkey_ref PRIMARY KEY (a);') 996ALTER TABLE citus_local_table ADD CONSTRAINT pkey_c PRIMARY KEY (a); 997NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1509001, 'citus_local_table_queries', 'ALTER TABLE citus_local_table ADD CONSTRAINT pkey_c PRIMARY KEY (a);') 998-- define a foreign key chain distributed table -> reference table -> citus local table 999-- to test sequential execution 1000ALTER TABLE distributed_table ADD CONSTRAINT fkey_dist_to_ref FOREIGN KEY(a) REFERENCES reference_table(a) ON DELETE RESTRICT; 1001ALTER TABLE reference_table ADD CONSTRAINT fkey_ref_to_local FOREIGN KEY(a) REFERENCES citus_local_table(a) ON DELETE RESTRICT; 1002NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1509003, 'citus_local_table_queries', 1509001, 'citus_local_table_queries', 'ALTER TABLE reference_table ADD CONSTRAINT fkey_ref_to_local FOREIGN KEY(a) REFERENCES citus_local_table(a) ON DELETE RESTRICT;') 1003INSERT INTO citus_local_table VALUES (1); 1004NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 (a) VALUES (1) 1005INSERT INTO reference_table VALUES (1); 1006NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.reference_table_1509003 (a) VALUES (1) 1007BEGIN; 1008 INSERT INTO citus_local_table VALUES (1) ON CONFLICT (a) DO NOTHING; 1009NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (1) ON CONFLICT(a) DO NOTHING 1010 INSERT INTO distributed_table VALUES (1); 1011 -- should show sequential as first inserting into citus local table 1012 -- would force the xact block to use sequential execution 1013 show citus.multi_shard_modify_mode; 1014 citus.multi_shard_modify_mode 1015--------------------------------------------------------------------- 1016 sequential 1017(1 row) 1018 1019ROLLBACK; 1020BEGIN; 1021 TRUNCATE distributed_table; 1022 -- should error out as we truncated distributed_table via parallel execution 1023 TRUNCATE citus_local_table CASCADE; 1024NOTICE: truncate cascades to table "reference_table" 1025NOTICE: truncate cascades to table "distributed_table" 1026NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.citus_local_table_xxxxx CASCADE 1027NOTICE: truncate cascades to table "reference_table_xxxxx" 1028ERROR: cannot execute DDL on table "reference_table" because there was a parallel DDL access to distributed table "distributed_table" in the same transaction 1029ROLLBACK; 1030BEGIN; 1031 SET LOCAL citus.multi_shard_modify_mode TO 'sequential'; 1032 TRUNCATE distributed_table; 1033 -- should work fine as we already switched to sequential execution 1034 -- before parallel truncate 1035 TRUNCATE citus_local_table CASCADE; 1036NOTICE: truncate cascades to table "reference_table" 1037NOTICE: truncate cascades to table "distributed_table" 1038NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.citus_local_table_xxxxx CASCADE 1039NOTICE: truncate cascades to table "reference_table_xxxxx" 1040NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.reference_table_xxxxx CASCADE 1041ROLLBACK; 1042ALTER TABLE distributed_table DROP CONSTRAINT fkey_dist_to_ref; 1043BEGIN; 1044 INSERT INTO citus_local_table VALUES (1) ON CONFLICT (a) DO NOTHING; 1045NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (1) ON CONFLICT(a) DO NOTHING 1046 show citus.multi_shard_modify_mode; 1047 citus.multi_shard_modify_mode 1048--------------------------------------------------------------------- 1049 sequential 1050(1 row) 1051 1052ROLLBACK; 1053-- remove uniqueness constraint and dependent foreign key constraint for next tests 1054ALTER TABLE reference_table DROP CONSTRAINT fkey_ref_to_local; 1055NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1509003, 'citus_local_table_queries', 1509001, 'citus_local_table_queries', 'ALTER TABLE reference_table DROP CONSTRAINT fkey_ref_to_local;') 1056ALTER TABLE citus_local_table DROP CONSTRAINT pkey_c; 1057NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1509001, 'citus_local_table_queries', 'ALTER TABLE citus_local_table DROP CONSTRAINT pkey_c;') 1058COPY citus_local_table(a) FROM PROGRAM 'seq 1'; 1059-- should use local execution 1060BEGIN; 1061 COPY citus_local_table(a) FROM PROGRAM 'seq 1'; 1062NOTICE: executing the copy locally for shard xxxxx 1063 COPY citus_local_table(a) FROM PROGRAM 'seq 1'; 1064NOTICE: executing the copy locally for shard xxxxx 1065COMMIT; 1066COPY citus_local_table TO STDOUT; 10671 \N 10681 \N 10691 \N 10701 \N 1071COPY (SELECT * FROM citus_local_table) TO STDOUT; 1072NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 10731 \N 10741 \N 10751 \N 10761 \N 1077BEGIN; 1078 COPY citus_local_table TO STDOUT; 10791 \N 10801 \N 10811 \N 10821 \N 1083COMMIT; 1084BEGIN; 1085 COPY (SELECT * FROM citus_local_table) TO STDOUT; 1086NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table 10871 \N 10881 \N 10891 \N 10901 \N 1091COMMIT; 1092-- truncate test tables for next test 1093TRUNCATE citus_local_table, reference_table, distributed_table; 1094NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.citus_local_table_xxxxx CASCADE 1095NOTICE: executing the command locally: TRUNCATE TABLE citus_local_table_queries.reference_table_xxxxx CASCADE 1096BEGIN; 1097 INSERT INTO citus_local_table VALUES (1), (2); 1098NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (1), (2) 1099 SAVEPOINT sp1; 1100 INSERT INTO citus_local_table VALUES (3), (4); 1101NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (3), (4) 1102 ROLLBACK TO SAVEPOINT sp1; 1103 SELECT * FROM citus_local_table ORDER BY 1,2; 1104NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table ORDER BY a, b 1105 a | b 1106--------------------------------------------------------------------- 1107 1 | 1108 2 | 1109(2 rows) 1110 1111 SAVEPOINT sp2; 1112 INSERT INTO citus_local_table VALUES (3), (4); 1113NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (3), (4) 1114 INSERT INTO distributed_table VALUES (3), (4); 1115 ROLLBACK TO SAVEPOINT sp2; 1116 SELECT * FROM citus_local_table ORDER BY 1,2; 1117NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table ORDER BY a, b 1118 a | b 1119--------------------------------------------------------------------- 1120 1 | 1121 2 | 1122(2 rows) 1123 1124 SELECT * FROM distributed_table ORDER BY 1,2; 1125 a | b 1126--------------------------------------------------------------------- 1127(0 rows) 1128 1129 SAVEPOINT sp3; 1130 INSERT INTO citus_local_table VALUES (3), (2); 1131NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.citus_local_table_1509001 AS citus_table_alias (a) VALUES (3), (2) 1132 INSERT INTO reference_table VALUES (3), (2); 1133NOTICE: executing the command locally: INSERT INTO citus_local_table_queries.reference_table_1509003 AS citus_table_alias (a) VALUES (3), (2) 1134 ROLLBACK TO SAVEPOINT sp3; 1135 SELECT * FROM citus_local_table ORDER BY 1,2; 1136NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.citus_local_table_1509001 citus_local_table ORDER BY a, b 1137 a | b 1138--------------------------------------------------------------------- 1139 1 | 1140 2 | 1141(2 rows) 1142 1143 SELECT * FROM reference_table ORDER BY 1,2; 1144NOTICE: executing the command locally: SELECT a, b FROM citus_local_table_queries.reference_table_1509003 reference_table ORDER BY a, b 1145 a | b 1146--------------------------------------------------------------------- 1147(0 rows) 1148 1149COMMIT; 1150-- cleanup at exit 1151DROP SCHEMA citus_local_table_queries CASCADE; 1152NOTICE: drop cascades to 14 other objects 1153