Created
November 23, 2016 08:30
-
-
Save saicitus/e8fef5cb7a75170d1ec4ed0d970e55ef to your computer and use it in GitHub Desktop.
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
My tests: | |
CREATE TABLE flows(id integer, app_protocol_details jsonb); | |
SELECT create_distributed_table('flows','id'); | |
Query which din't run: | |
select app_protocol_details from flows fu, jsonb_array_elements(fu.app_protocol_details->'protocols') protocols where protocols->>'{dnsProtocol,rc}' like '%ERROR%'; | |
Attempt #1:(Failed) | |
set citus.subquery_pushdown to on; | |
SELECT a FROM (select app_protocol_details a,id from flows fu, jsonb_array_elements(fu.app_protocol_details->'protocols') protocols where protocols->>'{dnsProtocol,rc}' like '%ERROR%' GROUP BY id, app_protocol_details) a; | |
ERROR: cannot push down this subquery | |
DETAIL: Table expressions other than simple relations and subqueries are currently unsupported | |
Attempt #2:(Failed) | |
SELECT citus_run_on_all_placements('flows','select app_protocol_details from %I fu, jsonb_array_elements(fu.app_protocol_details->''protocols'') protocols where protocols->>''{dnsProtocol,rc}'' like ''%ERROR%'';',true); | |
ERROR: unrecognized conversion type specifier "E" | |
CONTEXT: SQL statement "WITH citus_placements AS ( | |
SELECT | |
ds.logicalrelid::regclass AS tablename, | |
ds.shardid AS shardid, | |
shard_name(ds.logicalrelid, ds.shardid) AS shardname, | |
dsp.nodename AS nodename, dsp.nodeport::int AS nodeport | |
FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid) | |
WHERE dsp.shardstate = 1 and ds.logicalrelid::regclass = table_name | |
ORDER BY ds.logicalrelid, ds.shardid, dsp.nodename, dsp.nodeport) | |
SELECT | |
array_agg(cp.nodename), array_agg(cp.nodeport), array_agg(cp.shardid), | |
array_agg(format(command, cp.shardname)) | |
FROM citus_placements cp" | |
PL/pgSQL function citus_run_on_all_placements(regclass,text,boolean) line 8 at SQL statement | |
Attempt #3: (Success) | |
Created function on master and workers and and rewrote the query. Idea might be similar please check semantics. | |
CREATE OR REPLACE FUNCTION check_error (app_prot jsonb) | |
RETURNS boolean | |
AS $$ | |
DECLARE | |
i jsonb; | |
BEGIN | |
FOR i IN SELECT * FROM jsonb_array_elements(app_prot->protocols) | |
LOOP | |
IF i->>>'{dnsProtocol,rc}' like '%ERROR%' THEN | |
RETURN true; | |
END IF; | |
END LOOP; | |
RETURN false; | |
END; | |
$$ language plpgsql | |
select app_protocol_details from flows fu WHERE check_error(fu.app_protocol_details); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment