--
-- DROP_PARTITIONED_TABLE
--
-- Tests to make sure that we properly drop distributed partitioned tables
--
SET citus.next_shard_id TO 720000;
SET citus.shard_count TO 4;
SET citus.shard_replication_factor TO 1;
CREATE SCHEMA drop_partitioned_table;
SET search_path = drop_partitioned_table;
-- create a function that allows us to see the values of
-- original and normal values for each dropped table
-- coming from pg_event_trigger_dropped_objects()
-- for now the only case where we can distinguish a
-- dropped partition because of its dropped parent
-- is when both values are false: check citus_drop_trigger
CREATE FUNCTION check_original_normal_values()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    v_obj record;
BEGIN
    FOR v_obj IN SELECT * FROM pg_event_trigger_dropped_objects()
        WHERE object_type IN ('table', 'foreign table')
    LOOP
	    RAISE NOTICE 'dropped object: %.% original: % normal: %',
            v_obj.schema_name,
            v_obj.object_name,
            v_obj.original,
            v_obj.normal;
    END LOOP;
END;
$$;
CREATE EVENT TRIGGER new_trigger_for_drops
   ON sql_drop
   EXECUTE FUNCTION check_original_normal_values();
-- create a view printing the same output as \d for this test's schemas
-- since \d output is not guaranteed to be consistent between different PG versions etc
CREATE VIEW tables_info AS
SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'p' THEN 'partitioned table' END as "Type",
    pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_user u ON u.usesysid = c.relowner
WHERE n.nspname IN ('drop_partitioned_table', 'schema1')
    AND c.relkind IN ('r','p')
