--
-- Tests multiple commands in transactions where
-- there is foreign key relation between reference
-- tables and distributed tables
--
CREATE SCHEMA test_fkey_to_ref_in_tx;
SET search_path TO 'test_fkey_to_ref_in_tx';
SET citus.next_shard_id TO 2380000;
SET citus.next_placement_id TO 2380000;
SET citus.shard_replication_factor TO 1;
CREATE TABLE transitive_reference_table(id int PRIMARY KEY);
SELECT create_reference_table('transitive_reference_table');
 create_reference_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE reference_table(id int PRIMARY KEY, value_1 int);
SELECT create_reference_table('reference_table');
 create_reference_table
---------------------------------------------------------------------

(1 row)

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

(1 row)

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

(1 row)

ALTER TABLE on_update_fkey_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES reference_table(id) ON UPDATE CASCADE;
ALTER TABLE reference_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES transitive_reference_table(id) ON UPDATE CASCADE;
INSERT INTO transitive_reference_table SELECT i FROM generate_series(0, 100) i;
INSERT INTO reference_table SELECT i, i FROM generate_series(0, 100) i;
INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
INSERT INTO unrelated_dist_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
-- in order to see when the mode automatically swithces to sequential execution
SET client_min_messages TO DEBUG1;
-- case 1.1: SELECT to a reference table is followed by a parallel SELECT to a distributed table
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
-- case 1.2: SELECT to a reference table is followed by a multiple router SELECTs to a distributed table
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 15;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 16;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 17;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 18;
 count
---------------------------------------------------------------------
     1
(1 row)

ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 15;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 16;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 17;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE id = 18;
 count
---------------------------------------------------------------------
     1
(1 row)

ROLLBACK;
-- case 1.3: SELECT to a reference table is followed by a multi-shard UPDATE to a distributed table
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
ROLLBACK;
-- case 1.4: SELECT to a reference table is followed by a multiple sing-shard UPDATE to a distributed table
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18;
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 15;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 16;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 17;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE id = 18;
ROLLBACK;
-- case 1.5: SELECT to a reference table is followed by a DDL that touches fkey column
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
-- case 1.6: SELECT to a reference table is followed by an unrelated DDL
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ADD COLUMN X INT;
DEBUG:  switching to sequential query execution mode
DETAIL:  cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "reference_table" because there is a foreign key between them and "reference_table" has been accessed in this transaction
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ADD COLUMN X INT;
DEBUG:  switching to sequential query execution mode
DETAIL:  cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "transitive_reference_table" because there is a foreign key between them and "transitive_reference_table" has been accessed in this transaction
ROLLBACK;
-- case 1.7.1: SELECT to a reference table is followed by a DDL that is on
-- the foreign key column
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE;
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE;
ROLLBACK;
-- case 1.7.2: SELECT to a reference table is followed by a DDL that is on
-- the foreign key column after a parallel query has been executed
BEGIN;
	SELECT count(*) FROM unrelated_dist_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE;
ERROR:  cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM unrelated_dist_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table DROP COLUMN value_1 CASCADE;
ERROR:  cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 1.7.3: SELECT to a reference table is followed by a DDL that is not on
-- the foreign key column, and a parallel query has already been executed
BEGIN;
	SELECT count(*) FROM unrelated_dist_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ADD COLUMN X INT;
ERROR:  cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "reference_table" because there is a foreign key between them and "reference_table" has been accessed in this transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM unrelated_dist_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	ALTER TABLE on_update_fkey_table ADD COLUMN X INT;
ERROR:  cannot execute parallel DDL on table "on_update_fkey_table" after SELECT command on reference table "transitive_reference_table" because there is a foreign key between them and "transitive_reference_table" has been accessed in this transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 1.8: SELECT to a reference table is followed by a COPY
BEGIN;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	COPY on_update_fkey_table FROM STDIN WITH CSV;
ROLLBACK;
BEGIN;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

	COPY on_update_fkey_table FROM STDIN WITH CSV;
