\set VERBOSITY terse
SET citus.next_shard_id TO 1800000;
SET citus.next_placement_id TO 8500000;
SET citus.shard_replication_factor TO 1;
CREATE SCHEMA create_ref_dist_from_citus_local;
SET search_path TO create_ref_dist_from_citus_local;
SET client_min_messages to ERROR;
-- ensure that coordinator is added to pg_dist_node
SELECT 1 FROM master_add_node('localhost', :master_port, groupId => 0);
 ?column?
---------------------------------------------------------------------
        1
(1 row)

CREATE TABLE citus_local_table_1 (col_1 INT UNIQUE);
CREATE TABLE citus_local_table_2 (col_1 INT UNIQUE);
CREATE TABLE citus_local_table_3 (col_1 INT UNIQUE);
CREATE TABLE citus_local_table_4 (col_1 INT UNIQUE);
ALTER TABLE citus_local_table_2 ADD CONSTRAINT fkey_1 FOREIGN KEY (col_1) REFERENCES citus_local_table_1(col_1);
ALTER TABLE citus_local_table_3 ADD CONSTRAINT fkey_2 FOREIGN KEY (col_1) REFERENCES citus_local_table_1(col_1);
ALTER TABLE citus_local_table_1 ADD CONSTRAINT fkey_3 FOREIGN KEY (col_1) REFERENCES citus_local_table_3(col_1);
ALTER TABLE citus_local_table_1 ADD CONSTRAINT fkey_4 FOREIGN KEY (col_1) REFERENCES citus_local_table_4(col_1);
ALTER TABLE citus_local_table_4 ADD CONSTRAINT fkey_5 FOREIGN KEY (col_1) REFERENCES citus_local_table_3(col_1);
ALTER TABLE citus_local_table_4 ADD CONSTRAINT fkey_6 FOREIGN KEY (col_1) REFERENCES citus_local_table_4(col_1);
SELECT citus_add_local_table_to_metadata('citus_local_table_1', cascade_via_foreign_keys=>true);
 citus_add_local_table_to_metadata
---------------------------------------------------------------------

(1 row)

