Marco Slot marcocitus
-
Citus Data
- Haarlem
- Sign in to view email
- http://www.citusdata.com
View create-range-shards.sql
CREATE OR REPLACE FUNCTION create_colocated_range_shards(table_name regclass, min_value text, max_value text) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ | |
DECLARE | |
table_colocationid int; | |
group_ids int[]; | |
new_shard_id bigint; | |
current_table text; | |
node record; |
View pubsub-coordinator.sql
/* commands to run on the coordinator */ | |
CREATE EXTENSION citus; | |
SELECT master_add_node('10.0.0.2', 5432); | |
SELECT master_add_node('10.0.0.3', 5432); | |
SELECT start_metadata_sync_to_node(nodename, nodeport) FROM pg_dist_node; | |
SET citus.replication_model TO 'streaming' | |
CREATE TABLE events ( | |
event_id bigserial primary key, |
View replace-pg_table_size.sql
-- Replace the pg_table_size function with one that supports distributed tables | |
BEGIN; | |
ALTER FUNCTION pg_catalog.pg_table_size(regclass) RENAME TO orig_table_size; | |
CREATE OR REPLACE FUNCTION pg_catalog.pg_table_size(table_name regclass) | |
RETURNS bigint | |
LANGUAGE plpgsql | |
AS $function$ | |
BEGIN | |
IF EXISTS (SELECT 1 FROM pg_dist_partition WHERE logicalrelid = table_name) THEN |
View split.sql
CREATE OR REPLACE FUNCTION run_command_on_master_and_workers(p_sql text) | |
RETURNS void LANGUAGE plpgsql AS $function$ | |
BEGIN | |
EXECUTE p_sql; | |
PERFORM run_command_on_workers(p_sql); | |
END; | |
$function$; | |
SELECT run_command_on_master_and_workers($cmd$ | |
CREATE SCHEMA IF NOT EXISTS citus_split_lower |
View example.sql
CREATE TABLE events ( | |
id int not null, | |
created_at timestamptz, | |
payload jsonb | |
); | |
CREATE INDEX ON events (id); | |
CREATE INDEX ON events USING BRIN (created_at); | |
SELECT create_distributed_table('events', 'id'); | |
-- Set up the trigger on workers (idempotent) |
View deinstrument.sql
BEGIN; | |
DROP FUNCTION public.master_append_table_to_shard(bigint,text,text,int); | |
ALTER FUNCTION pg_catalog.real_master_append_table_to_shard(bigint,text,text,int) | |
RENAME TO master_append_table_to_shard; | |
END; |
View master_create_range_shard.sql
CREATE OR REPLACE FUNCTION master_create_range_shard( | |
table_name text, | |
minvalue text, | |
maxvalue text) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ | |
DECLARE | |
new_shard_id bigint; | |
BEGIN |
View partitioning-.sql
-- Functions to partition a table and drop old partitions | |
CREATE SEQUENCE IF NOT EXISTS partition_id_seq; | |
CREATE OR REPLACE FUNCTION partition_create( | |
table_name regclass) | |
RETURNS text | |
LANGUAGE plpgsql | |
AS $function$ |
View citus_tools.sql
CREATE OR REPLACE FUNCTION citus_shard_name(table_name regclass, shard_id bigint) | |
RETURNS text | |
LANGUAGE sql | |
AS $function$ | |
SELECT table_name||'_'||shard_id; | |
$function$; | |
CREATE OR REPLACE FUNCTION citus_shard_name(shard_id bigint) | |
RETURNS text | |
LANGUAGE sql |
View consecutive-users.sql
CREATE OR REPLACE FUNCTION consecutive_date_pattern(start_date date, num_days int) | |
RETURNS text[] | |
AS $BODY$ | |
DECLARE | |
result text[]; | |
BEGIN | |
SELECT array_agg('date=>"'||d::date||'"') INTO result | |
FROM generate_series(start_date, start_date + num_days * interval '1 day', '1 day') d; | |
RETURN result; |