ROLLBACK;
-- case 2.1: UPDATE to a reference table is followed by a multi-shard SELECT
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
     0
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101;
 count
---------------------------------------------------------------------
    10
(1 row)

ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101;
 count
---------------------------------------------------------------------
     0
(1 row)

ROLLBACK;
-- case 2.2: UPDATE to a reference table is followed by multiple router SELECT
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299;
 count
---------------------------------------------------------------------
     1
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399;
 count
---------------------------------------------------------------------
     1
(1 row)

ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 99;
 count
---------------------------------------------------------------------
     0
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 199;
 count
---------------------------------------------------------------------
     0
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 299;
 count
---------------------------------------------------------------------
     0
(1 row)

	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 101 AND id = 399;
 count
---------------------------------------------------------------------
     0
(1 row)

ROLLBACK;
-- case 2.3: UPDATE to a reference table is followed by a multi-shard UPDATE
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 15;
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 15;
ROLLBACK;
-- case 2.4: UPDATE to a reference table is followed by multiple router UPDATEs
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1;
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2;
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3;
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4;
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 1;
ERROR:  insert or update on table "on_update_fkey_table_xxxxxxx" violates foreign key constraint "fkey_xxxxxxx"
DETAIL:  Key (value_1)=(101) is not present in table "reference_table_2380001". 
CONTEXT:  while executing command on localhost:xxxxx
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 2;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 3;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	UPDATE on_update_fkey_table SET value_1 = 101 WHERE id = 4;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- case 2.5: UPDATE to a reference table is followed by a DDL that touches fkey column
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE bigint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
-- case 2.6: UPDATE to a reference table is followed by an unrelated DDL
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT;
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	ALTER TABLE on_update_fkey_table ADD COLUMN value_1_X INT;
ROLLBACK;
-- case 2.7: UPDATE to a reference table is followed by COPY
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	COPY on_update_fkey_table FROM STDIN WITH CSV;
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	COPY on_update_fkey_table FROM STDIN WITH CSV;
ERROR:  insert or update on table "on_update_fkey_table_xxxxxxx" violates foreign key constraint "fkey_xxxxxxx"
DETAIL:  Key (value_1)=(101) is not present in table "reference_table_2380001". 
ROLLBACK;
-- case 2.8: UPDATE to a reference table is followed by TRUNCATE
BEGIN;
	UPDATE reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	TRUNCATE on_update_fkey_table;
ROLLBACK;
BEGIN;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	TRUNCATE on_update_fkey_table;
ROLLBACK;
-- case 3.1: an unrelated DDL to a reference table is followed by a real-time SELECT
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
-- case 3.2: DDL that touches fkey column to a reference table is followed by a real-time SELECT
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE int;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE int;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

ROLLBACK;
-- case 3.3: DDL to a reference table followed by a multi shard UPDATE
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
ROLLBACK;
-- case 3.4: DDL to a reference table followed by multiple router UPDATEs
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4;
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DEFAULT 1001;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 1;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 2;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 3;
	UPDATE on_update_fkey_table SET value_1 = 98 WHERE id = 4;
ROLLBACK;
-- case 3.5: DDL to reference table followed by a DDL to dist table
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "reference_table"
DEBUG:  validating foreign key constraint "fkey"
	CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1);
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "transitive_reference_table"
DEBUG:  validating foreign key constraint "fkey"
	CREATE INDEX fkey_test_index_1 ON on_update_fkey_table(value_1);
ROLLBACK;
-- case 4.6: DDL to reference table followed by a DDL to dist table, both touching fkey columns
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "reference_table"
DEBUG:  validating foreign key constraint "fkey"
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "transitive_reference_table"
DEBUG:  validating foreign key constraint "fkey"
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint;
DEBUG:  rewriting table "on_update_fkey_table"
DEBUG:  validating foreign key constraint "fkey"
ROLLBACK;
-- case 3.7: DDL to a reference table is followed by COPY
BEGIN;
	ALTER TABLE reference_table  ADD COLUMN X int;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	COPY on_update_fkey_table FROM STDIN WITH CSV;
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table  ADD COLUMN X int;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	COPY on_update_fkey_table FROM STDIN WITH CSV;
ROLLBACK;
-- case 3.8: DDL to a reference table is followed by TRUNCATE
BEGIN;
	ALTER TABLE reference_table  ADD COLUMN X int;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	TRUNCATE on_update_fkey_table;
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table  ADD COLUMN X int;
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "transitive_reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
	TRUNCATE on_update_fkey_table;