CREATE TABLE reference_table_1(col_1 INT UNIQUE, col_2 INT UNIQUE);
CREATE TABLE reference_table_2(col_1 INT UNIQUE, col_2 INT UNIQUE);
SELECT create_reference_table('reference_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

SELECT create_reference_table('reference_table_2');
 create_reference_table
---------------------------------------------------------------------

(1 row)

ALTER TABLE citus_local_table_4 ADD CONSTRAINT fkey_7 FOREIGN KEY (col_1) REFERENCES reference_table_1(col_1);
ALTER TABLE reference_table_2 ADD CONSTRAINT fkey_8 FOREIGN KEY (col_1) REFERENCES citus_local_table_2(col_1);
CREATE TABLE distributed_table_1(col_1 INT UNIQUE, col_2 INT);
CREATE TABLE partitioned_dist_table_1 (col_1 INT UNIQUE, col_2 INT) PARTITION BY RANGE (col_1);
SELECT create_distributed_table('distributed_table_1', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_table('partitioned_dist_table_1', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

ALTER TABLE partitioned_dist_table_1 ADD CONSTRAINT fkey_9 FOREIGN KEY (col_1) REFERENCES distributed_table_1(col_1);
ALTER TABLE distributed_table_1 ADD CONSTRAINT fkey_10 FOREIGN KEY (col_1) REFERENCES reference_table_2(col_2);
ALTER TABLE partitioned_dist_table_1 ADD CONSTRAINT fkey_11 FOREIGN KEY (col_1) REFERENCES reference_table_1(col_2);
-- As we will heavily rely on this feature after implementing automatic
-- convertion of postgres tables to citus local tables, let's have a
-- complex foreign key graph to see everything is fine.
--
--  distributed_table_1  <----------------    partitioned_dist_table_1
--           |                                           |
--           v                                           v
--  reference_table_2                 _          reference_table_1
--           |                       | |                 ^
--           v                       | v                 |
-- citus_local_table_2 -> citus_local_table_1 -> citus_local_table_4
--                                   ^ |                 |
--                                   | v                 |
--                           citus_local_table_3 <--------
-- Now print metadata after each of create_reference/distributed_table
-- operations to show that everything is fine. Also show that we
-- preserve foreign keys.
BEGIN;
  SELECT create_reference_table('citus_local_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

  SELECT logicalrelid::text AS tablename, partmethod, repmodel FROM pg_dist_partition
  WHERE logicalrelid::text IN (SELECT tablename FROM pg_tables WHERE schemaname='create_ref_dist_from_citus_local')
  ORDER BY tablename;
        tablename         | partmethod | repmodel
---------------------------------------------------------------------
 citus_local_table_1      | n          | t
 citus_local_table_2      | n          | s
 citus_local_table_3      | n          | s
 citus_local_table_4      | n          | s
 distributed_table_1      | h          | s
 partitioned_dist_table_1 | h          | s
 reference_table_1        | n          | t
 reference_table_2        | n          | t
(8 rows)

  SELECT COUNT(*)=11 FROM pg_constraint
  WHERE connamespace = (SELECT oid FROM pg_namespace WHERE nspname='create_ref_dist_from_citus_local') AND
        conname ~ '^fkey\_\d+$';
 ?column?
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
BEGIN;
  SELECT create_reference_table('citus_local_table_2');
 create_reference_table
---------------------------------------------------------------------

(1 row)

  SELECT logicalrelid::text AS tablename, partmethod, repmodel FROM pg_dist_partition
  WHERE logicalrelid::text IN (SELECT tablename FROM pg_tables WHERE schemaname='create_ref_dist_from_citus_local')
  ORDER BY tablename;
        tablename         | partmethod | repmodel
---------------------------------------------------------------------
 citus_local_table_1      | n          | s
 citus_local_table_2      | n          | t
 citus_local_table_3      | n          | s
 citus_local_table_4      | n          | s
 distributed_table_1      | h          | s
 partitioned_dist_table_1 | h          | s
 reference_table_1        | n          | t
 reference_table_2        | n          | t
(8 rows)

  SELECT COUNT(*)=11 FROM pg_constraint
  WHERE connamespace = (SELECT oid FROM pg_namespace WHERE nspname='create_ref_dist_from_citus_local') AND
        conname ~ '^fkey\_\d+$';
 ?column?
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
-- those two errors out as they reference to citus local tables but
-- distributed tables cannot reference to postgres or citus local tables
SELECT create_distributed_table('citus_local_table_1', 'col_1');
ERROR:  cannot create foreign key constraint since relations are not colocated or not referencing a reference table
SELECT create_distributed_table('citus_local_table_4', 'col_1');
ERROR:  cannot create foreign key constraint since relations are not colocated or not referencing a reference table
BEGIN;
  SELECT create_reference_table('citus_local_table_2');
 create_reference_table
---------------------------------------------------------------------

(1 row)

  -- this would error out
  SELECT create_reference_table('citus_local_table_2');
ERROR:  table "citus_local_table_2" is already distributed
ROLLBACK;
-- test with a standalone table
CREATE TABLE citus_local_table_5 (col_1 INT UNIQUE);
SELECT citus_add_local_table_to_metadata('citus_local_table_5');
 citus_add_local_table_to_metadata
---------------------------------------------------------------------

(1 row)

BEGIN;
  SELECT create_distributed_table('citus_local_table_5', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

  -- this would error out
  SELECT create_reference_table('citus_local_table_5');
ERROR:  table "citus_local_table_5" is already distributed
ROLLBACK;
BEGIN;
  SELECT create_reference_table('citus_local_table_5');
 create_reference_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
BEGIN;
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_5(col_1);
  SELECT create_reference_table('citus_local_table_5');
 create_reference_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
BEGIN;
  SELECT create_distributed_table('citus_local_table_5', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

  SELECT logicalrelid::text AS tablename, partmethod, repmodel FROM pg_dist_partition
  WHERE logicalrelid::text IN (SELECT tablename FROM pg_tables WHERE schemaname='create_ref_dist_from_citus_local')
  ORDER BY tablename;
        tablename         | partmethod | repmodel
---------------------------------------------------------------------
 citus_local_table_1      | n          | s
 citus_local_table_2      | n          | s
 citus_local_table_3      | n          | s
 citus_local_table_4      | n          | s
 citus_local_table_5      | h          | s
 distributed_table_1      | h          | s
 partitioned_dist_table_1 | h          | s
 reference_table_1        | n          | t
 reference_table_2        | n          | t
(9 rows)

  SELECT COUNT(*)=11 FROM pg_constraint
  WHERE connamespace = (SELECT oid FROM pg_namespace WHERE nspname='create_ref_dist_from_citus_local') AND
        conname ~ '^fkey\_\d+$';
 ?column?
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
BEGIN;
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_5(col_1);
  SELECT create_distributed_table('citus_local_table_5', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

  SELECT logicalrelid::text AS tablename, partmethod, repmodel FROM pg_dist_partition
  WHERE logicalrelid::text IN (SELECT tablename FROM pg_tables WHERE schemaname='create_ref_dist_from_citus_local')
  ORDER BY tablename;
        tablename         | partmethod | repmodel
---------------------------------------------------------------------
 citus_local_table_1      | n          | s
 citus_local_table_2      | n          | s
 citus_local_table_3      | n          | s
 citus_local_table_4      | n          | s
 citus_local_table_5      | h          | s
 distributed_table_1      | h          | s
 partitioned_dist_table_1 | h          | s
 reference_table_1        | n          | t
 reference_table_2        | n          | t
(9 rows)

  SELECT COUNT(*)=12 FROM pg_constraint
  WHERE connamespace = (SELECT oid FROM pg_namespace WHERE nspname='create_ref_dist_from_citus_local') AND
        conname ~ '^fkey\_\d+$';
 ?column?
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
BEGIN;
  -- define a self reference and a foreign key to reference table
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_5(col_1);
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_13 FOREIGN KEY (col_1) REFERENCES reference_table_1(col_1);
  SELECT create_distributed_table('citus_local_table_5', 'col_1');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

  SELECT logicalrelid::text AS tablename, partmethod, repmodel FROM pg_dist_partition
  WHERE logicalrelid::text IN (SELECT tablename FROM pg_tables WHERE schemaname='create_ref_dist_from_citus_local')
  ORDER BY tablename;
        tablename         | partmethod | repmodel
---------------------------------------------------------------------
 citus_local_table_1      | n          | s
 citus_local_table_2      | n          | s
 citus_local_table_3      | n          | s
 citus_local_table_4      | n          | s
 citus_local_table_5      | h          | s
 distributed_table_1      | h          | s
 partitioned_dist_table_1 | h          | s
 reference_table_1        | n          | t
 reference_table_2        | n          | t
(9 rows)

  SELECT COUNT(*)=13 FROM pg_constraint
  WHERE connamespace = (SELECT oid FROM pg_namespace WHERE nspname='create_ref_dist_from_citus_local') AND
        conname ~ '^fkey\_\d+$';
 ?column?
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
CREATE TABLE citus_local_table_6 (col_1 INT UNIQUE);
SELECT citus_add_local_table_to_metadata('citus_local_table_6');
 citus_add_local_table_to_metadata
---------------------------------------------------------------------

(1 row)

BEGIN;
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_6(col_1);
  -- errors out as foreign keys from distributed tables to citus
  -- local tables are not supported
  SELECT create_distributed_table('citus_local_table_5', 'col_1');
ERROR:  cannot create foreign key constraint since relations are not colocated or not referencing a reference table
ROLLBACK;
BEGIN;
  -- errors out as foreign keys from citus local tables to distributed
  -- tables are not supported
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_6(col_1);
  SELECT create_distributed_table('citus_local_table_6', 'col_1');
ERROR:  cannot create foreign key constraint since foreign keys from reference tables and local tables to distributed tables are not supported
ROLLBACK;
-- have some more tests with foreign keys between citus local
-- and reference tables
BEGIN;
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_6(col_1);
  SELECT create_reference_table('citus_local_table_5');
 create_reference_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
BEGIN;
  ALTER TABLE citus_local_table_5 ADD CONSTRAINT fkey_12 FOREIGN KEY (col_1) REFERENCES citus_local_table_6(col_1);
  SELECT create_reference_table('citus_local_table_6');
 create_reference_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
BEGIN;
  CREATE FUNCTION update_value() RETURNS trigger AS $update_value$
  BEGIN
      NEW.value := value+1 ;
      RETURN NEW;
  END;
  $update_value$ LANGUAGE plpgsql;
  CREATE TRIGGER update_value_dist
  AFTER INSERT ON citus_local_table_6
  FOR EACH ROW EXECUTE PROCEDURE update_value();
  -- show that we error out as we don't supprt triggers on distributed tables
  SELECT create_distributed_table('citus_local_table_6', 'col_1');
ERROR:  cannot distribute relation "citus_local_table_6" because it has triggers
ROLLBACK;
-- make sure that creating append / range distributed tables is also ok
BEGIN;
  SELECT create_distributed_table('citus_local_table_5', 'col_1', 'range');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
BEGIN;
  ALTER TABLE citus_local_table_5 DROP CONSTRAINT citus_local_table_5_col_1_key;
  SELECT create_distributed_table('citus_local_table_5', 'col_1', 'append');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

ROLLBACK;
-- cleanup at exit
DROP SCHEMA create_ref_dist_from_citus_local CASCADE;