marcocitus

CREATE OR REPLACE FUNCTION alter_table_access_method(table_name regclass, access_method name)
RETURNS void
LANGUAGE plpgsql 
AS $$
DECLARE
        compressed_table_name name := table_name || '_cmp';
        original_table_name name := table_name::name;
        table_relkind char; 
        parent_table_name regclass;
        partition_boundaries text;

marcocitus

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

marcocitus

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

marcocitus

-- 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;

marcocitus

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

#!/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

/* 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

-- Create the raw events table
CREATE TABLE page_views (
    site_id int,
    path text,
    client_ip inet,
    view_time timestamptz default now(),
    view_id bigserial
);

-- Allow fast lookups of ranges of sequence IDs

marcocitus

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

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[];