ROLLBACK;
-- case 3.9: DDL to a reference table is followed by TRUNCATE
BEGIN;
	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "reference_table"
DEBUG:  validating foreign key constraint "fkey"
	TRUNCATE on_update_fkey_table;
ROLLBACK;
BEGIN;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "transitive_reference_table"
DEBUG:  validating foreign key constraint "fkey"
	TRUNCATE on_update_fkey_table;
ROLLBACK;
---------------------------------------------------------------------
--- Now, start testing the other way araound
---------------------------------------------------------------------
-- case 4.1: SELECT to a dist table is follwed by a SELECT to a reference table
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

ROLLBACK;
-- case 4.2: SELECT to a dist table is follwed by a DML to a reference table
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	UPDATE reference_table SET id = 101 WHERE id = 99;
ERROR:  cannot modify table "reference_table" because there was a parallel operation on a distributed table
DETAIL:  When there is a foreign key to a reference table or to a citus local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
ERROR:  cannot modify table "transitive_reference_table" because there was a parallel operation on a distributed table
DETAIL:  When there is a foreign key to a reference table or to a citus local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 4.3: SELECT to a dist table is follwed by an unrelated DDL to a reference table
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	ALTER TABLE reference_table ADD COLUMN X INT;
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	ALTER TABLE transitive_reference_table ADD COLUMN X INT;
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 4.4: SELECT to a dist table is follwed by a DDL to a reference table
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "reference_table"
DEBUG:  validating foreign key constraint "fkey"
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint;
DEBUG:  rewriting table "transitive_reference_table"
DEBUG:  validating foreign key constraint "fkey"
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 4.5: SELECT to a dist table is follwed by a TRUNCATE
\set VERBOSITY terse
SET client_min_messages to LOG;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	TRUNCATE reference_table CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE value_1 = 99;
 count
---------------------------------------------------------------------
    10
(1 row)

	TRUNCATE transitive_reference_table CASCADE;
NOTICE:  truncate cascades to table "reference_table"
NOTICE:  truncate cascades to table "on_update_fkey_table"
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
ROLLBACK;
-- case 4.6: Router SELECT to a dist table is followed by a TRUNCATE
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE id = 9;
 count
---------------------------------------------------------------------
     1
(1 row)

	TRUNCATE reference_table CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
ROLLBACK;
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE id = 9;
 count
---------------------------------------------------------------------
     1
(1 row)

	TRUNCATE transitive_reference_table CASCADE;
NOTICE:  truncate cascades to table "reference_table"
NOTICE:  truncate cascades to table "on_update_fkey_table"
ROLLBACK;
-- case 4.7: SELECT to a dist table is followed by a DROP
-- DROP following SELECT is important as we error out after
-- the standart process utility hook drops the table. 
-- That could cause SIGSEGV before the patch. 
-- Below block should "successfully" error out
BEGIN;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	DROP TABLE reference_table CASCADE;
NOTICE:  drop cascades to constraint fkey on table on_update_fkey_table
ERROR:  cannot execute DDL on table because there was a parallel SELECT access to distributed table "on_update_fkey_table" in the same transaction
ROLLBACK;
-- case 4.8: Router SELECT to a dist table is followed by a TRUNCATE
-- No errors expected from below block as SELECT there is a router
-- query
BEGIN;
	SELECT count(*) FROM on_update_fkey_table WHERE id = 9;
 count
---------------------------------------------------------------------
     1
(1 row)

	DROP TABLE reference_table CASCADE;
