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.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[]; |
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.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 |
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
/* 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[]; |
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
#!/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 { |
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.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, |
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 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 |
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
-- Q1 | |
select ol_number, | |
sum(ol_quantity) as sum_qty, | |
sum(ol_amount) as sum_amount, | |
avg(ol_quantity) as avg_qty, | |
avg(ol_amount) as avg_amount, | |
count(*) as count_order | |
from order_line | |
where ol_delivery_d > '2007-01-02 00:00:00.000000' | |
group by ol_number order by ol_number; |
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 rollups ( | |
name text, | |
rolled_up_generation bigint default -1 | |
); | |
-- Create a stub on workers to allow usage as a default in distributed tables | |
SELECT run_command_on_workers($$ | |
CREATE OR REPLACE FUNCTION current_rollup_generation(rollup_name text) | |
RETURNS bigint LANGUAGE sql | |
AS $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
BEGIN; | |
CREATE TABLE stock_partitioned (LIKE stock INCLUDING ALL) PARTITION BY HASH (s_w_id); | |
CREATE TABLE stock_00 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 0); | |
CREATE TABLE stock_01 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 1); | |
CREATE TABLE stock_02 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 2); | |
CREATE TABLE stock_03 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 3); | |
CREATE TABLE stock_04 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 4); | |
CREATE TABLE stock_05 PARTITION OF s |
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
Instructions to downgrade from v9.2.3 to 9.2.2: | |
1. Install Citus v9.2.2 | |
2. Copy citus--9.3-1--9.2-2.sql to <postgresql installation directory>/share/extension/citus--9.3-1--9.2-2.sql | |
3. Restart PostgreSQL | |
4. Run: ALTER EXTENSION citus UPDATE; | |
5. Delete <postgresql installation directory>/share/extension/citus--9.3-1--9.2-2.sql |