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
**Issue**: | |
1) Start a long running query say ~5 minutes. Ex: | |
SELECT count(*) from github_events g1,github_events g2 WHERE g1.repo_id=g2.repo_id; | |
2) Terminate a worker whilst the query is running. | |
3) Now the system hangs indefinitely. | |
**Reasoning** | |
I think what's happening is that removing the network interface doesn't kill the connection. As a result, the coordinator node waits for a response from the worker node, which never comes. | |
Citus uses PostgreSQL's client connection library (libpq) to communicate between machines; and Postgres uses system defaults to timeout in this scenario. On Linux, I think the default TCP keepalive time (tcp_keepalive_time) is set to 7200 seconds. |
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
SELECT nodename, | |
rarray[1] blocked_statement, | |
rarray[2] cur_stmt_blocking_proc, | |
rarray[3] count, | |
rarray[4] age | |
FROM ( | |
SELECT nodename, | |
regexp_split_to_array(RESULT, '\$') rarray | |
FROM ( | |
SELECT nodename,unnest(RESULT) 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
BEGIN; | |
SET search_path TO 'public'; | |
CREATE SCHEMA temp_schema; | |
CREATE TABLE temp_schema.deviceid_rollup_daily (LIKE deviceid_rollup_daily INCLUDING ALL); | |
ALTER TABLE temp_schema.deviceid_rollup_daily ALTER COLUMN state SET DATA TYPE character varying(100); | |
SELECT create_distributed_table('temp_schema.deviceid_rollup_daily','state'); | |
INSERT INTO temp_schema.deviceid_rollup_daily SELECT * from deviceid_rollup_daily; | |
END; | |
-- Move the new table to the actual schema. |
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 AGGREGATE sum(jsonb)( | |
SFUNC = topn_union_trans, | |
STYPE = internal, | |
FINALFUNC = topn_pack | |
); | |
CREATE AGGREGATE sum(text)( | |
SFUNC = topn_add_trans, | |
STYPE = internal, | |
FINALFUNC = topn_pack |
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 events( | |
tenant_id int, | |
id int, | |
type text | |
); | |
--- CREATE 2 roles using roles tab as: | |
-- tenant_1 is the role for tenant_id 1 | |
-- tenant_2 is the role for tenant_id 2 |
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
-- Replace "<*>" with real names | |
psql -h <coordinator_host> -p <coordinator_port> -tA -F" " -c "SELECT nodename, nodeport, shardid FROM pg_dist_shard | |
JOIN pg_dist_shard_placement USING (shardid) WHERE logicalrelid = '<table_name>'::regclass" | | |
xargs -n 3 -P8 sh -c "psql -h \$0 -p \$1 -c \"CLUSTER <table_name>_\$2 USING <table_name>_<index_name>_\$2;\"" |
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
select relallvisible, relpages from pg_class where relname = '<table_name>'; |
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 test (x varchar, y int); | |
SELECT create_distributed_table('test', 'x'); | |
BEGIN; | |
SET LOCAL citus.enable_ddl_propagation TO off; | |
DELETE FROM pg_depend WHERE objid = 'test'::regclass AND objid = refobjid RETURNING *; | |
ALTER TABLE test ALTER COLUMN x TYPE text; | |
SELECT * FROM run_command_on_placements('test', 'ALTER TABLE %I ALTER COLUMN x TYPE text'); | |
UPDATE pg_dist_partition SET partkey = column_name_to_column('test', 'x') WHERE logicalrelid = 'test'::regclass; | |
COMMIT; |
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
-- a reference table | |
CREATE TABLE states ( | |
code char(2) PRIMARY KEY, | |
full_name text NOT NULL, | |
general_sales_tax numeric(4,3) | |
); | |
-- distribute it to all workers | |
SELECT create_reference_table('states'); | |
--large_table |
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 filter (children jsonb[]) | |
RETURNS jsonb[] | |
AS $$ | |
DECLARE | |
i jsonb; | |
result jsonb[]; | |
BEGIN | |
FOR i IN SELECT * FROM jsonb_array_elements(children) | |
LOOP | |
IF i.value -> 'name' != 'null'::jsonb THEN |
NewerOlder