NOTICE:  drop cascades to constraint fkey on table on_update_fkey_table
ROLLBACK;
RESET client_min_messages;
\set VERBOSITY default
-- case 5.1: Parallel UPDATE on distributed table follow by a SELECT
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	SELECT count(*) FROM reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

ROLLBACK;
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	SELECT count(*) FROM transitive_reference_table;
 count
---------------------------------------------------------------------
   101
(1 row)

ROLLBACK;
-- case 5.2: Parallel UPDATE on distributed table follow by a UPDATE
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	UPDATE reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	UPDATE transitive_reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *)
    SELECT * FROM cte;
 id  | value_1
---------------------------------------------------------------------
  15 |      16
 115 |      16
 215 |      16
 315 |      16
 415 |      16
 515 |      16
 615 |      16
 715 |      16
 815 |      16
 915 |      16
(10 rows)

	UPDATE reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	WITH cte AS (UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15 RETURNING *)
    SELECT * FROM cte;
 id  | value_1
---------------------------------------------------------------------
  15 |      16
 115 |      16
 215 |      16
 315 |      16
 415 |      16
 515 |      16
 615 |      16
 715 |      16
 815 |      16
 915 |      16
(10 rows)

	UPDATE transitive_reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 5.3: Parallel UPDATE on distributed table follow by an unrelated DDL on reference table
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	ALTER TABLE reference_table ADD COLUMN X INT;
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	ALTER TABLE transitive_reference_table ADD COLUMN X INT;
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 5.4: Parallel UPDATE on distributed table follow by a related DDL on reference table
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	ALTER TABLE reference_table ALTER COLUMN id SET DATA TYPE smallint;
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 16 WHERE value_1 = 15;
	ALTER TABLE transitive_reference_table ALTER COLUMN id SET DATA TYPE smallint;
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 6:1: Unrelated parallel DDL on distributed table followed by SELECT on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	SELECT count(*) FROM reference_table;
ERROR:  cannot execute SELECT on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	SELECT count(*) FROM transitive_reference_table;
ERROR:  cannot execute SELECT on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 6:2: Related parallel DDL on distributed table followed by SELECT on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint;
	UPDATE reference_table SET id = 160 WHERE id = 15;
ROLLBACK;
BEGIN;
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint;
	UPDATE transitive_reference_table SET id = 160 WHERE id = 15;
ROLLBACK;
-- case 6:3: Unrelated parallel DDL on distributed table followed by UPDATE on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	SELECT count(*) FROM reference_table;
ERROR:  cannot execute SELECT on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	SELECT count(*) FROM transitive_reference_table;
ERROR:  cannot execute SELECT on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 6:4: Related parallel DDL on distributed table followed by SELECT on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	UPDATE reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	UPDATE transitive_reference_table SET id = 160 WHERE id = 15;
ERROR:  cannot execute DML on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 6:5: Unrelated parallel DDL on distributed table followed by unrelated DDL on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	ALTER TABLE reference_table ADD COLUMN X int;
ERROR:  cannot execute DDL on table "reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	ALTER TABLE transitive_reference_table ADD COLUMN X int;
ERROR:  cannot execute DDL on table "transitive_reference_table" because there was a parallel DDL access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- case 6:6: Unrelated parallel DDL on distributed table followed by related DDL on ref.  table
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN X int;
	ALTER TABLE on_update_fkey_table ALTER COLUMN value_1 SET DATA TYPE smallint;
ERROR:  cannot modify table "on_update_fkey_table" because there was a parallel operation on a distributed table in the transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- some more extensive tests
-- UPDATE on dist table is followed by DELETE to reference table
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
	DELETE FROM reference_table  WHERE id = 99;
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
	DELETE FROM transitive_reference_table  WHERE id = 99;
ERROR:  cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- an unrelated update followed by update on dist table and update
-- on reference table
BEGIN;
	UPDATE unrelated_dist_table SET value_1 = 15;
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
	UPDATE reference_table SET id = 101 WHERE id = 99;
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
BEGIN;
	UPDATE unrelated_dist_table SET value_1 = 15;
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
	UPDATE transitive_reference_table SET id = 101 WHERE id = 99;
