saicitus

SELECT nodename,
rarray[1] blocked_statement,
rarray[2] cur_stmt_blocking_proc,
rarray[3] count,
rarray[4] age
FROM (
SELECT nodename,
regexp_split_to_array(RESULT, '\$') rarray
FROM (
SELECT nodename,unnest(RESULT) RESULT

saicitus

BEGIN;
SET search_path TO 'public';
CREATE SCHEMA temp_schema;
CREATE TABLE temp_schema.deviceid_rollup_daily (LIKE deviceid_rollup_daily INCLUDING ALL);
ALTER TABLE temp_schema.deviceid_rollup_daily ALTER COLUMN state SET DATA TYPE character varying(100);
SELECT create_distributed_table('temp_schema.deviceid_rollup_daily','state');
INSERT INTO temp_schema.deviceid_rollup_daily SELECT * from deviceid_rollup_daily;
END;

-- Move the new table to the actual schema.

saicitus

CREATE AGGREGATE sum(jsonb)(
        SFUNC = topn_union_trans,
        STYPE = internal,
        FINALFUNC = topn_pack
);

CREATE AGGREGATE sum(text)(
        SFUNC = topn_add_trans,
        STYPE = internal,
        FINALFUNC = topn_pack

saicitus

CREATE TABLE events(
tenant_id int,
id int,
type text
);

--- CREATE 2 roles using roles tab as:
-- tenant_1 is the role for tenant_id 1
-- tenant_2 is the role for tenant_id 2

saicitus

-- Replace "<*>" with real names
psql -h <coordinator_host> -p <coordinator_port> -tA -F" " -c "SELECT nodename, nodeport, shardid FROM pg_dist_shard 
JOIN pg_dist_shard_placement USING (shardid) WHERE logicalrelid = '<table_name>'::regclass" | 
xargs -n 3 -P8 sh -c "psql -h \$0 -p \$1 -c \"CLUSTER <table_name>_\$2 USING <table_name>_<index_name>_\$2;\""

saicitus

select relallvisible, relpages from pg_class where relname = '<table_name>';

saicitus

CREATE TABLE test (x varchar, y int);
SELECT create_distributed_table('test', 'x');
BEGIN;
SET LOCAL citus.enable_ddl_propagation TO off;
DELETE FROM pg_depend WHERE objid = 'test'::regclass AND objid = refobjid RETURNING *;
ALTER TABLE test ALTER COLUMN x TYPE text;
SELECT * FROM run_command_on_placements('test', 'ALTER TABLE %I ALTER COLUMN x TYPE text');
UPDATE pg_dist_partition SET partkey = column_name_to_column('test', 'x') WHERE logicalrelid = 'test'::regclass;
COMMIT;

saicitus

-- a reference table 
CREATE TABLE states ( 
code char(2) PRIMARY KEY, 
full_name text NOT NULL, 
general_sales_tax numeric(4,3) 
); 
-- distribute it to all workers 
SELECT create_reference_table('states');

--large_table

saicitus

CREATE OR REPLACE FUNCTION filter (children jsonb[])
RETURNS jsonb[]
AS $$
DECLARE
    i jsonb;
    result jsonb[];
BEGIN
  FOR i IN SELECT * FROM jsonb_array_elements(children)
  LOOP
    IF i.value -> 'name' != 'null'::jsonb THEN

saicitus

SELECT logicalrelid::regclass, partmethod, column_to_column_name(logicalrelid, partkey) FROM pg_dist_partition;