ORDER BY 1, 2;
SET citus.next_shard_id TO 721000;
-- CASE 1
-- Dropping the parent table
CREATE TABLE parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
SELECT create_distributed_table('parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT * FROM drop_partitioned_table.tables_info;
         Schema         |  Name  |       Type        |  Owner
---------------------------------------------------------------------
 drop_partitioned_table | child1 | table             | postgres
 drop_partitioned_table | child2 | table             | postgres
 drop_partitioned_table | parent | partitioned table | postgres
(3 rows)

\set VERBOSITY terse
DROP TABLE parent;
NOTICE:  dropped object: drop_partitioned_table.parent original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child1 original: f normal: f
NOTICE:  dropped object: drop_partitioned_table.child2 original: f normal: f
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET search_path = drop_partitioned_table;
SET citus.next_shard_id TO 722000;
-- CASE 2
-- Dropping the parent table, but including children in the DROP command
CREATE TABLE parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
SELECT create_distributed_table('parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT * FROM drop_partitioned_table.tables_info;
         Schema         |  Name  |       Type        |  Owner
---------------------------------------------------------------------
 drop_partitioned_table | child1 | table             | postgres
 drop_partitioned_table | child2 | table             | postgres
 drop_partitioned_table | parent | partitioned table | postgres
(3 rows)

\set VERBOSITY terse
DROP TABLE child1, parent, child2;
NOTICE:  dropped object: drop_partitioned_table.parent original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child2 original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child1 original: t normal: f
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET search_path = drop_partitioned_table;
SET citus.next_shard_id TO 723000;
-- CASE 3
-- DROP OWNED BY role1; Only parent is owned by role1, children are owned by another owner
SET client_min_messages TO ERROR;
CREATE ROLE role1;
SELECT 1 FROM run_command_on_workers('CREATE ROLE role1');
 ?column?
---------------------------------------------------------------------
        1
        1
(2 rows)

RESET client_min_messages;
GRANT ALL ON SCHEMA drop_partitioned_table TO role1;
SET ROLE role1;
CREATE TABLE drop_partitioned_table.parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
RESET ROLE;
CREATE TABLE child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
SELECT create_distributed_table('parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT * FROM drop_partitioned_table.tables_info;
         Schema         |  Name  |       Type        |  Owner
---------------------------------------------------------------------
 drop_partitioned_table | child1 | table             | postgres
 drop_partitioned_table | child2 | table             | postgres
 drop_partitioned_table | parent | partitioned table | role1
(3 rows)

\set VERBOSITY terse
DROP OWNED BY role1;
NOTICE:  dropped object: drop_partitioned_table.parent original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child1 original: f normal: f
NOTICE:  dropped object: drop_partitioned_table.child2 original: f normal: f
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET search_path = drop_partitioned_table;
SET citus.next_shard_id TO 724000;
-- CASE 4
-- DROP OWNED BY role1; Parent and children are owned by role1
GRANT ALL ON SCHEMA drop_partitioned_table TO role1;
SET ROLE role1;
CREATE TABLE drop_partitioned_table.parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE drop_partitioned_table.child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE drop_partitioned_table.parent ATTACH PARTITION drop_partitioned_table.child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE drop_partitioned_table.child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE drop_partitioned_table.parent ATTACH PARTITION drop_partitioned_table.child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
RESET ROLE;
SELECT create_distributed_table('parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT * FROM drop_partitioned_table.tables_info;
         Schema         |  Name  |       Type        | Owner
---------------------------------------------------------------------
 drop_partitioned_table | child1 | table             | role1
 drop_partitioned_table | child2 | table             | role1
 drop_partitioned_table | parent | partitioned table | role1
(3 rows)

\set VERBOSITY terse
DROP OWNED BY role1;
NOTICE:  dropped object: drop_partitioned_table.parent original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child1 original: t normal: f
NOTICE:  dropped object: drop_partitioned_table.child2 original: t normal: f
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET search_path = drop_partitioned_table;
SET citus.next_shard_id TO 725000;
REVOKE ALL ON SCHEMA drop_partitioned_table FROM role1;
DROP ROLE role1;
SELECT run_command_on_workers('DROP ROLE IF EXISTS role1');
     run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,"DROP ROLE")
 (localhost,57638,t,"DROP ROLE")
(2 rows)

-- CASE 5
-- DROP SCHEMA schema1 CASCADE; Parent is in schema1, children are in another schema
CREATE SCHEMA schema1;
CREATE TABLE schema1.parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE schema1.parent ATTACH PARTITION child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE schema1.parent ATTACH PARTITION child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
SELECT create_distributed_table('schema1.parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SET search_path = drop_partitioned_table, schema1;
SELECT * FROM drop_partitioned_table.tables_info;
         Schema         |  Name  |       Type        |  Owner
---------------------------------------------------------------------
 drop_partitioned_table | child1 | table             | postgres
 drop_partitioned_table | child2 | table             | postgres
 schema1                | parent | partitioned table | postgres
(3 rows)

\set VERBOSITY terse
DROP SCHEMA schema1 CASCADE;
NOTICE:  drop cascades to table parent
NOTICE:  dropped object: schema1.parent original: f normal: t
NOTICE:  dropped object: drop_partitioned_table.child1 original: f normal: f
NOTICE:  dropped object: drop_partitioned_table.child2 original: f normal: f
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table, schema1;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET citus.next_shard_id TO 726000;
-- CASE 6
-- DROP SCHEMA schema1 CASCADE; Parent and children are in schema1
CREATE SCHEMA schema1;
CREATE TABLE schema1.parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE schema1.child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE schema1.parent ATTACH PARTITION schema1.child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
CREATE TABLE schema1.child2 (x text, t timestamptz DEFAULT now());
ALTER TABLE schema1.parent ATTACH PARTITION schema1.child2 FOR VALUES FROM ('2021-06-30') TO ('2021-07-01');
SELECT create_distributed_table('schema1.parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SET search_path = drop_partitioned_table, schema1;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema  |  Name  |       Type        |  Owner
---------------------------------------------------------------------
 schema1 | child1 | table             | postgres
 schema1 | child2 | table             | postgres
 schema1 | parent | partitioned table | postgres
(3 rows)

\set VERBOSITY terse
DROP SCHEMA schema1 CASCADE;
NOTICE:  drop cascades to table parent
NOTICE:  dropped object: schema1.parent original: f normal: t
NOTICE:  dropped object: schema1.child1 original: f normal: t
NOTICE:  dropped object: schema1.child2 original: f normal: t
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :worker_1_port
SET search_path = drop_partitioned_table, schema1;
SELECT * FROM drop_partitioned_table.tables_info;
 Schema | Name | Type | Owner
---------------------------------------------------------------------
(0 rows)

\c - - - :master_port
SET search_path = drop_partitioned_table;
-- Check that we actually skip sending remote commands to skip shards
SET citus.shard_count TO 1;
SET citus.shard_replication_factor TO 1;
SET citus.next_shard_id TO 727000;
ALTER SEQUENCE pg_catalog.pg_dist_colocationid_seq RESTART 1344400;
DROP EVENT TRIGGER new_trigger_for_drops;
-- Case 1 - we should skip
CREATE TABLE parent (x text, t timestamptz DEFAULT now()) PARTITION BY RANGE (t);
CREATE TABLE child1 (x text, t timestamptz DEFAULT now());
ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES FROM ('2021-05-31') TO ('2021-06-01');
SELECT create_distributed_table('parent','x');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

BEGIN;
SET citus.log_remote_commands TO on;
DROP TABLE parent;
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
NOTICE:  issuing ALTER TABLE IF EXISTS drop_partitioned_table.parent DETACH PARTITION drop_partitioned_table.child1;
NOTICE:  issuing ALTER TABLE IF EXISTS drop_partitioned_table.parent DETACH PARTITION drop_partitioned_table.child1;
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.parent')
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.parent')
NOTICE:  issuing DROP TABLE IF EXISTS drop_partitioned_table.parent_xxxxx CASCADE
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.child1')
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.child1')
NOTICE:  issuing SELECT pg_catalog.citus_internal_delete_colocation_metadata(1344400)
NOTICE:  issuing SELECT pg_catalog.citus_internal_delete_colocation_metadata(1344400)
ROLLBACK;
NOTICE:  issuing ROLLBACK
NOTICE:  issuing ROLLBACK
-- Case 2 - we shouldn't skip
BEGIN;
SET citus.log_remote_commands TO on;
DROP TABLE parent, child1;
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'off'
NOTICE:  issuing ALTER TABLE IF EXISTS drop_partitioned_table.parent DETACH PARTITION drop_partitioned_table.child1;
NOTICE:  issuing ALTER TABLE IF EXISTS drop_partitioned_table.parent DETACH PARTITION drop_partitioned_table.child1;
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
NOTICE:  issuing SET citus.enable_ddl_propagation TO 'on'
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.parent')
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.parent')
NOTICE:  issuing DROP TABLE IF EXISTS drop_partitioned_table.parent_xxxxx CASCADE
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.child1')
NOTICE:  issuing SELECT worker_drop_distributed_table('drop_partitioned_table.child1')
NOTICE:  issuing DROP TABLE IF EXISTS drop_partitioned_table.child1_xxxxx CASCADE
NOTICE:  issuing SELECT pg_catalog.citus_internal_delete_colocation_metadata(1344400)
NOTICE:  issuing SELECT pg_catalog.citus_internal_delete_colocation_metadata(1344400)
ROLLBACK;
NOTICE:  issuing ROLLBACK
NOTICE:  issuing ROLLBACK
DROP SCHEMA drop_partitioned_table CASCADE;
NOTICE:  drop cascades to 3 other objects
SET search_path TO public;
-- dropping the schema should drop the metadata on the workers
CREATE SCHEMA partitioning_schema;
SET search_path TO partitioning_schema;
CREATE TABLE part_table (
      col timestamp
  ) PARTITION BY RANGE (col);
CREATE TABLE part_table_1
  PARTITION OF part_table
  FOR VALUES FROM ('2010-01-01') TO ('2015-01-01');
SELECT create_distributed_table('part_table', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

-- show we have pg_dist_partition entries on the workers
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_partition where exists(select * from pg_class where pg_class.oid=pg_dist_partition.logicalrelid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,2)
 (localhost,57638,t,2)
(2 rows)

-- show we have pg_dist_object entries on the workers
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_object as obj where classid = 1259 AND exists(select * from pg_class where pg_class.oid=obj.objid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,2)
 (localhost,57638,t,2)
(2 rows)

DROP SCHEMA partitioning_schema CASCADE;
NOTICE:  drop cascades to table part_table
-- show we don't have pg_dist_partition entries on the workers after dropping the schema
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_partition where exists(select * from pg_class where pg_class.oid=pg_dist_partition.logicalrelid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

-- show we don't have pg_dist_object entries on the workers after dropping the schema
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_object as obj where classid = 1259 AND exists(select * from pg_class where pg_class.oid=obj.objid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

-- dropping the parent should drop the metadata on the workers
CREATE SCHEMA partitioning_schema;
SET search_path TO partitioning_schema;
CREATE TABLE part_table (
      col timestamp
  ) PARTITION BY RANGE (col);
CREATE TABLE part_table_1
  PARTITION OF part_table
  FOR VALUES FROM ('2010-01-01') TO ('2015-01-01');
SELECT create_distributed_table('part_table', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

DROP TABLE part_table;
-- show we don't have pg_dist_partition entries on the workers after dropping the parent
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_partition where exists(select * from pg_class where pg_class.oid=pg_dist_partition.logicalrelid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

-- show we don't have pg_dist_object entries on the workers after dropping the parent
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_object as obj where classid = 1259 AND exists(select * from pg_class where pg_class.oid=obj.objid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,0)
 (localhost,57638,t,0)
(2 rows)

SET search_path TO partitioning_schema;
CREATE TABLE part_table (
      col timestamp
  ) PARTITION BY RANGE (col);
CREATE TABLE part_table_1
  PARTITION OF part_table
  FOR VALUES FROM ('2010-01-01') TO ('2015-01-01');
SELECT create_distributed_table('part_table', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

DROP TABLE part_table_1;
-- show we have pg_dist_partition entries for the parent on the workers after dropping the partition
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_partition where exists(select * from pg_class where pg_class.oid=pg_dist_partition.logicalrelid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,1)
 (localhost,57638,t,1)
(2 rows)

-- show we have pg_dist_object entries for the parent on the workers after dropping the partition
SELECT run_command_on_workers($$SELECT count(*) FROM  pg_dist_object as obj where classid = 1259 AND exists(select * from pg_class where pg_class.oid=obj.objid AND relname ILIKE '%part_table%');$$);
 run_command_on_workers
---------------------------------------------------------------------
 (localhost,57637,t,1)
 (localhost,57638,t,1)
(2 rows)

-- clean-up
DROP SCHEMA partitioning_schema CASCADE;
NOTICE:  drop cascades to table part_table