-- This tests file includes tests for local execution of utility commands. -- For now, this file includes tests only for local execution of -- `TRUNCATE/DROP/DDL` commands for all kinds of distributed tables from -- the coordinator node having regular distributed tables' shards -- (shouldHaveShards = on) and having reference table placements in it. \set VERBOSITY terse SET citus.next_shard_id TO 1500000; SET citus.shard_replication_factor TO 1; SET citus.enable_local_execution TO ON; SET citus.shard_COUNT TO 32; SET citus.log_local_commands TO ON; CREATE SCHEMA local_commands_test_schema; SET search_path TO local_commands_test_schema; -- let coordinator have distributed table shards/placements set client_min_messages to ERROR; SELECT 1 FROM master_add_node('localhost', :master_port, groupId => 0); ?column? --------------------------------------------------------------------- 1 (1 row) RESET client_min_messages; SELECT master_set_node_property('localhost', :master_port, 'shouldhaveshards', true); master_set_node_property --------------------------------------------------------------------- (1 row) --------------------------------------------------------------------- ------ local execution of TRUNCATE ------ --------------------------------------------------------------------- CREATE TABLE ref_table (a int primary key); SELECT create_reference_table('ref_table'); create_reference_table --------------------------------------------------------------------- (1 row) CREATE TABLE dist_table(a int); SELECT create_distributed_table('dist_table', 'a', colocate_with:='none'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE dist_table ADD CONSTRAINT fkey FOREIGN KEY(a) REFERENCES ref_table(a); -- insert some data INSERT INTO ref_table VALUES(1); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (1) INSERT INTO dist_table VALUES(1); -- Currently, we support local execution of TRUNCATE commands for all kinds -- Hence, cascading to distributed tables wouldn't be a problem even in the -- case that coordinator have some local distributed table shards. TRUNCATE ref_table CASCADE; NOTICE: truncate cascades to table "dist_table" NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.ref_table_xxxxx CASCADE NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE -- show that TRUNCATE is successfull SELECT COUNT(*) FROM ref_table, dist_table; count --------------------------------------------------------------------- 0 (1 row) -- insert some data INSERT INTO ref_table VALUES(1); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (1) INSERT INTO dist_table VALUES(1); -- As SELECT accesses local placements of reference table, TRUNCATE would also -- be forced to local execution even if they operate on different tables. BEGIN; SELECT COUNT(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500000 ref_table count --------------------------------------------------------------------- 1 (1 row) TRUNCATE dist_table; NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE COMMIT; -- show that TRUNCATE is successfull SELECT COUNT(*) FROM dist_table; count --------------------------------------------------------------------- 0 (1 row) -- insert some data INSERT INTO ref_table VALUES(2); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (2) INSERT INTO dist_table VALUES(2); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.dist_table_1500025 (a) VALUES (2) -- SELECT would access local placements via local execution as that is -- in a transaction block even though it contains multi local shards. BEGIN; SELECT COUNT(*) FROM dist_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500001 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500004 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500007 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500010 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500013 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500016 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500019 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500022 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500025 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500028 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500031 dist_table WHERE true count --------------------------------------------------------------------- 1 (1 row) TRUNCATE dist_table; NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE COMMIT; -- show that TRUNCATE is successfull SELECT COUNT(*) FROM dist_table; count --------------------------------------------------------------------- 0 (1 row) -- insert some data INSERT INTO ref_table VALUES(3); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (3) INSERT INTO dist_table VALUES(3); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.dist_table_1500016 (a) VALUES (3) -- TRUNCATE on dist_table (note that: again no cascade here) would -- just be handled via remote executions even on its local shards TRUNCATE dist_table; -- show that TRUNCATE is successfull SELECT COUNT(*) FROM dist_table; count --------------------------------------------------------------------- 0 (1 row) -- insert some data INSERT INTO ref_table VALUES(4); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (4) -- Creating a dist. table is handled by local execution inside a transaction block. -- Hence, the commands following it (INSERT & TRUNCATE) would also be -- handled via local execution. BEGIN; CREATE TABLE ref_table_1(a int); SELECT create_reference_table('ref_table_1'); NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500033, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.ref_table_1 (a integer) ');SELECT worker_apply_shard_ddl_command (1500033, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.ref_table_1 OWNER TO postgres') create_reference_table --------------------------------------------------------------------- (1 row) -- insert some data INSERT INTO ref_table_1 VALUES(5); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1_1500033 (a) VALUES (5) TRUNCATE ref_table_1; NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.ref_table_1_xxxxx CASCADE COMMIT; -- show that TRUNCATE is successfull SELECT COUNT(*) FROM ref_table_1; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1_1500033 ref_table_1 count --------------------------------------------------------------------- 0 (1 row) -- However, as SELECT would access local placements via local execution -- for regular distributed tables, below TRUNCATE would error -- out BEGIN; SELECT COUNT(*) FROM dist_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500001 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500004 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500007 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500010 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500013 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500016 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500019 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500022 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500025 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500028 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500031 dist_table WHERE true count --------------------------------------------------------------------- 0 (1 row) TRUNCATE ref_table CASCADE; NOTICE: truncate cascades to table "dist_table" ERROR: cannot execute DDL on table "ref_table" because there was a parallel SELECT access to distributed table "dist_table" in the same transaction COMMIT; -- as we do not support local ANALYZE execution yet, below block would error out BEGIN; TRUNCATE ref_table CASCADE; NOTICE: truncate cascades to table "dist_table" NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.ref_table_xxxxx CASCADE NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE ANALYZE ref_table; ERROR: cannot execute command because a local execution has accessed a placement in the transaction COMMIT; -- insert some data INSERT INTO ref_table VALUES(7); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500000 (a) VALUES (7) INSERT INTO dist_table VALUES(7); -- we can TRUNCATE those two tables within the same command TRUNCATE ref_table, dist_table; NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.ref_table_xxxxx CASCADE NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: truncate cascades to table "dist_table_xxxxx" NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.dist_table_xxxxx CASCADE -- show that TRUNCATE is successfull SELECT COUNT(*) FROM ref_table, dist_table; count --------------------------------------------------------------------- 0 (1 row) --------------------------------------------------------------------- ------ local execution of DROP ------ --------------------------------------------------------------------- -- droping just the referenced table would error out as dist_table references it DROP TABLE ref_table; ERROR: cannot drop table ref_table because other objects depend on it -- drop those two tables via remote execution DROP TABLE ref_table, dist_table; -- drop the other standalone table locally DROP TABLE ref_table_1; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.ref_table_1_xxxxx CASCADE -- show that DROP commands are successfull SELECT tablename FROM pg_tables where schemaname='local_commands_test_schema' ORDER BY tablename; tablename --------------------------------------------------------------------- (0 rows) CREATE TABLE ref_table (a int primary key); SELECT create_reference_table('ref_table'); create_reference_table --------------------------------------------------------------------- (1 row) -- We execute SELECT command within the below block locally. -- Hence we should execute the DROP command locally as well. BEGIN; SELECT COUNT(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500034 ref_table count --------------------------------------------------------------------- 0 (1 row) DROP TABLE ref_table; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.ref_table_xxxxx CASCADE COMMIT; CREATE TABLE ref_table (a int primary key); SELECT create_reference_table('ref_table'); create_reference_table --------------------------------------------------------------------- (1 row) CREATE TABLE dist_table(a int); SELECT create_distributed_table('dist_table', 'a', colocate_with:='none'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE dist_table ADD CONSTRAINT fkey FOREIGN KEY(a) REFERENCES ref_table(a); -- show that DROP command is rollback'd successfully (should print 1) SELECT 1 FROM pg_tables where tablename='dist_table'; ?column? --------------------------------------------------------------------- 1 (1 row) -- As SELECT will be executed remotely, the DROP command should also be executed -- remotely to prevent possible self-deadlocks & inconsistencies. -- FIXME: we have a known bug for below case described in -- https://github.com/citusdata/citus/issues/3526. Hence, commented out as it could -- randomly fall into distributed deadlocks --BEGIN; -- SELECT COUNT(*) FROM dist_table; -- DROP TABLE dist_table; --END; -- As SELECT will be executed remotely, the DROP command below should also be -- executed remotely. CREATE TABLE another_dist_table(a int); SELECT create_distributed_table('another_dist_table', 'a', colocate_with:='dist_table'); create_distributed_table --------------------------------------------------------------------- (1 row) BEGIN; SELECT COUNT(*) FROM another_dist_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500068 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500071 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500074 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500077 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500080 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500083 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500086 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500089 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500092 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500095 another_dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.another_dist_table_1500098 another_dist_table WHERE true count --------------------------------------------------------------------- 0 (1 row) DROP TABLE another_dist_table; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.another_dist_table_xxxxx CASCADE COMMIT; -- show that DROP command is committed successfully SELECT 1 FROM pg_tables where tablename='another_dist_table'; ?column? --------------------------------------------------------------------- (0 rows) -- below DROP will be executed remotely. DROP TABLE dist_table; -- show that DROP command is successfull SELECT 1 FROM pg_tables where tablename='dist_table'; ?column? --------------------------------------------------------------------- (0 rows) CREATE TABLE dist_table(a int); SELECT create_distributed_table('dist_table', 'a', colocate_with:='none'); create_distributed_table --------------------------------------------------------------------- (1 row) ALTER TABLE dist_table ADD CONSTRAINT fkey FOREIGN KEY(a) REFERENCES ref_table(a); -- as SELECT on ref_table will be executed locally, the SELECT and DROP following -- it would also be executed locally BEGIN; SELECT COUNT(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500035 ref_table count --------------------------------------------------------------------- 0 (1 row) DROP TABLE dist_table CASCADE; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE ROLLBACK; -- show that DROP command is rollback'd successfully (should print 1) SELECT 1 FROM pg_tables where tablename='dist_table'; ?column? --------------------------------------------------------------------- 1 (1 row) --------------------------------------------------------------------- ------ local execution of DDL commands ------ --------------------------------------------------------------------- -- try some complicated CASCADE cases along with DDL commands CREATE TABLE ref_table_1(a int primary key); SELECT create_reference_table('ref_table_1'); create_reference_table --------------------------------------------------------------------- (1 row) -- below block should execute successfully BEGIN; SELECT COUNT(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500035 ref_table count --------------------------------------------------------------------- 0 (1 row) -- as SELECT above runs locally and as now we support local execution of DDL commands, -- below DDL should be able to define foreign key constraint successfully ALTER TABLE ref_table ADD CONSTRAINT fkey FOREIGN KEY(a) REFERENCES ref_table_1(a); NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500035, 'local_commands_test_schema', 1500132, 'local_commands_test_schema', 'ALTER TABLE ref_table ADD CONSTRAINT fkey FOREIGN KEY(a) REFERENCES ref_table_1(a);') -- insert some data INSERT INTO ref_table_1 VALUES (1); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1_1500132 (a) VALUES (1) INSERT INTO ref_table_1 VALUES (2); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1_1500132 (a) VALUES (2) INSERT INTO ref_table VALUES (1); NOTICE: executing the command locally: INSERT INTO local_commands_test_schema.ref_table_1500035 (a) VALUES (1) -- chain foreign key constraints -- local execution should be observed here as well ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a); NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500100, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500103, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500106, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500109, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500112, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500115, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500118, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500121, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500124, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500127, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500130, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD CONSTRAINT fkey1 FOREIGN KEY(a) REFERENCES ref_table(a);') INSERT INTO dist_table VALUES (1); DELETE FROM ref_table_1 WHERE a=2; NOTICE: executing the command locally: DELETE FROM local_commands_test_schema.ref_table_1_1500132 ref_table_1 WHERE (a OPERATOR(pg_catalog.=) 2) -- add another column to dist_table -- note that we execute below DDL locally as well ALTER TABLE ref_table ADD b int; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500035, 'local_commands_test_schema', 'ALTER TABLE ref_table ADD b int;') -- define self reference ALTER TABLE ref_table ADD CONSTRAINT fkey2 FOREIGN KEY(b) REFERENCES ref_table(a); NOTICE: executing the command locally: SELECT worker_apply_inter_shard_ddl_command (1500035, 'local_commands_test_schema', 1500035, 'local_commands_test_schema', 'ALTER TABLE ref_table ADD CONSTRAINT fkey2 FOREIGN KEY(b) REFERENCES ref_table(a);') SELECT COUNT(*) FROM ref_table_1, ref_table, dist_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500100 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500103 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500106 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500109 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500112 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500115 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500118 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500121 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500124 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500127 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM ((local_commands_test_schema.dist_table_1500130 dist_table JOIN local_commands_test_schema.ref_table_1_1500132 ref_table_1 ON (true)) JOIN local_commands_test_schema.ref_table_1500035 ref_table ON (true)) WHERE true count --------------------------------------------------------------------- 1 (1 row) -- observe DROP on a self-referencing table also works DROP TABLE ref_table_1, ref_table, dist_table; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.dist_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.ref_table_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.ref_table_1_xxxxx CASCADE -- show that DROP command is successfull SELECT tablename FROM pg_tables where schemaname='local_commands_test_schema' ORDER BY tablename; tablename --------------------------------------------------------------------- (0 rows) ROLLBACK; -- add another column to dist_table (should be executed remotely) ALTER TABLE dist_table ADD b int; CREATE SCHEMA foo_schema; -- As SELECT will be executed remotely, ALTER TABLE SET SCHEMA command should alse be executed remotely BEGIN; SELECT COUNT(*) FROM dist_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500100 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500103 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500106 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500109 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500112 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500115 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500118 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500121 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500124 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500127 dist_table WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.dist_table_1500130 dist_table WHERE true count --------------------------------------------------------------------- 0 (1 row) ALTER TABLE dist_table SET SCHEMA foo_schema; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500100, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500103, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500106, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500109, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500112, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500115, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500118, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500121, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500124, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500127, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500130, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.dist_table SET SCHEMA foo_schema;') -- show that ALTER TABLE SET SCHEMA is successfull SELECT tablename FROM pg_tables where schemaname='foo_schema' ORDER BY tablename; tablename --------------------------------------------------------------------- dist_table dist_table_1500100 dist_table_1500103 dist_table_1500106 dist_table_1500109 dist_table_1500112 dist_table_1500115 dist_table_1500118 dist_table_1500121 dist_table_1500124 dist_table_1500127 dist_table_1500130 (12 rows) ROLLBACK; -- However, below ALTER TABLE SET SCHEMA command will be executed locally BEGIN; ALTER TABLE ref_table SET SCHEMA foo_schema; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500035, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.ref_table SET SCHEMA foo_schema;') -- show that ALTER TABLE SET SCHEMA is successfull SELECT tablename FROM pg_tables where schemaname='foo_schema' ORDER BY tablename; tablename --------------------------------------------------------------------- ref_table ref_table_1500035 (2 rows) ROLLBACK; BEGIN; -- here this SELECT will enforce the whole block for local execution SELECT COUNT(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500035 ref_table count --------------------------------------------------------------------- 0 (1 row) -- execute bunch of DDL & DROP commands succesfully ALTER TABLE dist_table ADD column c int; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500100, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500103, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500106, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500109, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500112, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500115, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500118, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500121, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500124, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500127, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500130, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD column c int;') ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500100, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500103, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500106, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500109, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500112, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500115, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500118, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500121, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500124, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500127, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500130, 'local_commands_test_schema', 'ALTER TABLE dist_table ALTER COLUMN c SET NOT NULL;') CREATE TABLE another_dist_table(a int); SELECT create_distributed_table('another_dist_table', 'a', colocate_with:='dist_table'); NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500133, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500133, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500136, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500136, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500139, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500139, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500142, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500142, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500145, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500145, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500148, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500148, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500151, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500151, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500154, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500154, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500157, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500157, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500160, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500160, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500163, 'local_commands_test_schema', 'CREATE TABLE local_commands_test_schema.another_dist_table (a integer) ');SELECT worker_apply_shard_ddl_command (1500163, 'local_commands_test_schema', 'ALTER TABLE local_commands_test_schema.another_dist_table OWNER TO postgres') create_distributed_table --------------------------------------------------------------------- (1 row) COMMIT; -- add a foreign key for next test ALTER TABLE dist_table ADD CONSTRAINT fkey_dist_to_ref FOREIGN KEY (b) REFERENCES ref_table(a); BEGIN; SELECT count(*) FROM ref_table; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.ref_table_1500035 ref_table count --------------------------------------------------------------------- 0 (1 row) -- should show parallel SHOW citus.multi_shard_modify_mode ; citus.multi_shard_modify_mode --------------------------------------------------------------------- parallel (1 row) -- wants to do parallel execution but will switch to sequential mode ALTER TABLE dist_table DROP COLUMN c; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500100, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500103, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500106, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500109, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500112, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500115, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500118, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500121, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500124, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500127, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500130, 'local_commands_test_schema', 'ALTER TABLE dist_table DROP COLUMN c;') -- should show sequential SHOW citus.multi_shard_modify_mode; citus.multi_shard_modify_mode --------------------------------------------------------------------- sequential (1 row) ROLLBACK; --------------------------------------------------------------------- ------------ partitioned tables ------------- --------------------------------------------------------------------- -- test combination of TRUNCATE & DROP & DDL commands with partitioned tables as well CREATE TABLE partitioning_test(id int, time date) PARTITION BY RANGE (time); CREATE TABLE partitioning_test_2012 PARTITION OF partitioning_test FOR VALUES FROM ('2012-06-06') TO ('2012-08-08'); CREATE TABLE partitioning_test_2013 PARTITION OF partitioning_test FOR VALUES FROM ('2013-06-06') TO ('2013-07-07'); -- load some data INSERT INTO partitioning_test VALUES (5, '2012-06-06'); INSERT INTO partitioning_test VALUES (6, '2012-07-07'); INSERT INTO partitioning_test VALUES (5, '2013-06-06'); SELECT create_distributed_table('partitioning_test', 'id', colocate_with:='dist_table'); NOTICE: Copying data from local table... NOTICE: copying the data has completed NOTICE: Copying data from local table... NOTICE: copying the data has completed create_distributed_table --------------------------------------------------------------------- (1 row) -- all commands below should be executed via local execution due to SELECT on ref_table BEGIN; SELECT * from ref_table; NOTICE: executing the command locally: SELECT a FROM local_commands_test_schema.ref_table_1500035 ref_table a --------------------------------------------------------------------- (0 rows) INSERT INTO partitioning_test VALUES (7, '2012-07-07'); SELECT COUNT(*) FROM partitioning_test; NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500165 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500168 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500171 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500174 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500177 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500180 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500183 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500186 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500189 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500192 partitioning_test WHERE true NOTICE: executing the command locally: SELECT count(*) AS count FROM local_commands_test_schema.partitioning_test_1500195 partitioning_test WHERE true count --------------------------------------------------------------------- 4 (1 row) -- execute bunch of DDL & DROP commands succesfully ALTER TABLE partitioning_test ADD column c int; NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500165, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500168, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500171, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500174, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500177, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500180, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500183, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500186, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500189, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500192, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') NOTICE: executing the command locally: SELECT worker_apply_shard_ddl_command (1500195, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD column c int;') TRUNCATE partitioning_test; NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: executing the command locally: TRUNCATE TABLE local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE DROP TABLE partitioning_test; NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_xxxxx CASCADE NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500197" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500200" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500203" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500206" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500209" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500212" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500215" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500218" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500221" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500224" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2012_xxxxx CASCADE NOTICE: table "partitioning_test_2012_1500227" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500229" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500232" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500235" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500238" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500241" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500244" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500247" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500250" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500253" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500256" does not exist, skipping NOTICE: executing the command locally: DROP TABLE IF EXISTS local_commands_test_schema.partitioning_test_2013_xxxxx CASCADE NOTICE: table "partitioning_test_2013_1500259" does not exist, skipping ROLLBACK; -- below should be executed via remote connections TRUNCATE partitioning_test; DROP TABLE partitioning_test; -- cleanup at exit DROP SCHEMA local_commands_test_schema CASCADE; NOTICE: drop cascades to 28 other objects DROP SCHEMA foo_schema; SELECT 1 FROM master_set_node_property('localhost', :master_port, 'shouldhaveshards', false); ?column? --------------------------------------------------------------------- 1 (1 row)