View alter_table_access_method.sql
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; |
View stock_partitioned.sql
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 |
View Instructions
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 |
View queries.sql
-- 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; |
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 |
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 { |
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[]; |
View example.sql
-- 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 |
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 |
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[]; |
NewerOlder