Stopping a blocking query in postgresql

Posted on 22 June 2016 by kosmas

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

1	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)

1	select pg_cancel_backend(pid)

(issues) lib/issues/github_issues.ex:14: Issues.GithubIssues.handle_response({:error, %HTTPoison.Error{id: nil, reason: :connect_timeout}})

Posted on 9 June 2016 by kosmas

Facebook

Twitter

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}})

1	(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"}])

1	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

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"}])

1	iex(1)> Issues.GithubIssues.fetch("elixir-lang", "elixir", [{:proxy, "your_proxy_ip:your_proxy_port"}])

function for getting the bitmask integer value in PostgreSQL

Posted on 3 June 2016 by kosmas

Facebook

Twitter

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;

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;

42

Web notebook about thoughts and discoveries in DevOps, SRE, Software Engineering….

Monthly Archives: June 2016

Stopping a blocking query in postgresql

(issues) lib/issues/github_issues.ex:14: Issues.GithubIssues.handle_response({:error, %HTTPoison.Error{id: nil, reason: :connect_timeout}})

function for getting the bitmask integer value in PostgreSQL