Citus Release Party - Query from any node

script.sql

-- Create a distributed table on the coordinator
CREATE TABLE test_distributed(key bigint PRIMARY KEY, data jsonb);
SELECT create_distributed_table('test_distributed', 'key');

-- connect to any worker and show table is there
psql -h localhost -p 9700 postgres
\d+ test_distributed

-- Insert from one worker,
INSERT INTO test_distributed VALUES (1, '{"value": "100"}');

-- select from another worker
psql -h localhost -p 9701 postgres
SELECT * FROM test_distributed;

-- Show that CREATE VIEW / FUNCTION is propagated to the workers
-- connect back to the coordinator and create some objects
-- like a VIEW or FUNCTION
CREATE VIEW sum_of_values AS 
  SELECT sum((data->>'value')::bigint) FROM test_distributed;

CREATE OR REPLACE FUNCTION double(x bigint) 
returns double precision as $$
    select 2 * x;
$$ language sql;

-- connect back to a worker
psql -h localhost -p 9700 postgres
SELECT * FROM sum_of_values;
SELECT double(sum::bigint) FROM sum_of_values;

- show that coordinator local tables can be accessed from workers

-- make sure that the coordinator is added to the metadata
SELECT citus_set_coordinator_host('localhost', 5432); 


-- create a local table
CREATE TABLE test_coordinator_local(key bigint PRIMARY KEY);
INSERT INTO test_coordinator_local VALUES (1);

-- on the coordinator, you can always join local tables
-- with distributed tables
SELECT count(*) FROM test_coordinator_local JOIN test_distributed USING (key);


-- now, add this local table to Citus metadata 
SELECT citus_add_local_table_to_metadata('test_coordinator_local');


-- and, access the local table from the workers 
SELECT count(*) FROM test_coordinator_local JOIN test_distributed USING (key);


-- if you want all your tables to be added to Citus metadata
ALTER SYSTEM SET citus.use_citus_managed_tables TO ON;


SELECT citus_is_coordinator();
SELECT nodename, nodeport, CASE WHEN groupid = (SELECT groupid FROM pg_dist_local_group) THEN '*' ELSE ' ' END current_node FROM pg_dist_node;