CREATE SCHEMA undistribute_table;
SET search_path TO undistribute_table;
SET citus.shard_count TO 4;
SET citus.shard_replication_factor TO 1;
CREATE TABLE dist_table (id INT, a INT, b TEXT);
SELECT create_distributed_table('dist_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO dist_table VALUES (1, 2, 'abc'), (2, 3, 'abcd'), (1, 3, 'abc');
SELECT logicalrelid FROM pg_dist_partition WHERE logicalrelid = 'dist_table'::regclass;
 logicalrelid
---------------------------------------------------------------------
 dist_table
(1 row)

SELECT run_command_on_workers($$SELECT COUNT(*) FROM pg_catalog.pg_class WHERE relname LIKE 'dist\_table\_%'$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,2)
 (localhost,57638,t,2)
(2 rows)

SELECT * FROM dist_table ORDER BY 1, 2, 3;
 id | a |  b
---------------------------------------------------------------------
  1 | 2 | abc
  1 | 3 | abc
  2 | 3 | abcd
(3 rows)

-- we cannot immediately convert in the same statement, because
-- the name->OID conversion happens at parse time.
SELECT undistribute_table('dist_table'), create_distributed_table('dist_table', 'a');
NOTICE:  creating a new table for undistribute_table.dist_table
NOTICE:  moving the data of undistribute_table.dist_table
NOTICE:  dropping the old undistribute_table.dist_table
NOTICE:  renaming the new table to undistribute_table.dist_table
ERROR:  relation with OID XXXX does not exist
SELECT undistribute_table('dist_table');
NOTICE:  creating a new table for undistribute_table.dist_table
NOTICE:  moving the data of undistribute_table.dist_table
NOTICE:  dropping the old undistribute_table.dist_table
NOTICE:  renaming the new table to undistribute_table.dist_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

SELECT logicalrelid FROM pg_dist_partition WHERE logicalrelid = 'dist_table'::regclass;
 logicalrelid
---------------------------------------------------------------------
(0 rows)

SELECT run_command_on_workers($$SELECT COUNT(*) FROM pg_catalog.pg_class WHERE relname LIKE 'dist\_table\_%'$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

SELECT * FROM dist_table ORDER BY 1, 2, 3;
 id | a |  b
---------------------------------------------------------------------
  1 | 2 | abc
  1 | 3 | abc
  2 | 3 | abcd
(3 rows)

DROP TABLE dist_table;
-- test indexes
CREATE TABLE dist_table (id INT, a INT, b TEXT);
SELECT create_distributed_table('dist_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO dist_table VALUES (1, 2, 'abc'), (2, 3, 'abcd'), (1, 3, 'abc');
CREATE INDEX index1 ON dist_table (id);
SELECT * FROM pg_indexes WHERE tablename = 'dist_table';
     schemaname     | tablename  | indexname | tablespace |                               indexdef
---------------------------------------------------------------------
 undistribute_table | dist_table | index1    |            | CREATE INDEX index1 ON undistribute_table.dist_table USING btree (id)
(1 row)

SELECT undistribute_table('dist_table');
NOTICE:  creating a new table for undistribute_table.dist_table
NOTICE:  moving the data of undistribute_table.dist_table
NOTICE:  dropping the old undistribute_table.dist_table
NOTICE:  renaming the new table to undistribute_table.dist_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

SELECT * FROM pg_indexes WHERE tablename = 'dist_table';
     schemaname     | tablename  | indexname | tablespace |                               indexdef
---------------------------------------------------------------------
 undistribute_table | dist_table | index1    |            | CREATE INDEX index1 ON undistribute_table.dist_table USING btree (id)
(1 row)

DROP TABLE dist_table;
-- test tables with references
-- we expect errors
CREATE TABLE referenced_table (id INT PRIMARY KEY, a INT, b TEXT);
SELECT create_distributed_table('referenced_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO referenced_table VALUES (1, 2, 'abc'), (2, 3, 'abcd'), (4, 3, 'abc');
CREATE TABLE referencing_table (id INT REFERENCES referenced_table (id), a INT, b TEXT);
SELECT create_distributed_table('referencing_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO referencing_table VALUES (4, 6, 'cba'), (1, 1, 'dcba'), (2, 3, 'aaa');
SELECT undistribute_table('referenced_table');
ERROR:  cannot complete operation because table referenced_table is referenced by a foreign key
HINT:  Use cascade option to undistribute all the relations involved in a foreign key relationship with undistribute_table.referenced_table by executing SELECT undistribute_table($$undistribute_table.referenced_table$$, cascade_via_foreign_keys=>true)
SELECT undistribute_table('referencing_table');
ERROR:  cannot complete operation because table referencing_table has a foreign key
HINT:  Use cascade option to undistribute all the relations involved in a foreign key relationship with undistribute_table.referencing_table by executing SELECT undistribute_table($$undistribute_table.referencing_table$$, cascade_via_foreign_keys=>true)
DROP TABLE referenced_table, referencing_table;
-- test partitioned tables
CREATE TABLE partitioned_table (id INT, a INT) PARTITION BY RANGE (id);
CREATE TABLE partitioned_table_1_5 PARTITION OF partitioned_table FOR VALUES FROM (1) TO (5);
CREATE TABLE partitioned_table_6_10 PARTITION OF partitioned_table FOR VALUES FROM (6) TO (10);
SELECT create_distributed_table('partitioned_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO partitioned_table VALUES (2, 12), (7, 2);
SELECT logicalrelid FROM pg_dist_partition WHERE logicalrelid::regclass::text LIKE 'partitioned\_table%' ORDER BY 1;
      logicalrelid
---------------------------------------------------------------------
 partitioned_table
 partitioned_table_1_5
 partitioned_table_6_10
(3 rows)

SELECT run_command_on_workers($$SELECT COUNT(*) FROM pg_catalog.pg_class WHERE relname SIMILAR TO 'partitioned\_table%\d{3,}'$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,6)
 (localhost,57638,t,6)
(2 rows)

SELECT inhrelid::regclass FROM pg_catalog.pg_inherits WHERE inhparent = 'partitioned_table'::regclass ORDER BY 1;
        inhrelid
---------------------------------------------------------------------
 partitioned_table_1_5
 partitioned_table_6_10
(2 rows)

SELECT * FROM partitioned_table ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  2 | 12
  7 |  2
(2 rows)

SELECT * FROM partitioned_table_1_5 ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  2 | 12
(1 row)

SELECT * FROM partitioned_table_6_10 ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  7 | 2
(1 row)

-- undistributing partitions are not supported
SELECT undistribute_table('partitioned_table_1_5');
ERROR:  cannot complete operation because table is a partition
HINT:  the parent table is "partitioned_table"
-- we can undistribute partitioned parent tables
SELECT undistribute_table('partitioned_table');
NOTICE:  converting the partitions of undistribute_table.partitioned_table
NOTICE:  creating a new table for undistribute_table.partitioned_table_1_5
NOTICE:  moving the data of undistribute_table.partitioned_table_1_5
NOTICE:  dropping the old undistribute_table.partitioned_table_1_5
NOTICE:  renaming the new table to undistribute_table.partitioned_table_1_5
NOTICE:  creating a new table for undistribute_table.partitioned_table_6_10
NOTICE:  moving the data of undistribute_table.partitioned_table_6_10
NOTICE:  dropping the old undistribute_table.partitioned_table_6_10
NOTICE:  renaming the new table to undistribute_table.partitioned_table_6_10
NOTICE:  creating a new table for undistribute_table.partitioned_table
NOTICE:  dropping the old undistribute_table.partitioned_table
NOTICE:  renaming the new table to undistribute_table.partitioned_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

SELECT logicalrelid FROM pg_dist_partition WHERE logicalrelid::regclass::text LIKE 'partitioned\_table%'  ORDER BY 1;
 logicalrelid
---------------------------------------------------------------------
(0 rows)

SELECT run_command_on_workers($$SELECT COUNT(*) FROM pg_catalog.pg_class WHERE relname SIMILAR TO 'partitioned\_table%\d{3,}'$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

SELECT inhrelid::regclass FROM pg_catalog.pg_inherits WHERE inhparent = 'partitioned_table'::regclass ORDER BY 1;
        inhrelid
---------------------------------------------------------------------
 partitioned_table_1_5
 partitioned_table_6_10
(2 rows)

SELECT * FROM partitioned_table ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  2 | 12
  7 |  2
(2 rows)

SELECT * FROM partitioned_table_1_5 ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  2 | 12
(1 row)

SELECT * FROM partitioned_table_6_10 ORDER BY 1, 2;
 id | a
---------------------------------------------------------------------
  7 | 2
(1 row)

DROP TABLE partitioned_table;
-- test tables with sequences
CREATE TABLE seq_table (id INT, a bigserial);
SELECT create_distributed_table('seq_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT objid::regclass AS "Sequence Name" FROM pg_depend WHERE refobjid = 'seq_table'::regclass::oid AND classid = 'pg_class'::regclass::oid;
  Sequence Name
---------------------------------------------------------------------
 seq_table_a_seq
(1 row)

INSERT INTO seq_table (id) VALUES (5), (9), (3);
SELECT * FROM seq_table ORDER BY a;
 id | a
---------------------------------------------------------------------
  5 | 1
  9 | 2
  3 | 3
(3 rows)

SELECT undistribute_table('seq_table');
NOTICE:  creating a new table for undistribute_table.seq_table
NOTICE:  moving the data of undistribute_table.seq_table
NOTICE:  dropping the old undistribute_table.seq_table
NOTICE:  renaming the new table to undistribute_table.seq_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

SELECT objid::regclass AS "Sequence Name" FROM pg_depend WHERE refobjid = 'seq_table'::regclass::oid AND classid = 'pg_class'::regclass::oid;
  Sequence Name
---------------------------------------------------------------------
 seq_table_a_seq
(1 row)

INSERT INTO seq_table (id) VALUES (7), (1), (8);
SELECT * FROM seq_table ORDER BY a;
 id | a
---------------------------------------------------------------------
  5 | 1
  9 | 2
  3 | 3
  7 | 4
  1 | 5
  8 | 6
(6 rows)

DROP TABLE seq_table;
--test tables with views
CREATE TABLE view_table (a int, b int, c int);
SELECT create_distributed_table('view_table', 'a');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

INSERT INTO view_table VALUES (1, 2, 3), (2, 4, 6), (3, 6, 9);
CREATE SCHEMA another_schema;
CREATE VIEW undis_view1 AS SELECT a, b FROM view_table table_name_for_view;
CREATE VIEW undis_view2 AS SELECT a, c FROM view_table table_name_for_view;
CREATE VIEW another_schema.undis_view3 AS SELECT b, c FROM undis_view1 JOIN undis_view2 ON undis_view1.a = undis_view2.a;
SELECT schemaname, viewname, viewowner, definition FROM pg_views WHERE viewname LIKE 'undis\_view%' ORDER BY viewname;
     schemaname     |  viewname   | viewowner |                          definition
---------------------------------------------------------------------
 undistribute_table | undis_view1 | postgres  |  SELECT a,                              +
                    |             |           |     b                                   +
                    |             |           |    FROM view_table table_name_for_view;
 undistribute_table | undis_view2 | postgres  |  SELECT a,                              +
                    |             |           |     c                                   +
                    |             |           |    FROM view_table table_name_for_view;
 another_schema     | undis_view3 | postgres  |  SELECT undis_view1.b,                                      +
                    |             |           |     undis_view2.c                                           +
                    |             |           |    FROM (undis_view1                                        +
                    |             |           |      JOIN undis_view2 ON ((undis_view1.a = undis_view2.a)));
(3 rows)

SELECT * FROM another_schema.undis_view3 ORDER BY 1, 2;
 b | c
---------------------------------------------------------------------
 2 | 3
 4 | 6
 6 | 9
(3 rows)

SELECT undistribute_table('view_table');
NOTICE:  creating a new table for undistribute_table.view_table
NOTICE:  moving the data of undistribute_table.view_table
NOTICE:  dropping the old undistribute_table.view_table
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view undis_view1
drop cascades to view undis_view2
drop cascades to view another_schema.undis_view3
CONTEXT:  SQL statement "DROP TABLE undistribute_table.view_table CASCADE"
NOTICE:  renaming the new table to undistribute_table.view_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

SELECT schemaname, viewname, viewowner, definition FROM pg_views WHERE viewname LIKE 'undis\_view%' ORDER BY viewname;
     schemaname     |  viewname   | viewowner |                          definition
---------------------------------------------------------------------
 undistribute_table | undis_view1 | postgres  |  SELECT a,                              +
                    |             |           |     b                                   +
                    |             |           |    FROM view_table table_name_for_view;
 undistribute_table | undis_view2 | postgres  |  SELECT a,                              +
                    |             |           |     c                                   +
                    |             |           |    FROM view_table table_name_for_view;
 another_schema     | undis_view3 | postgres  |  SELECT undis_view1.b,                                      +
                    |             |           |     undis_view2.c                                           +
                    |             |           |    FROM (undis_view1                                        +
                    |             |           |      JOIN undis_view2 ON ((undis_view1.a = undis_view2.a)));
(3 rows)

SELECT * FROM another_schema.undis_view3 ORDER BY 1, 2;
 b | c
---------------------------------------------------------------------
 2 | 3
 4 | 6
 6 | 9
(3 rows)

-- test the drop in undistribute_table cannot cascade to an extension
CREATE TABLE extension_table (a INT);
SELECT create_distributed_table('extension_table', 'a');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE VIEW extension_view AS SELECT * FROM extension_table;
ALTER EXTENSION plpgsql ADD VIEW extension_view;
NOTICE:  Citus does not propagate adding/dropping member objects
HINT:  You can add/drop the member objects on the workers as well.
SELECT undistribute_table ('extension_table');
ERROR:  cannot alter table because an extension depends on it
-- test the drop that doesn't cascade to an extension
CREATE TABLE dist_type_table (a INT, b citus.distribution_type);
SELECT create_distributed_table('dist_type_table', 'a');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT undistribute_table('dist_type_table');
NOTICE:  creating a new table for undistribute_table.dist_type_table
NOTICE:  moving the data of undistribute_table.dist_type_table
NOTICE:  dropping the old undistribute_table.dist_type_table
NOTICE:  renaming the new table to undistribute_table.dist_type_table
 undistribute_table
---------------------------------------------------------------------

(1 row)

-- test CREATE RULE without ON SELECT
CREATE TABLE rule_table_3 (a INT);
CREATE TABLE rule_table_4 (a INT);
SELECT create_distributed_table('rule_table_4', 'a');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE RULE "rule_1" AS ON INSERT TO rule_table_3 DO INSTEAD SELECT * FROM rule_table_4;
ALTER EXTENSION plpgsql ADD TABLE rule_table_3;
NOTICE:  Citus does not propagate adding/dropping member objects
HINT:  You can add/drop the member objects on the workers as well.
SELECT undistribute_table('rule_table_4');
NOTICE:  creating a new table for undistribute_table.rule_table_4
NOTICE:  moving the data of undistribute_table.rule_table_4
NOTICE:  dropping the old undistribute_table.rule_table_4
NOTICE:  drop cascades to rule rule_1 on table rule_table_3
CONTEXT:  SQL statement "DROP TABLE undistribute_table.rule_table_4 CASCADE"
NOTICE:  renaming the new table to undistribute_table.rule_table_4
 undistribute_table
---------------------------------------------------------------------

(1 row)

ALTER EXTENSION plpgsql DROP VIEW extension_view;
NOTICE:  Citus does not propagate adding/dropping member objects
HINT:  You can add/drop the member objects on the workers as well.
ALTER EXTENSION plpgsql DROP TABLE rule_table_3;
NOTICE:  Citus does not propagate adding/dropping member objects
HINT:  You can add/drop the member objects on the workers as well.
DROP TABLE view_table CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to view undis_view1
drop cascades to view undis_view2
drop cascades to view another_schema.undis_view3
DROP SCHEMA undistribute_table, another_schema CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to table extension_table
drop cascades to view extension_view
drop cascades to table dist_type_table
drop cascades to table rule_table_3
drop cascades to table rule_table_4
