A bozo's blog: March 2019

Friday, March 22, 2019

postgreSQL: collapse contiguous ranges

Source: Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

with recursive
data as (
    select * from (
    values  ('foo', 2, 3),
            ('foo', 3, 4),
            ('foo', 4, 5),
            ('foo', 10, 11),
            ('foo', 11, 13),
            ('foo', 13, 15),
            ('bar', 1, 2),
            ('bar', 2, 4),
            ('bar', 7, 8)
    ) as baz (name, first, last)
),
recur (name, first, last) as (
    select name, first, last, last-first as span from data
    union all
    select name, data.first, data.last, recur.span+data.last-data.first as span
    from data join recur using (name)
    where data.first = recur.last
)
select name, start, start + span as end, span from (
    select name, (last-span) as start, max(span) as span from (
         select name, first, last, max(span) as span 
        from recur
        group by name, first, last
    ) as z
    group by name, (last-span)
) as z
 
┌──────┬───────┬─────┬──────┐
│ name │ start │ end │ span │
├──────┼───────┼─────┼──────┤
│ bar  │     1 │   4 │    3 │
│ bar  │     7 │   8 │    1 │
│ foo  │    10 │  15 │    5 │
│ foo  │     2 │   5 │    3 │
└──────┴───────┴─────┴──────┘
(4 rows)

Or if we do not have a "last" column.

with recursive
data as (
    select * from (
        values ('foo', 2),
               ('foo', 3),
               ('foo', 4),
               ('foo', 10),
               ('foo', 11),
               ('foo', 13),
               ('bar', 1),
               ('bar', 2),
               ('bar', 7)
    ) as baz (name, first)
),
recur (name, first) as (
    select name, first, 1 as span from data
    union all
    select name, data.first, recur.span+1 as span
    from data
    join recur using (name)
    where data.first = recur.first + 1
)
select name, start, start + span - 1 as end, span from (
    select name, (first+1-span) as start, max(span) as span from (
        select name, first, max(span) as span 
        from recur
        group by name, first
    ) as z
    group by name, start
) as z
order by name, start
 
┌──────┬───────┬─────┬──────┐
│ name │ start │ end │ span │
├──────┼───────┼─────┼──────┤
│ bar  │     1 │   2 │    2 │
│ bar  │     7 │   7 │    1 │
│ foo  │     2 │   4 │    3 │
│ foo  │    10 │  11 │    2 │
│ foo  │    13 │  13 │    1 │
└──────┴───────┴─────┴──────┘
(5 rows)

The problem is that with recursive tends to be slow when dealing with millions of rows.
How to find the boundaries of groups of contiguous sequential numbers? proposes a faster approach.

with
data as (
    select * from (
        values ('foo', 2),
               ('foo', 3),
               ('foo', 4),
               ('foo', 10),
               ('foo', 11),
               ('foo', 13),
               ('bar', 1),
               ('bar', 2),
               ('bar', 7)
    ) as baz (name, first)
),
island as (
    select first - row_number() over (order by name, first) as grp,
    name, first
    from data
)
select name, min(first) as start, max(first) as end
from island
group by name, grp
order by name, start
 
┌──────┬───────┬─────┐
│ name │ start │ end │
├──────┼───────┼─────┤
│ bar  │     1 │   2 │
│ bar  │     7 │   7 │
│ foo  │     2 │   4 │
│ foo  │    10 │  11 │
│ foo  │    13 │  13 │
└──────┴───────┴─────┘
(5 rows)

Thursday, March 14, 2019

macOS: terminal UTF-8 issues

If you cannot display unicode characters in psql, e.g.

[jerome@jeroboam] > psql mydb
mydb=# \d public.tap_funky 
         View "public.tap_funky"
<E2><94><8C><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><AC><E2><94><80><E2><94><80><E2>
<94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><AC><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94>
<80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><80><E2><94><90>
<E2><94><82>   Column    <E2><94><82>     Type     <E2><94><82> Modifiers <E2><94><82>

Then

[jerome@jeroboam] > vi ~/.bash_profile

1	`export` `LANG=en_US.UTF-8`

[jerome@jeroboam] > source ~/.bash_profile

And finally

mydb=# \d public.tap_funky 
         View "public.tap_funky"
┌─────────────┬──────────────┬───────────┐
│   Column    │     Type     │ Modifiers │
├─────────────┼──────────────┼───────────┤
│ oid         │ oid          │           │
│ schema      │ name         │           │
│ name        │ name         │           │
│ owner       │ name         │           │
│ args        │ text         │           │
│ returns     │ text         │           │
│ langoid     │ oid          │           │
│ is_strict   │ boolean      │           │
│ is_agg      │ boolean      │           │
│ is_definer  │ boolean      │           │
│ returns_set │ boolean      │           │
│ volatility  │ character(1) │           │
│ is_visible  │ boolean      │           │
└─────────────┴──────────────┴───────────┘

macOS: Library not loaded: /usr/local/opt/readline/lib/libreadline.7.dylib

If you get:

[jerome@jeroboam] > psql mydb
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.7.dylib
  Referenced from: /usr/local/bin/psql
  Reason: image not found
Abort trap: 6

Fix it with:

[jerome@jeroboam] > ln -s /usr/local/opt/readline/lib/libreadline.8.0.dylib /usr/local/opt/readline/lib/libreadline.7.dylib

Monday, March 4, 2019

AWS MFA with Yubikey and macOS

Purpose: Take advantage of the Yubikey to generate the 6 digit TOTP required by AWS MFA without using Google Authenticator.
Downside: You can't just press the Yubikey button and have the code generated as you would expect, but this can be alleviated with a keyboard shortcut.
Sources:

Install the Yubikey CLI

brew install ykman

Insert the Yubikey
Show a list of configured TOTP accounts

[jerome@jeroboam] > ykman oath list
[jerome@jeroboam] >

Log in to AWS Management Console as usual, pop up the menu by clicking on your user name and select My Security Credentials.

Push the "Manage MFA Device" button.
Select Remove to disable MFA, and then re-start the procedure to activate MFA again.

In the next screen, select “Virtual MFA device”.

Show the secret key: it will be passed to ykman.

Configure MFA for your service.

[jerome@jeroboam] > ykman oath add 'Amazon Web Services:toto@org-prod' 
[jerome@jeroboam] > ykman oath list
Amazon Web Services:toto@org-prod

Then, this will get you a 6 digit code.

[jerome@jeroboam] > ykman oath code --single 'Amazon Web Services:toto@org-prod'

Start Automator, and create a new Quick Action.
Search for applescript.
Drag and drop "Run applescript" to the right hand side, select "Workflow receives no input", and type the following code.
Then File | Save, and go to System Preferences | Keyboard | Shortcuts | Services to assign a shortcut to your new service.
Now, simply log in the AWS Management Console with your password, and when the site asks for the MFA, use the programmed shortcut, which will automatically generate the 6 digit code and grant you access.