Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

Block or report user

Report or block marcocitus

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@marcocitus
marcocitus / alter-pgshard-table.sh
Created Mar 14, 2015
Shell commands to ALTER a pg_shard distributed table
View alter-pgshard-table.sh
TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"
psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
psql -tA -F" " -h localhost -d postgres -c "select relname, s.id, p.node_name, p.node_port from pg_class c, pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement p where s.relation_id = c.oid and s.id = p.shard_id and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c 'psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"
@marcocitus
marcocitus / create-customer_reviews.sql
Created May 4, 2015
Create different types of customer_reviews tables
View create-customer_reviews.sql
CREATE TABLE customer_reviews_regular
(
customer_id TEXT not null,
review_date DATE not null,
review_rating INTEGER not null,
review_votes INTEGER,
review_helpful_votes INTEGER,
product_id CHAR(10) not null,
product_title TEXT not null,
product_sales_rank BIGINT,
@marcocitus
marcocitus / compress.sql
Created Aug 19, 2015
PL/pgsql functions to compress and decompress PostgreSQL tables using cstore_fdw
View compress.sql
-- Compress a table using cstore_fdw
CREATE OR REPLACE FUNCTION compress_table(uncomp_table regclass) RETURNS VOID AS
$BODY$
DECLARE
comp_table_name varchar := table_name || '_comp';
BEGIN
IF EXISTS (SELECT 1 FROM pg_class WHERE oid = table_name AND relkind = 'f') THEN
RETURN;
END IF;
@marcocitus
marcocitus / master.sql
Last active Feb 13, 2016
Adding stage tables to CitusDB
View master.sql
CREATE TABLE IF NOT EXISTS logs (
id uuid,
date timestamptz,
version jsonb,
data jsonb
);
CREATE INDEX logs_id_idx ON logs (id);
SELECT master_create_distributed_table('logs', 'date', 'append');
@marcocitus
marcocitus / events.sql
Created Sep 23, 2015
gdelt inherited table
View events.sql
-- Parent table
CREATE TABLE events (
event_id BIGINT,
event_date INT,
monthyear INT,
year INT,
FractionDate DOUBLE PRECISION,
Actor1Code TEXT,
Actor1Name TEXT,
Actor1CountryCode TEXT,
@marcocitus
marcocitus / view.sql
Created Apr 12, 2016
Create view in Citus
View view.sql
CREATE OR REPLACE FUNCTION public.create_shard_view(table_shardid bigint, view_name regclass, query_format text)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
DECLARE
view_shardid bigint := master_get_new_shardid();
table_name regclass;
placement record;
BEGIN
SELECT logicalrelid INTO table_name FROM pg_dist_shard WHERE shardid = table_shardid;
@marcocitus
marcocitus / product_text_search.sql
Created Apr 13, 2016
Scalable real-time product search using PostgreSQL with Citus
View product_text_search.sql
CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
SELECT setweight(to_tsvector(name),'A') ||
setweight(to_tsvector(description),'B');
$function$;
View citus.source
--
-- Test compatibility with Citus extension
--
-- Function to perform a COPY, but hide error messages
CREATE OR REPLACE FUNCTION silent_copy()
RETURNS void LANGUAGE plpgsql AS $function$
BEGIN
COPY contestant FROM '@abs_srcdir@/data/contestants.1.csv' WITH CSV;
EXCEPTION WHEN OTHERS THEN
@marcocitus
marcocitus / consecutive-users.sql
Last active May 1, 2016
Retention query using Citus
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;
@marcocitus
marcocitus / partitioning-.sql
Last active May 13, 2016
Simple partitioning for Citus
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$
You can’t perform that action at this time.