Skip to content

Instantly share code, notes, and snippets.

Avatar

Marco Slot marcocitus

View GitHub Profile
@marcocitus
marcocitus / hammerdb.sql
Last active Oct 7, 2019
HammerDB Function Schema
View hammerdb.sql
CREATE TABLE public.customer (c_id numeric(5,0) NOT NULL, c_d_id numeric(2,0) NOT NULL, c_w_id numeric(4,0) NOT NULL, c_first character varying(16), c_middle character(2), c_last character varying(16), c_street_1 character varying(20), c_street_2 character varying(20), c_city character varying(20), c_state character(2), c_zip character(9), c_phone character(16), c_since timestamp without time zone, c_credit character(2), c_credit_lim numeric(12,2), c_discount numeric(4,4), c_balance numeric(12,2), c_ytd_payment numeric(12,2), c_payment_cnt numeric(8,0), c_delivery_cnt numeric(8,0), c_data character varying(500)) WITH (fillfactor='50');
CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id) TABLESPACE pg_default ;
ALTER TABLE public.customer ADD CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id);
CREATE TABLE public.district (d_id numeric(2,0) NOT NULL, d_w_id numeric(4,0) NOT NULL, d_ytd numeric(12,2), d_tax numeric(4,4), d_next_o_id numeric, d_nam
@marcocitus
marcocitus / pg_partman.sql
Last active Mar 16, 2019
Setting up pg_partman on cloud
View pg_partman.sql
-- as superuser:
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO citus;
CREATE SCHEMA partman;
GRANT USAGE ON SCHEMA partman TO citus;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
GRANT ALL ON TABLE partman.part_config TO citus;
GRANT ALL ON TABLE partman.part_config_sub TO citus;
@marcocitus
marcocitus / load_github_data.sql
Last active Mar 12, 2019
Load data from GitHub in postgres
View load_github_data.sql
CREATE OR REPLACE FUNCTION public.load_github_data(events_date date, hour integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
CREATE TEMPORARY TABLE input (data jsonb) ON COMMIT DROP;
EXECUTE format($$COPY input FROM PROGRAM 'curl -s http://data.githubarchive.org/%s-%s.json.gz | zcat | grep -v \\u0000' CSV QUOTE e'\x01' DELIMITER e'\x02'$$, events_date, hour);
INSERT INTO github.events SELECT
(data->>'id')::bigint AS event_id,
View citus-cloud-create-dev.sh
#!/bin/sh
name=Marco
formation_name=${1:-test}
formations_url=https://console.citusdata.com/api/v1/formations
api_token=$(cat $HOME/.cituscloud/token)
formation_dir=$HOME/.cituscloud/formations/$formation_name/
worker_count=2
function log {
@marcocitus
marcocitus / citus-fdw.sql
Created Jul 11, 2018
Creating a distributed postgres_fdw table
View citus-fdw.sql
/* add the user mappings from the distributed table to the shards */
CREATE OR REPLACE FUNCTION bind_shards_to_remote_shards(table_name regclass)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
foreign_server text;
fdw_options text[];
user_mapping_options text[];
server_options text[];
@marcocitus
marcocitus / repair-placements.sql
Last active Apr 1, 2018
Repair all shard placements in shardstate 3
View repair-placements.sql
CREATE OR REPLACE FUNCTION public.repair_all()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM
master_copy_shard_placement(shardid, h.nodename, h.nodeport, u.nodename, u.nodeport)
FROM
(SELECT shardid, nodename, nodeport FROM pg_dist_shard_placement WHERE shardstate = 3) u
JOIN
@marcocitus
marcocitus / drain_node.sql
Last active Mar 22, 2018
Functions to drain and remove a node in Citus Enterprise
View drain_node.sql
CREATE OR REPLACE FUNCTION public.drain_node(remove_host text, remove_port int DEFAULT 5432, shard_transfer_mode citus.shard_transfer_mode DEFAULT 'auto'::citus.shard_transfer_mode)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
coordinator_name text;
coordinator_port int;
candidate_count int;
candidate_hosts text[];
candidate_ports int[];
@marcocitus
marcocitus / create-range-shards.sql
Created Nov 6, 2017
Create co-located shards for range-distributed tables
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;
@marcocitus
marcocitus / alter-citusdb-table.sh
Last active Sep 19, 2017
Shell commands to ALTER a CitusDB distributed table
View alter-citusdb-table.sh
TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"
/opt/citusdb/3.0/bin/psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
/opt/citusdb/3.0/bin/psql -tA -F" " -h localhost -d postgres -c "select relname, s.shardid, p.nodename, p.nodeport from pg_class c, pg_dist_shard s, pg_dist_shard_placement p where s.logicalrelid = c.oid and s.shardid = p.shardid and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c '/opt/citusdb/3.0/bin/psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"
@marcocitus
marcocitus / replace-pg_table_size.sql
Last active Aug 29, 2017
Show distributed table sizes in \d+
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