collocation.sql

-- definition of pg_dist_collocation
CREATE TABLE pg_dist_collocation (
	shardCount INT, 
	replicationFactor INT, 
	collocationId SERIAL, 
	UNIQUE (shardCount, replicationFactor))
;

-- generation of a new collocationid with the given inputs
CREATE FUNCTION getCollocationId(shardCountIn, replicationFactorIn)
		
		SELECT 
			collocationId
		FROM 
			pg_dist_collocation 
		WHERE 
			shardCount = shardCountIn and replicationFactor = replicationFactorIn

-- generation of a new collocationid with the given inputs
CREATE FUNCTION createCollocationId(shardCountIn, replicationFactorIn)
	INSERT INTO
		pg_dist_colocation (shardCount, replicationFactor) VALUES
						   (shardCountIn, replicationFactorIn)
		ON CONFLICT (shardCount, replicationFactor) DO NOTHING
		RETURNING collocationId;	

	RETURN collocationId;

-- add collocationId to pg_dist_partition
ALTER TABLE pg_dist_partition ADD COLUMN collocationId BIGINT;

-- set the shard count
ALTER SYSTEM SET citus.hash_distribution_shard_count = 128;
SELECT pg_reload_conf();

-- set the replication factor
ALTER SYSTEM SET citus.shard_replication_factor = 2;
SELECT pg_reload_conf();

-- a new UDF that creates hash distributed table
-- get shard count from citus.hash_distribution_shard_count GUC
-- get replication factor from citus.shard_replication_factor GUC
SELECT create_hash_distributed_table('tableName', distributionColumn);


-- create some helper functions

--(i) get the collocationId iff we're to create the hash_distributed table now
-- RETURNS INVALID_COLLOCATION_ID(-1) iff there are no applicable collocation id
-- ELSE returns the collocationId from pg_dist_collocation 
SELECT get_current_applicable_collocation_id();

-- (ii) apply the collocationId for the tables that are create before 5.3

-- iterates through all the tables/placements and fills pg_dist_collocation 
-- and pg_dist_partition tables accordingly
SELECT update_collocation_information();