ERROR:  cannot execute DML on table "transitive_reference_table" because there was a parallel DML access to distributed table "on_update_fkey_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
ROLLBACK;
-- an unrelated update followed by update on the reference table and update
-- on the cascading distributed table
-- note that the UPDATE on the reference table will try to set the execution
-- mode to sequential, which will fail since there is an already opened
-- parallel connections
BEGIN;
	UPDATE unrelated_dist_table SET value_1 = 15;
	UPDATE reference_table SET id = 101 WHERE id = 99;
ERROR:  cannot modify table "reference_table" because there was a parallel operation on a distributed table
DETAIL:  When there is a foreign key to a reference table or to a citus local table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
	UPDATE on_update_fkey_table SET value_1 = 5 WHERE id ! = 11;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
BEGIN;
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id));
	SELECT create_distributed_table('test_table_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	DROP TABLE test_table_1 CASCADE;
ROLLBACK;
-- the fails since we're trying to switch sequential mode after
-- already executed a parallel query
BEGIN;
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id));
	SELECT create_distributed_table('tt4', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id), FOREIGN KEY(id) REFERENCES tt4(id));
	SELECT create_distributed_table('test_table_2', 'id');
ERROR:  cannot distribute relation "test_table_2" in this transaction because it has a foreign key to a reference table
DETAIL:  If a hash distributed table has a foreign key to a reference table, it has to be created in sequential mode before any parallel commands have been executed in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_1 CASCADE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- same test with the above, but this time using
-- sequential mode, succeeds
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE tt4(id int PRIMARY KEY, value_1 int, FOREIGN KEY(id) REFERENCES tt4(id));
	SELECT create_distributed_table('tt4', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id), FOREIGN KEY(id) REFERENCES tt4(id));
	SELECT create_distributed_table('test_table_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	DROP TABLE test_table_1 CASCADE;
ROLLBACK;
-- another test with ALTER TABLE fails since we're already opened
-- parallel connection via create_distributed_table(), later
-- adding foreign key to reference table fails
BEGIN;
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

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

(1 row)

	ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id);
ERROR:  cannot modify table "test_table_2" because there was a parallel operation on a distributed table in the transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_1, test_table_2;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
-- same test with the above on sequential mode should work fine
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

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

(1 row)

	ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id);
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	DROP TABLE test_table_1, test_table_2;
COMMIT;
-- similar test with the above, but this time the order of
-- create_distributed_table and create_reference_table is
-- changed
BEGIN;
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int);
	SELECT create_distributed_table('test_table_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id);
ERROR:  cannot modify table "test_table_2" because there was a parallel operation on a distributed table in the transaction
DETAIL:  When there is a foreign key to a reference table, Citus needs to perform all operations over a single connection per node to ensure consistency. 
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_1 CASCADE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- same test in sequential mode should succeed
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int);
	SELECT create_distributed_table('test_table_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	CREATE TABLE test_table_1(id int PRIMARY KEY);
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id);
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	DROP TABLE test_table_1 CASCADE;
ROLLBACK;
-- again a very similar test, but this time
-- a parallel SELECT is already executed before
-- setting the mode to sequential should fail
BEGIN;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int);
	SELECT create_distributed_table('test_table_2', 'id');
ERROR:  cannot distribute "test_table_2" in sequential mode because a parallel query was executed in this transaction
HINT:  If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option. 
	CREATE TABLE test_table_1(id int PRIMARY KEY);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	SELECT create_reference_table('test_table_1');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	ALTER TABLE test_table_2 ADD CONSTRAINT c_check FOREIGN KEY (value_1) REFERENCES test_table_1(id);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_1 CASCADE;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- make sure that we cannot create hash distributed tables with
-- foreign keys to reference tables when they have data in it
BEGIN;
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i;
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id));
	INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i;
	SELECT create_reference_table('test_table_1');
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk. 
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$test_fkey_to_ref_in_tx.test_table_1$$)
 create_reference_table
