AI, startup hacks, and engineering miracles from your friends at Faraday

Deleting /var/lib/docker with devicemapper

Seamus Abshere on

If you're running docker with devicemapper as the storage driver, there are a few steps to recover after deleting /var/lib/docker:

$ sudo service docker stop
$ sudo rm -rf /var/lib/docker
$ sudo lvremove docker
$ docker-storage-setup
$ sudo service docker start

This will help you recover from errors like

Error starting daemon: error initializing graphdriver: devicemapper: Non existing device docker-docker–pool

or

Error starting daemon: error initializing graphdriver: Unable to take ownership of thin-pool (dockerVG-docker--pool) that already has used data blocks

Thanks to hints from redhat bugzilla and docker forums.

Set application_name in ActiveRecord connections

Seamus Abshere on

Here's a great way to make Rails apps running on Postgres more inspectable.

Just put this in config/initializers/application_name.rb:

ActiveRecord::Base.connection.class.set_callback(:checkout, :after) { raw_connection.exec "set application_name = 'MyRailsApp'" }

Now when you inspect pg_stat_activity, you can tell which app is which.

psql# select
    application_name,
    state,
    pid,
    regexp_replace(query, '\s+', ' ', 'g') AS "query"
  from pg_stat_activity where not query ~ 'pg_stat_ac' ;
 application_name | state | pid |                                                                                                                                                               query
------------------+-------+-----+--------------------------------
                  | -     |  24 |
                  | -     |  22 |
 MyRailApp        | idle  | 111 |  SELECT a.attname, forma[...]
                  | -     |  20 |
                  | -     |  19 |
                  | -     |  21 |
(6 rows)

Thanks to this question on SO!

How to read CREATE TABLE SQL with pg_query

Seamus Abshere on

pg_query is a really cool Ruby library that uses Postgres's query parser to give you programmatic access to SQL queries.

One thing you can do with it is read a CREATE TABLE statement to map column names to column types:

sql = <<-SQL
  CREATE TABLE example (
    my_text text,
    my_int int,
    my_float float
  )
SQL

require 'pg_query'
types = PgQuery.parse(sql).tree[0].dig('RawStmt', 'stmt', 'CreateStmt', 'tableElts').map do |c|
  c.fetch('ColumnDef')
end.inject({}) do |memo, c|
  memo[c.fetch('colname')] = c.dig('typeName', 'TypeName', 'names').detect { |td| td.dig('String', 'str') != 'pg_catalog' }.dig('String', 'str')
  memo
end

The output will be:

{
  "my_text"  => "text",
  "my_int"   => "int4",
  "my_float" => "float8"
}

Thanks to Lukas Fittl for this gem!

How to generate UUID ranges for SQL queries

Seamus Abshere on

So you've got a table with a UUID primary key. How do you search for only 1/4, or 1/8, or 1/128 of it?

BETWEEN '00000000-0000-0000-0000-000000000000' AND '3fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '40000000-0000-0000-0000-000000000000' AND '7fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '80000000-0000-0000-0000-000000000000' AND 'bfffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'c0000000-0000-0000-0000-000000000000' AND 'ffffffff-ffff-ffff-ffff-ffffffffffff'

And how do you generate those ranges? Here it is in Ruby, with n=4:

UUID_MAX = 2**128
def to_uuid(int)
  memo = int.to_s(16)
  memo = memo.rjust(32, '0')
  [ 8, 13, 18, 23 ].each do |p|
    memo = memo.insert(p, '-')
  end
  memo
end
def sql_uuid_ranges(n)
  chunk = UUID_MAX / n
  (n+1).times.map do |i|
    [chunk*i, chunk*i-1]
  end.each_cons(2).map do |bottom, top|
    a1, _ = bottom
    _, b2 = top
    "BETWEEN '#{to_uuid(a1)}' AND '#{to_uuid(b2)}'"
  end
end
puts sql_uuid_ranges(4)

Note that we have to do some fancy stuff with chunk*i-1 because BETWEEN is inclusive on both sides. Not that that will cause a collision any time before the heat death of the universe, but still.

Here's the same thing, with n=64:

