SET citus.next_shard_id TO 3000000;
SET citus.shard_replication_factor TO 1;
CREATE SCHEMA fkey_graph;
SET search_path TO 'fkey_graph';
CREATE FUNCTION get_referencing_relation_id_list(Oid)
    RETURNS SETOF Oid
    LANGUAGE C STABLE STRICT
    AS 'citus', $$get_referencing_relation_id_list$$;
CREATE FUNCTION get_referenced_relation_id_list(Oid)
    RETURNS SETOF Oid
    LANGUAGE C STABLE STRICT
    AS 'citus', $$get_referenced_relation_id_list$$;
-- Simple case with distributed tables
CREATE TABLE dtt1(id int PRIMARY KEY);
SELECT create_distributed_table('dtt1','id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE dtt2(id int PRIMARY KEY REFERENCES dtt1(id));
SELECT create_distributed_table('dtt2','id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE dtt3(id int PRIMARY KEY REFERENCES dtt2(id));
SELECT create_distributed_table('dtt3','id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt1'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt2'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
(1 row)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
 dtt2
(2 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt1'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 dtt2
 dtt3
(2 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt2'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 dtt3
(1 row)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

CREATE TABLE dtt4(id int PRIMARY KEY);
SELECT create_distributed_table('dtt4', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

ALTER TABLE dtt4 ADD CONSTRAINT dtt4_fkey FOREIGN KEY (id) REFERENCES dtt3(id);
SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
 dtt2
 dtt3
(3 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt1'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt2'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
(1 row)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
 dtt2
(2 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt1'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 dtt2
 dtt3
 dtt4
(3 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt2'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 dtt3
 dtt4
(2 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 dtt4
(1 row)

ALTER TABLE dtt4 DROP CONSTRAINT dtt4_fkey;
SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 dtt1
 dtt2
(2 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt3'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('dtt4'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

-- some tests within transction blocks to make sure that
-- cache invalidation works fine
CREATE TABLE test_1 (id int UNIQUE);
CREATE TABLE test_2 (id int UNIQUE);
CREATE TABLE test_3 (id int UNIQUE);
CREATE TABLE test_4 (id int UNIQUE);
CREATE TABLE test_5 (id int UNIQUE);
SELECT create_distributed_Table('test_1', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_Table('test_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_Table('test_3', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_Table('test_4', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_Table('test_5', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE VIEW referential_integrity_summary AS
    WITH RECURSIVE referential_integrity_summary(n, table_name, referencing_relations, referenced_relations) AS
    (
        SELECT 0,'0','{}'::regclass[],'{}'::regclass[]
      UNION ALL
        SELECT
          n + 1,
          'test_' || n + 1|| '' as table_name,
          (SELECT  array_agg(get_referencing_relation_id_list::regclass ORDER BY 1) FROM get_referencing_relation_id_list(('test_' || (n +1) ) ::regclass)) as referencing_relations,
          (SELECT  array_agg(get_referenced_relation_id_list::regclass ORDER BY 1) FROM get_referenced_relation_id_list(('test_' || (n +1) ) ::regclass)) as referenced_by_relations
        FROM referential_integrity_summary, pg_class
        WHERE
         pg_class.relname = ('test_' || (n +1))
        AND n < 5
    )
    SELECT * FROM referential_integrity_summary WHERE n != 0 ORDER BY 1;
-- make sure that invalidation through ALTER TABLE works fine
BEGIN;
    ALTER TABLE test_2 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_1(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
 3 | test_3     |                       |
 4 | test_4     |                       |
 5 | test_5     |                       |
(5 rows)

    ALTER TABLE test_3 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_2(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3}       |
 2 | test_2     | {test_3}              | {test_1}
 3 | test_3     |                       | {test_2,test_1}
 4 | test_4     |                       |
 5 | test_5     |                       |
(5 rows)

    ALTER TABLE test_4 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_3(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations  |  referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4} |
 2 | test_2     | {test_3,test_4}        | {test_1}
 3 | test_3     | {test_4}               | {test_2,test_1}
 4 | test_4     |                        | {test_3,test_2,test_1}
 5 | test_5     |                        |
(5 rows)

    ALTER TABLE test_5 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_4(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name |     referencing_relations     |     referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4,test_5} |
 2 | test_2     | {test_3,test_4,test_5}        | {test_1}
 3 | test_3     | {test_4,test_5}               | {test_2,test_1}
 4 | test_4     | {test_5}                      | {test_3,test_2,test_1}
 5 | test_5     |                               | {test_4,test_3,test_2,test_1}
(5 rows)

ROLLBACK;
-- similar test, but slightly different order of creating foreign keys
BEGIN;
    ALTER TABLE test_2 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_1(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
 3 | test_3     |                       |
 4 | test_4     |                       |
 5 | test_5     |                       |
(5 rows)

    ALTER TABLE test_4 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_3(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
 3 | test_3     | {test_4}              |
 4 | test_4     |                       | {test_3}
 5 | test_5     |                       |
(5 rows)

    ALTER TABLE test_5 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_4(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
 3 | test_3     | {test_4,test_5}       |
 4 | test_4     | {test_5}              | {test_3}
 5 | test_5     |                       | {test_4,test_3}
(5 rows)

    ALTER TABLE test_3 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_2(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name |     referencing_relations     |     referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4,test_5} |
 2 | test_2     | {test_3,test_4,test_5}        | {test_1}
 3 | test_3     | {test_4,test_5}               | {test_2,test_1}
 4 | test_4     | {test_5}                      | {test_3,test_2,test_1}
 5 | test_5     |                               | {test_4,test_3,test_2,test_1}
(5 rows)

ROLLBACK;
-- make sure that DROP CONSTRAINT works invalidates the cache correctly
BEGIN;
    ALTER TABLE test_2 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_1(id);
    ALTER TABLE test_3 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_2(id);
    ALTER TABLE test_4 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_3(id);
    ALTER TABLE test_5 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_4(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name |     referencing_relations     |     referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4,test_5} |
 2 | test_2     | {test_3,test_4,test_5}        | {test_1}
 3 | test_3     | {test_4,test_5}               | {test_2,test_1}
 4 | test_4     | {test_5}                      | {test_3,test_2,test_1}
 5 | test_5     |                               | {test_4,test_3,test_2,test_1}
(5 rows)

    ALTER TABLE test_3 DROP CONSTRAINT fkey_1;
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
 3 | test_3     | {test_4,test_5}       |
 4 | test_4     | {test_5}              | {test_3}
 5 | test_5     |                       | {test_4,test_3}
(5 rows)

ROLLBACK;
-- make sure that CREATE TABLE invalidates the cache correctly
DROP TABLE test_1, test_2, test_3, test_4, test_5 CASCADE;
BEGIN;
    CREATE TABLE test_1 (id int UNIQUE);
    SELECT create_distributed_Table('test_1', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    CREATE TABLE test_2 (id int UNIQUE, FOREIGN KEY(id) REFERENCES test_1(id));
    SELECT create_distributed_Table('test_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
(2 rows)

    CREATE TABLE test_3 (id int UNIQUE, FOREIGN KEY(id) REFERENCES test_2(id));
    SELECT create_distributed_Table('test_3', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3}       |
 2 | test_2     | {test_3}              | {test_1}
 3 | test_3     |                       | {test_2,test_1}
(3 rows)

    CREATE TABLE test_4 (id int UNIQUE, FOREIGN KEY(id) REFERENCES test_3(id));
    SELECT create_distributed_Table('test_4', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations  |  referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4} |
 2 | test_2     | {test_3,test_4}        | {test_1}
 3 | test_3     | {test_4}               | {test_2,test_1}
 4 | test_4     |                        | {test_3,test_2,test_1}
(4 rows)

    CREATE TABLE test_5 (id int UNIQUE, FOREIGN KEY(id) REFERENCES test_4(id));
    SELECT create_distributed_Table('test_5', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT * FROM referential_integrity_summary;
 n | table_name |     referencing_relations     |     referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4,test_5} |
 2 | test_2     | {test_3,test_4,test_5}        | {test_1}
 3 | test_3     | {test_4,test_5}               | {test_2,test_1}
 4 | test_4     | {test_5}                      | {test_3,test_2,test_1}
 5 | test_5     |                               | {test_4,test_3,test_2,test_1}
(5 rows)

COMMIT;
-- DROP TABLE works expected
-- re-create the constraints
BEGIN;
    ALTER TABLE test_2 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_1(id);
    ALTER TABLE test_3 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_2(id);
    ALTER TABLE test_4 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_3(id);
    ALTER TABLE test_5 ADD CONSTRAINT fkey_1 FOREIGN KEY(id) REFERENCES test_4(id);
    SELECT * FROM referential_integrity_summary;
 n | table_name |     referencing_relations     |     referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2,test_3,test_4,test_5} |
 2 | test_2     | {test_3,test_4,test_5}        | {test_1}
 3 | test_3     | {test_4,test_5}               | {test_2,test_1}
 4 | test_4     | {test_5}                      | {test_3,test_2,test_1}
 5 | test_5     |                               | {test_4,test_3,test_2,test_1}
(5 rows)

    DROP TABLE test_3 CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to constraint test_4_id_fkey on table test_4
drop cascades to constraint fkey_1 on table test_4
    SELECT * FROM referential_integrity_summary;
 n | table_name | referencing_relations | referenced_relations
---------------------------------------------------------------------
 1 | test_1     | {test_2}              |
 2 | test_2     |                       | {test_1}
(2 rows)

ROLLBACK;
-- Test schemas
BEGIN;
    SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
    CREATE SCHEMA fkey_intermediate_schema_1;
    CREATE SCHEMA fkey_intermediate_schema_2;
    SET search_path TO fkey_graph, fkey_intermediate_schema_1, fkey_intermediate_schema_2;
    CREATE TABLE fkey_intermediate_schema_1.test_6(id int PRIMARY KEY);
    SELECT create_distributed_table('fkey_intermediate_schema_1.test_6', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    CREATE TABLE fkey_intermediate_schema_2.test_7(id int PRIMARY KEY REFERENCES fkey_intermediate_schema_1.test_6(id));
    SELECT create_distributed_table('fkey_intermediate_schema_2.test_7','id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    CREATE TABLE fkey_intermediate_schema_1.test_8(id int PRIMARY KEY REFERENCES fkey_intermediate_schema_2.test_7(id));
    SELECT create_distributed_table('fkey_intermediate_schema_1.test_8', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 test_7
 test_8
(2 rows)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 test_8
(1 row)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 test_6
(1 row)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 test_6
 test_7
(2 rows)

    DROP SCHEMA fkey_intermediate_schema_2 CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table test_7
drop cascades to constraint test_8_id_fkey on table test_8
    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

ROLLBACK;
BEGIN;
    SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
    CREATE SCHEMA fkey_intermediate_schema_1;
    CREATE SCHEMA fkey_intermediate_schema_2;
    SET search_path TO fkey_graph, fkey_intermediate_schema_1, fkey_intermediate_schema_2;
    CREATE TABLE fkey_intermediate_schema_1.test_6(id int PRIMARY KEY);
    SELECT create_distributed_table('fkey_intermediate_schema_1.test_6', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    CREATE TABLE fkey_intermediate_schema_2.test_7(id int PRIMARY KEY REFERENCES fkey_intermediate_schema_1.test_6(id));
    SELECT create_distributed_table('fkey_intermediate_schema_2.test_7','id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    CREATE TABLE fkey_intermediate_schema_1.test_8(id int PRIMARY KEY REFERENCES fkey_intermediate_schema_2.test_7(id));
    SELECT create_distributed_table('fkey_intermediate_schema_1.test_8', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 test_7
 test_8
(2 rows)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
 test_8
(1 row)

    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_6'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 test_6
(1 row)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_8'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
 test_6
 test_7
(2 rows)

    DROP SCHEMA fkey_intermediate_schema_1 CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table test_6
drop cascades to constraint test_7_id_fkey on table test_7
drop cascades to table test_8
    SELECT get_referencing_relation_id_list::regclass FROM get_referencing_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referencing_relation_id_list
---------------------------------------------------------------------
(0 rows)

    SELECT get_referenced_relation_id_list::regclass FROM get_referenced_relation_id_list('test_7'::regclass) ORDER BY 1;
 get_referenced_relation_id_list
---------------------------------------------------------------------
(0 rows)

ROLLBACK;
CREATE OR REPLACE FUNCTION get_foreign_key_connected_relations(IN table_name regclass)
RETURNS SETOF RECORD
LANGUAGE C STRICT
AS 'citus', $$get_foreign_key_connected_relations$$;
COMMENT ON FUNCTION get_foreign_key_connected_relations(IN table_name regclass)
IS 'returns relations connected to input relation via a foreign key graph';
CREATE TABLE distributed_table_1(col int unique);
CREATE TABLE distributed_table_2(col int unique);
CREATE TABLE distributed_table_3(col int unique);
CREATE TABLE distributed_table_4(col int unique);
SELECT create_distributed_table('distributed_table_1', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_table('distributed_table_2', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_table('distributed_table_3', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT create_distributed_table('distributed_table_4', 'col');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE reference_table_1(col int unique);
CREATE TABLE reference_table_2(col 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)

-- Now build below foreign key graph:
--
--                               --------------------------------------------
--                               ^                                          |
--                               |                                          v
-- distributed_table_1 <- distributed_table_2 -> reference_table_1 <- reference_table_2
--            |                   ^
--            |                   |
--            ----------> distributed_table_3
ALTER TABLE distributed_table_2 ADD CONSTRAINT fkey_1 FOREIGN KEY (col) REFERENCES distributed_table_1(col);
ALTER TABLE distributed_table_2 ADD CONSTRAINT fkey_2 FOREIGN KEY (col) REFERENCES reference_table_1(col);
ALTER TABLE reference_table_2 ADD CONSTRAINT fkey_3 FOREIGN KEY (col) REFERENCES reference_table_1(col);
ALTER TABLE distributed_table_3 ADD CONSTRAINT fkey_4 FOREIGN KEY (col) REFERENCES distributed_table_2(col);
ALTER TABLE distributed_table_2 ADD CONSTRAINT fkey_5 FOREIGN KEY (col) REFERENCES reference_table_2(col);
ALTER TABLE distributed_table_1 ADD CONSTRAINT fkey_6 FOREIGN KEY (col) REFERENCES distributed_table_3(col);
-- below queries should print all 5 tables mentioned in above graph
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('reference_table_1') AS f(oid oid)
ORDER BY tablename;
      tablename
---------------------------------------------------------------------
 distributed_table_1
 distributed_table_2
 distributed_table_3
 reference_table_1
 reference_table_2
(5 rows)

SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('distributed_table_1') AS f(oid oid)
ORDER BY tablename;
      tablename
---------------------------------------------------------------------
 distributed_table_1
 distributed_table_2
 distributed_table_3
 reference_table_1
 reference_table_2
(5 rows)

-- show that this does not print anything as distributed_table_4
-- is not involved in any foreign key relationship
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('distributed_table_4') AS f(oid oid)
ORDER BY tablename;
 tablename
---------------------------------------------------------------------
(0 rows)

ALTER TABLE distributed_table_4 ADD CONSTRAINT fkey_1 FOREIGN KEY (col) REFERENCES distributed_table_4(col);
-- show that we print table itself as it has a self reference
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('distributed_table_4') AS f(oid oid)
ORDER BY tablename;
      tablename
---------------------------------------------------------------------
 distributed_table_4
(1 row)

CREATE TABLE local_table_1 (col int unique);
CREATE TABLE local_table_2 (col int unique);
-- show that we do not trigger updating foreign key graph when
-- defining/dropping foreign keys between postgres tables
ALTER TABLE local_table_1 ADD CONSTRAINT fkey_1 FOREIGN KEY (col) REFERENCES local_table_2(col);
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('local_table_2') AS f(oid oid)
ORDER BY tablename;
 tablename
---------------------------------------------------------------------
(0 rows)

ALTER TABLE local_table_1 DROP CONSTRAINT fkey_1;
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('local_table_1') AS f(oid oid)
ORDER BY tablename;
 tablename
---------------------------------------------------------------------
(0 rows)

-- show that we error out for non-existent tables
SELECT oid::regclass::text AS tablename
FROM get_foreign_key_connected_relations('non_existent_table') AS f(oid oid)
ORDER BY tablename;
ERROR:  relation "non_existent_table" does not exist
set client_min_messages to error;
SET search_path TO public;
DROP SCHEMA fkey_graph CASCADE;