---------------------------------------------------------------------

(1 row)

	SELECT create_distributed_table('test_table_2', 'id');
ERROR:  cannot distribute "test_table_2" in sequential mode because it is not empty
HINT:  If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option.  If that is not the case, try distributing local tables when they are empty. 
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_2, test_table_1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
-- the same test with above in sequential mode would still not work
-- since COPY cannot be executed in sequential mode
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	INSERT INTO test_table_1 SELECT i FROM generate_series(0,100) i;
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id));
	INSERT INTO test_table_2 SELECT i, i FROM generate_series(0,100) i;
	SELECT create_reference_table('test_table_1');
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk. 
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$test_fkey_to_ref_in_tx.test_table_1$$)
 create_reference_table
---------------------------------------------------------------------

(1 row)

	SELECT create_distributed_table('test_table_2', 'id');
ERROR:  cannot distribute "test_table_2" in sequential mode because it is not empty
HINT:  If you have manually set citus.multi_shard_modify_mode to 'sequential', try with 'parallel' option.  If that is not the case, try distributing local tables when they are empty. 
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
	DROP TABLE test_table_2, test_table_1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
COMMIT;
-- we should be able to execute and DML/DDL/SELECT after we've
-- switched to sequential via create_distributed_table
BEGIN;
	CREATE TABLE test_table_1(id int PRIMARY KEY);
	CREATE TABLE test_table_2(id int PRIMARY KEY, value_1 int, FOREIGN KEY(value_1) REFERENCES test_table_1(id));
	SELECT create_reference_table('test_table_1');
 create_reference_table
---------------------------------------------------------------------