BETWEEN '00000000-0000-0000-0000-000000000000' AND '03ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '04000000-0000-0000-0000-000000000000' AND '07ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '08000000-0000-0000-0000-000000000000' AND '0bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '0c000000-0000-0000-0000-000000000000' AND '0fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '10000000-0000-0000-0000-000000000000' AND '13ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '14000000-0000-0000-0000-000000000000' AND '17ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '18000000-0000-0000-0000-000000000000' AND '1bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '1c000000-0000-0000-0000-000000000000' AND '1fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '20000000-0000-0000-0000-000000000000' AND '23ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '24000000-0000-0000-0000-000000000000' AND '27ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '28000000-0000-0000-0000-000000000000' AND '2bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '2c000000-0000-0000-0000-000000000000' AND '2fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '30000000-0000-0000-0000-000000000000' AND '33ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '34000000-0000-0000-0000-000000000000' AND '37ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '38000000-0000-0000-0000-000000000000' AND '3bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '3c000000-0000-0000-0000-000000000000' AND '3fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '40000000-0000-0000-0000-000000000000' AND '43ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '44000000-0000-0000-0000-000000000000' AND '47ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '48000000-0000-0000-0000-000000000000' AND '4bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '4c000000-0000-0000-0000-000000000000' AND '4fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '50000000-0000-0000-0000-000000000000' AND '53ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '54000000-0000-0000-0000-000000000000' AND '57ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '58000000-0000-0000-0000-000000000000' AND '5bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '5c000000-0000-0000-0000-000000000000' AND '5fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '60000000-0000-0000-0000-000000000000' AND '63ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '64000000-0000-0000-0000-000000000000' AND '67ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '68000000-0000-0000-0000-000000000000' AND '6bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '6c000000-0000-0000-0000-000000000000' AND '6fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '70000000-0000-0000-0000-000000000000' AND '73ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '74000000-0000-0000-0000-000000000000' AND '77ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '78000000-0000-0000-0000-000000000000' AND '7bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '7c000000-0000-0000-0000-000000000000' AND '7fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '80000000-0000-0000-0000-000000000000' AND '83ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '84000000-0000-0000-0000-000000000000' AND '87ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '88000000-0000-0000-0000-000000000000' AND '8bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '8c000000-0000-0000-0000-000000000000' AND '8fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '90000000-0000-0000-0000-000000000000' AND '93ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '94000000-0000-0000-0000-000000000000' AND '97ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '98000000-0000-0000-0000-000000000000' AND '9bffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN '9c000000-0000-0000-0000-000000000000' AND '9fffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'a0000000-0000-0000-0000-000000000000' AND 'a3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'a4000000-0000-0000-0000-000000000000' AND 'a7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'a8000000-0000-0000-0000-000000000000' AND 'abffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'ac000000-0000-0000-0000-000000000000' AND 'afffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'b0000000-0000-0000-0000-000000000000' AND 'b3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'b4000000-0000-0000-0000-000000000000' AND 'b7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'b8000000-0000-0000-0000-000000000000' AND 'bbffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'bc000000-0000-0000-0000-000000000000' AND 'bfffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'c0000000-0000-0000-0000-000000000000' AND 'c3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'c4000000-0000-0000-0000-000000000000' AND 'c7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'c8000000-0000-0000-0000-000000000000' AND 'cbffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'cc000000-0000-0000-0000-000000000000' AND 'cfffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'd0000000-0000-0000-0000-000000000000' AND 'd3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'd4000000-0000-0000-0000-000000000000' AND 'd7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'd8000000-0000-0000-0000-000000000000' AND 'dbffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'dc000000-0000-0000-0000-000000000000' AND 'dfffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'e0000000-0000-0000-0000-000000000000' AND 'e3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'e4000000-0000-0000-0000-000000000000' AND 'e7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'e8000000-0000-0000-0000-000000000000' AND 'ebffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'ec000000-0000-0000-0000-000000000000' AND 'efffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'f0000000-0000-0000-0000-000000000000' AND 'f3ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'f4000000-0000-0000-0000-000000000000' AND 'f7ffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'f8000000-0000-0000-0000-000000000000' AND 'fbffffff-ffff-ffff-ffff-ffffffffffff'
BETWEEN 'fc000000-0000-0000-0000-000000000000' AND 'ffffffff-ffff-ffff-ffff-ffffffffffff'

You get the idea.

ActiveRecord ↔︎ Sequel raw sql cheat sheet

Seamus Abshere on

UPDATED with fixes from @jeremyevans

Going back and forth between ActiveRecord and Sequel can be very confusing... especially because the Sequel documentation assumes that you use "datasets" instead of raw sql.

What ActiveRecord Sequel
One value ActiveRecord::Base.connection.select_value(sql) DB[sql].get
One row ActiveRecord::Base.connection.select_row(sql) DB[sql].first
One column across many rows ActiveRecord::Base.connection.select_values(sql) DB[sql].select_map
Many rows as hashes ActiveRecord::Base.connection.select_all(sql) DB[sql].all
Quote value ActiveRecord::Base.connection.quote(value) DB.literal(value)
Quote identifier (e.g. table name, column name) ActiveRecord::Base.connection.quote_ident(ident) DB.quote_identifier(ident)

Please email us if you think of any other ones!