-- 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.next_placement_id TO 8300000;
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) USING heap');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 COLUMN b integer;')
  -- 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 integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500103, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500106, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500109, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500112, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500115, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500118, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500121, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500124, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500127, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500130, 'local_commands_test_schema', 'ALTER TABLE dist_table ADD COLUMN c integer;')
  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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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) USING heap');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 integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500168, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500171, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500174, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500177, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500180, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500183, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500186, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500189, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500192, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
NOTICE:  executing the command locally: SELECT worker_apply_shard_ddl_command (1500195, 'local_commands_test_schema', 'ALTER TABLE partitioning_test ADD COLUMN c integer;')
  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
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)

