CREATE SCHEMA truncate_from_workers;
SET search_path TO 'truncate_from_workers';
SET citus.next_shard_id TO 2380000;
SET citus.next_placement_id TO 2380000;
SET citus.shard_replication_factor TO 1;
SET citus.shard_count TO 6;
SET citus.replication_model TO streaming;
CREATE TABLE "refer'ence_table"(id int PRIMARY KEY);
SELECT create_reference_table('refer''ence_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)

ALTER TABLE on_update_fkey_table ADD CONSTRAINT fkey FOREIGN KEY(value_1) REFERENCES "refer'ence_table"(id) ON UPDATE CASCADE;
INSERT INTO "refer'ence_table" SELECT i FROM generate_series(0, 100) i;
INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
-- first, make sure that truncate from the coordinator workers as expected
TRUNCATE on_update_fkey_table;
SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

-- fill the table again
INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
-- now, show that TRUNCATE CASCADE works expected from the coordinator
TRUNCATE "refer'ence_table" CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM "refer'ence_table";
 count
---------------------------------------------------------------------
     0
(1 row)

-- load some data for the next tests
INSERT INTO "refer'ence_table" SELECT i FROM generate_series(0, 100) i;
INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
-- make sure that DDLs along with TRUNCATE worker fine
BEGIN;
	ALTER TABLE on_update_fkey_table ADD COLUMN x INT;
	TRUNCATE on_update_fkey_table;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

ROLLBACK;
\c - - - :worker_1_port
SET search_path TO 'truncate_from_workers';
-- make sure that TRUNCATE workes expected from the worker node
TRUNCATE on_update_fkey_table;
SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

-- load some data
INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
-- now, show that TRUNCATE CASCADE works expected from the worker
TRUNCATE "refer'ence_table" CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM "refer'ence_table";
 count
---------------------------------------------------------------------
     0
(1 row)

-- test within transaction blocks
BEGIN;
	TRUNCATE on_update_fkey_table;
ROLLBACK;
-- test within transaction blocks
BEGIN;
	TRUNCATE "refer'ence_table" CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
ROLLBACK;
-- test with sequential mode and CASCADE
BEGIN;
	SET LOCAL citus.multi_shard_modify_mode TO sequential;
	TRUNCATE on_update_fkey_table;
	TRUNCATE "refer'ence_table" CASCADE;
NOTICE:  truncate cascades to table "on_update_fkey_table"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
NOTICE:  truncate cascades to table "on_update_fkey_table_xxxxxxx"
ROLLBACK;
-- fill some data for the next test
\c - - - :master_port
SET search_path TO 'truncate_from_workers';
INSERT INTO "refer'ence_table" SELECT i FROM generate_series(0, 100) i;
\c - - - :worker_1_port
SET search_path TO 'truncate_from_workers';
-- make sure that DMLs-SELECTs works along with TRUNCATE worker fine
BEGIN;
	-- we can enable local execution when truncate can be executed locally.
	SET citus.enable_local_execution = 'off';
	INSERT INTO on_update_fkey_table SELECT i, i % 100  FROM generate_series(0, 1000) i;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
  1001
(1 row)

	TRUNCATE on_update_fkey_table;
	SELECT count(*) FROM on_update_fkey_table;
 count
---------------------------------------------------------------------
     0
(1 row)

ROLLBACK;
RESET client_min_messages;
\c - - - :master_port
-- also test the infrastructure that is used for supporting
-- TRUNCATE from worker nodes
-- should fail since it is not in transaction block
SELECT lock_relation_if_exists('on_update_fkey_table', 'ACCESS SHARE');
ERROR:  lock_relation_if_exists can only be used in transaction blocks
BEGIN;
	-- should fail since the schema is not provided
	SELECT lock_relation_if_exists('on_update_fkey_table', 'ACCESS SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 f
(1 row)

ROLLBACK;
BEGIN;
	-- should work since the schema is in the search path
	SET search_path TO 'truncate_from_workers';
	SELECT lock_relation_if_exists('on_update_fkey_table', 'ACCESS SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

ROLLBACK;
BEGIN;
	-- should return false since there is no such table
	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_tableXXX', 'ACCESS SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 f
(1 row)

ROLLBACK;
BEGIN;
	-- should fail since there is no such lock mode
	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'MY LOCK MODE');
ERROR:  unknown lock mode: MY LOCK MODE
ROLLBACK;
BEGIN;
	-- test all lock levels
	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'ACCESS SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'ROW SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'ROW EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'SHARE UPDATE EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'SHARE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'SHARE ROW EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'SHARE ROW EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	SELECT lock_relation_if_exists('truncate_from_workers.on_update_fkey_table', 'ACCESS EXCLUSIVE');
 lock_relation_if_exists
---------------------------------------------------------------------
 t
(1 row)

	-- see them all
	SELECT relation::regclass, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation = 'truncate_from_workers.on_update_fkey_table'::regclass ORDER BY 2 DESC;
                  relation                  |           mode
---------------------------------------------------------------------
 truncate_from_workers.on_update_fkey_table | ShareUpdateExclusiveLock
 truncate_from_workers.on_update_fkey_table | ShareRowExclusiveLock
 truncate_from_workers.on_update_fkey_table | ShareLock
 truncate_from_workers.on_update_fkey_table | RowShareLock
 truncate_from_workers.on_update_fkey_table | RowExclusiveLock
 truncate_from_workers.on_update_fkey_table | ExclusiveLock
 truncate_from_workers.on_update_fkey_table | AccessShareLock
 truncate_from_workers.on_update_fkey_table | AccessExclusiveLock
(8 rows)

COMMIT;
DROP SCHEMA truncate_from_workers CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table truncate_from_workers."refer'ence_table"
drop cascades to table truncate_from_workers.on_update_fkey_table
SET search_path TO public;