(1 row)

	SELECT create_distributed_table('test_table_2', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

	-- and maybe some other test
	CREATE INDEX i1 ON test_table_1(id);
	ALTER TABLE test_table_2 ADD CONSTRAINT check_val CHECK (id > 0);
	SELECT count(*) FROM test_table_2;
 count
---------------------------------------------------------------------
     0
(1 row)

	SELECT count(*) FROM test_table_1;
 count
---------------------------------------------------------------------
     0
(1 row)

	UPDATE test_table_2 SET value_1 = 15;
	-- make sure that the output isn't too verbose
 	SET LOCAL client_min_messages TO ERROR;
	DROP TABLE test_table_2, test_table_1;
COMMIT;
SET client_min_messages TO ERROR;
DROP TABLE reference_table CASCADE;
SET client_min_messages TO DEBUG1;
-- make sure that modifications to reference tables in a CTE can
-- set the mode to sequential for the next operations
CREATE TABLE reference_table(id int PRIMARY KEY);
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "reference_table_pkey" for table "reference_table"
SELECT create_reference_table('reference_table');
 create_reference_table
---------------------------------------------------------------------

(1 row)

CREATE TABLE distributed_table(id int PRIMARY KEY, value_1 int);
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "distributed_table_pkey" for table "distributed_table"
SELECT create_distributed_table('distributed_table', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

ALTER TABLE
	distributed_table
ADD CONSTRAINT
	fkey_delete FOREIGN KEY(value_1)
REFERENCES
	reference_table(id) ON DELETE CASCADE;
INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i;
DEBUG:  distributed INSERT ... SELECT can only select from distributed tables
DEBUG:  Collecting INSERT ... SELECT results on coordinator
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
INSERT INTO distributed_table SELECT i, i % 10  FROM generate_series(0, 100) i;
DEBUG:  distributed INSERT ... SELECT can only select from distributed tables
DEBUG:  Collecting INSERT ... SELECT results on coordinator
-- this query returns 100 rows in Postgres, but not in Citus
-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion
WITH t1 AS (DELETE FROM reference_table RETURNING id)
	DELETE FROM distributed_table USING t1 WHERE value_1 = t1.id RETURNING *;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.distributed_table USING (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1 WHERE (distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) RETURNING distributed_table.id, distributed_table.value_1, t1.id
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
 id | value_1 | id
---------------------------------------------------------------------
(0 rows)

-- load some more data for one more test with real-time selects
INSERT INTO reference_table SELECT i FROM generate_series(0, 10) i;
DEBUG:  distributed INSERT ... SELECT can only select from distributed tables
DEBUG:  Collecting INSERT ... SELECT results on coordinator
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
INSERT INTO distributed_table SELECT i, i % 10  FROM generate_series(0, 100) i;
DEBUG:  distributed INSERT ... SELECT can only select from distributed tables
DEBUG:  Collecting INSERT ... SELECT results on coordinator
-- this query returns 100 rows in Postgres, but not in Citus
-- see https://github.com/citusdata/citus_docs/issues/664 for the discussion
WITH t1 AS (DELETE FROM reference_table RETURNING id)
	SELECT count(*) FROM distributed_table, t1 WHERE  value_1 = t1.id;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1 WHERE (distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id)
DEBUG:  switching to sequential query execution mode
DETAIL:  Table "reference_table" is modified, which might lead to data inconsistencies or distributed deadlocks via parallel accesses to hash distributed tables due to foreign keys.  Any parallel modification to those hash distributed tables in the same transaction can only be executed in sequential query execution mode
 count
---------------------------------------------------------------------
     0
(1 row)

-- this query should fail since we first to a parallel access to a distributed table
-- with t1, and then access to t2
WITH t1 AS (DELETE FROM distributed_table RETURNING id),
	t2 AS (DELETE FROM reference_table RETURNING id)
	SELECT count(*) FROM distributed_table, t1, t2 WHERE  value_1 = t1.id AND value_1 = t2.id;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id
DEBUG:  generating subplan XXX_2 for CTE t2: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t2 WHERE ((distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) AND (distributed_table.value_1 OPERATOR(pg_catalog.=) t2.id))
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "distributed_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
-- similarly this should fail since we first access to a distributed
-- table via t1, and then access to the reference table in the main query
WITH t1 AS (DELETE FROM distributed_table RETURNING id)
	DELETE FROM reference_table RETURNING id;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
ERROR:  cannot execute DML on table "reference_table" because there was a parallel DML access to distributed table "distributed_table" in the same transaction
HINT:  Try re-running the transaction with "SET LOCAL citus.multi_shard_modify_mode TO 'sequential';"
-- finally, make sure that we can execute the same queries
-- in the sequential mode
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	WITH t1 AS (DELETE FROM distributed_table RETURNING id),
		t2 AS (DELETE FROM reference_table RETURNING id)
		SELECT count(*) FROM distributed_table, t1, t2 WHERE  value_1 = t1.id AND value_1 = t2.id;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id
DEBUG:  generating subplan XXX_2 for CTE t2: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: SELECT count(*) AS count FROM test_fkey_to_ref_in_tx.distributed_table, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t1, (SELECT intermediate_result.id FROM read_intermediate_result('XXX_2'::text, 'binary'::citus_copy_format) intermediate_result(id integer)) t2 WHERE ((distributed_table.value_1 OPERATOR(pg_catalog.=) t1.id) AND (distributed_table.value_1 OPERATOR(pg_catalog.=) t2.id))
 count
---------------------------------------------------------------------
     0
(1 row)

ROLLBACK;
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO 'sequential';
	WITH t1 AS (DELETE FROM distributed_table RETURNING id)
		DELETE FROM reference_table RETURNING id;
DEBUG:  generating subplan XXX_1 for CTE t1: DELETE FROM test_fkey_to_ref_in_tx.distributed_table RETURNING id
DEBUG:  Plan XXX query after replacing subqueries and CTEs: DELETE FROM test_fkey_to_ref_in_tx.reference_table RETURNING id
 id
---------------------------------------------------------------------
(0 rows)

ROLLBACK;
RESET client_min_messages;
\set VERBOSITY terse
DROP SCHEMA test_fkey_to_ref_in_tx CASCADE;
NOTICE:  drop cascades to 5 other objects
\set VERBOSITY default
SET search_path TO public;
