Problem
You run a query in PostgreSQL that takes a lot of time to finish (maybe because of an error) and you want to stop/kill it as it is blocking other queries in db.
Solution
You will need to find the pid for the query, either by using a GUI program like Pgadmin or by issuing the following to an SQL prompt:
select * from pg_stat_activity
make a note of the pid of the query that you want to stop/kill and issue the following:
select pg_cancel_backend(pid)
Problem
Trying to follow the example in ‘Programming Elixir 1.2’ for fetching the issues from Github you are getting the following error:
(issues) lib/issues/github_issues.ex:14: Issues.GithubIssues.handle_response({:error, %HTTPoison.Error{id: nil, reason: :connect_timeout}})
Solution
You are quite likely behind a proxy and you get a timeout. Add the proxy details to the HTTPoison.get request as follows:
iex(4)> HTTPoison.get!( "https://api.github.com/repos/elixir-lang/elixir", [], [{:proxy, "your_proxy_ip:your_proxy_port"}])
And you could make the following change to the github_issues.ex file to be able to work with the proxy:
def fetch(user, project, proxy) do issues_url(user, project) |> HTTPoison.get(@user_agent, proxy) |> handle_response end
So you can call it like the following from iex:
iex(1)> Issues.GithubIssues.fetch("elixir-lang", "elixir", [{:proxy, "your_proxy_ip:your_proxy_port"}])
Problem
You have set up some bitmask values and you would like to return the calculated value given a string with one or more of the codes used, to be able to used it in an SQL query.
Solution
You can use the following to create a function called bitmask. This one deals with any number of individual codes (2 and 3 digit codes in this case).
It also does not calculate duplicate values since they are removed.
So for this example the string ’02a’ is the same as ’02a,02a’ and should give 16 in both cases.
CREATE OR REPLACE FUNCTION bitmask(code text) RETURNS integer AS $$
DECLARE bm INTEGER := 0;
DECLARE total INTEGER := 0;
DECLARE counter INTEGER := 0;
DECLARE code_to_translate TEXT := '';
BEGIN
LOOP
EXIT WHEN LENGTH(code) = 0;
/* only take first 2 or 3 characters */
code_to_translate := SPLIT_PART(code, ',', 1);
CASE code_to_translate
WHEN 'XX' THEN bm = 0;
WHEN '01a' THEN bm = 2;
WHEN '01b' THEN bm = 4;
WHEN '01c' THEN bm = 8;
WHEN '02a' THEN bm = 16;
WHEN '02b' THEN bm = 32;
WHEN '03' THEN bm = 64;
WHEN '04a' THEN bm = 128;
WHEN '04b' THEN bm = 256;
WHEN '04c' THEN bm = 512;
WHEN '04d' THEN bm = 1024;
WHEN '05' THEN bm = 2048;
WHEN '06a' THEN bm = 4096;
WHEN '06b' THEN bm = 8192;
WHEN '10a' THEN bm = 16384;
WHEN '10b' THEN bm = 32768;
WHEN '10c' THEN bm = 65536;
WHEN '10d' THEN bm = 131072;
WHEN '11a' THEN bm = 262144;
WHEN '11b' THEN bm = 524288;
ELSE bm = 0;
END CASE;
total := total + bm;
counter := counter + 1;
/* Remove checked code so we do not have duplicates */
code := regexp_replace(code, code_to_translate||'[,]*', '', 'g');
END LOOP ;
RETURN total;
END;
$$ LANGUAGE plpgsql;