This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Parent table | |
CREATE TABLE events ( | |
event_id BIGINT, | |
event_date INT, | |
monthyear INT, | |
year INT, | |
FractionDate DOUBLE PRECISION, | |
Actor1Code TEXT, | |
Actor1Name TEXT, | |
Actor1CountryCode TEXT, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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$ |
OlderNewer