postgreSQL: get some table statistics

select * from pg_stat_all_tables where schemaname = 'short_message_db' and relname = 'dt_msg';
select * from pg_statio_all_tables where schemaname = 'short_message_db' and relname = 'dt_msg';

macOS: bash error: declare: -A: invalid option

[jerome@jeroboam] > ./avws-stores.sh 
+++ dirname ./avws-stores.sh
++ cd .
++ pwd
+ SCRIPT_DIR=/Users/jerome/src/boss-sms-vas/release
+ REPO_NAME=boss-sms-vas-stores
+ declare -A AWS_S3_BUCKETS
./avws-stores.sh: line 16: declare: -A: invalid option
declare: usage: declare [-afFirtx] [-p] [name[=value] ...]

Reason: The standard macOS bash version is an antiquity.
Source: Upgrading Bash on macOS

[jerome@jeroboam] > brew install bash
[jerome@jeroboam] > sudo vi /etc/shells

/bin/bash
/bin/csh
/bin/ksh
/bin/sh
/bin/tcsh
/bin/zsh
/usr/local/bin/bash

[jerome@jeroboam] > chsh -s /usr/local/bin/bash

AWS: list Aurora supported PostgreSQL versions

[jerome@jeroboam] > aws rds describe-db-engine-versions --engine aurora-postgresql --region eu-west-1
[
...
        {
            "Engine": "aurora-postgresql", 
            "DBParameterGroupFamily": "aurora-postgresql10", 
            "SupportsLogExportsToCloudwatchLogs": false, 
            "SupportsReadReplica": false, 
            "DBEngineDescription": "Aurora (PostgreSQL)", 
            "SupportedEngineModes": [
                "provisioned"
            ], 
            "EngineVersion": "10.6", 
            "DBEngineVersionDescription": "Aurora PostgreSQL (compatible with PostgreSQL 10.6)", 
            "ValidUpgradeTarget": []
        }
    ]

Clojure spec: key required depending on another key value

;; status > 0 => msgid required
(defmulti sendmt-rsp-mm (fn [rsp] (-> rsp :status (clojure.string/starts-with? "-")))) ; Actually means: status < 0
(defmethod sendmt-rsp-mm false [_] (s/keys :req-un [::status
                                                    ::msgid]))
(defmethod sendmt-rsp-mm true  [_] (s/keys :req-un [::status
                                                    ::text]))
(s/def ::sendmt-rsp (s/multi-spec sendmt-rsp-mm (fn [gen-v _] gen-v)))

or

(s/def ::sendmt-rsp (s/and (s/keys :req-un [::status])
                            #(if (clojure.string/starts-with? (:status %) "-")
                               (contains? % :text)
                               (contains? % :msgid))))

user> (s/valid? ::sendmt-rsp {:status "2" :text "caca"})
false
user> (s/valid? ::sendmt-rsp {:status "2" :msgid "caca"})
true
user> (s/valid? ::sendmt-rsp {:status "-2" :text "caca"})
true
user> (s/valid? ::sendmt-rsp {:status "-2" :msgid "caca"})
false

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)

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

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

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:

• Use a YubiKey as a MFA device to replace Google Authenticator
• Faster AWS/PayPal/TOTP two factor auth with Yubikey

1. Install the Yubikey CLI

brew install ykman

2. Insert the Yubikey
3. Show a list of configured TOTP accounts

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

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

5. Push the "Manage MFA Device" button.
   
6. 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.
7. 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

8. Then, this will get you a 6 digit code.

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

9. Start Automator, and create a new Quick Action.
   
   
10. Search for applescript.
    
    
11. Drag and drop "Run applescript" to the right hand side, select "Workflow receives no input", and type the following code.
    
    
12. Then File | Save, and go to System Preferences | Keyboard | Shortcuts | Services to assign a shortcut to your new service.
    
    
13. 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.
    
    

NB: First time I tried the shortcut in Firefox, I got this error: 

but macOS Mojave, Automator “Not authorized to send Apple events to System Events.” gave the solution:
I quote:

System Preferences > Security & Privacy > Accessibility > Click Automator and TADA it works.

End quote

macOS: merge 2 terminals in tabs

Source: How can I merge two terminal windows in OS X lion?
This still actually works with macOS 10.14.2

Github: retrieve individual files from Github in a Dockerfile

FROM alpine:edge

ARG githubtoken=1852...

RUN apk add --no-cache curl wget

RUN curl -o /var/mbqt/lib/MBQT/Bootstrap.pm -H "Authorization: token $githubtoken" -H 'Accept: application/vnd.github.v3.raw' -L https://api.github.com/repos///contents/perl/lib/MBQT/Bootstrap.pm

PostgreSQL: output from multi-line pl/pgsql from psql from perl

#!/usr/bin/perl

my $cmd = "psql -Xq --set ON_ERROR_STOP=on --dbname crmmbqt <<'EOS'
";
$cmd .= <<'END';
do
$$
begin
    raise notice '%', pg_is_in_recovery();
end
$$
END
$cmd .= 'EOS
';

print `$cmd`;

PostgreSQL: see the age of locks

Source: https://wiki.postgresql.org/wiki/Lock_Monitoring

select a.pid, c.relname, l.transactionid, l.mode, l.granted, a.usename, a.query, a.query_start, age(now(), a.query_start) as "age" from pg_stat_activity a join pg_locks l on l.pid = a.pid join pg_class c on c.oid = l.relation order by a.query_start

macOS: match PC keyboard with apple keyboard

In order to match the order of the modifier keys between both types of keyboards, we need to reverse the Option key (⌥) and the Command key (⌘) on the PC keyboard. This can be done in Settings | Keyboard.

This way, we can have, Ctrl, Windows, Alt

match ^, ⌥, ⌘

Docker: difference between container and image

Source: In Docker, what's the difference between a container and an image?
See explanation from cbare: "Images are frozen immutable snapshots of live containers..."

Emacs: How can I tell emacs not to break long lines?

Source: How can I tell emacs not to break long lines?

M-x auto-fill-mode

GitHub: search code

In order to search code in all the repo of a user, use e.g. OPERATOR_CONNECTION user:AirVantage

Perl: shortest slurp

Source: Perl Slurp-Eaze

my $str = 'my '.do { local(@ARGV, $/) = $pl_filename; <> };

macOS: installing High Sierra on a new combo

GigaByte Z370 HD3P - i7-8700 Coffee Lake - iGPU HD 630 - High Sierra

macOS: from 10.14 to 10.14.1

Steps:

• Update Clover through Clover Configurator: pick custom install and check (default if already done once)
  • AptioMemoryFix-64,
  • ApfsDriverLoader-64 and
  • PartitionDxe-64
• Update kexts in /Volumes/EFI/EFI/CLOVER/kexts/Other
• AppleALC.kext
• lilu.kext
• WhateverGreen.kext
• FakeSMC.kext
• IntelMausiEthernet.kext
• Update macOS to 10.14.1 via Software Update
  
  and leave it reboot by default
• As my config. was booting, but got stuck, not starting the macOS GUI, I changed the Clover options at boot time so as to replace iMac 17,1, which was working for 10.14, with iMac 18,3. This allowed to start the GUI but the display was flickering like hell. Multiple trials later, I found out iMac 14,2 was solving the flickering issue.
  
• Follow [Guide] Creating a Custom SSDT for USBInjectAll.kext so as to solve USB3 ports issues.
  At first, I thought about following Intel FB-Patcher v1.6.3 | USB Port Patching, because less complicated, but I found out the FB-Patcher is not as reliable as the RehabMan's method for detecting ports.
  At least, FB-Patcher told me I was using a "USB controller - XHC - 8086:A2AF". So,
  • Removed all legacy High Sierra ACPI changes mentioned in my GigaByte Z370 HD3P - i7-8700 Coffee Lake - iGPU HD 630 - High Sierra page, that were useless in 10.14, i.e. Change HDAS to HDEF, Change HECI to IMEI and Change GFX0 to IGPU.
  • Copied USBInjectAll.kext into /Volumes/EFI/EFI/CLOVER/kexts/Other.
  • Copied XHCI-unsupported.kext (from USBInjectAll ZIP) into /Volumes/EFI/EFI/CLOVER/kexts/Other, like mentioned in OS-X-USB-Inject-All for a 8086:a2af USB controller.
  • EHCx renames and port limit patches, like in config_patches.plist.
  • 1st reboot with -uia_exclude_ss option at Clover boot
  • Test of HS ports, validating HS03, HS04, HS07,HS08,HS09,HS10,HS11,HS12,HS13,HS14 as USB3 ports.
  • 2nd reboot with uia_exclude=HS01,...,HS09,HS11,...,HS14,USR1 because proposed option (-uia_exclude_ss uia_include=HS10) was not keeping HS10 (my Dell screen USB hub where my keyboard and mouse are plugged in) active.
  • Test of SS ports, validating SS03, SS04, SS07, SS08 as USB3 ports.
  • Updated SSDT-UIAC-ALL.dsl so as to remove everything not concerning 8086_a2af, like in

    // SSDT-UIAC-ALL.dsl
    //
    // This SSDT can be used as a template to build your own
    // customization for USBInjectAll.kext.
    //
    // This SSDT contains all ports, so using it is the same as without
    // a custom SSDT.  Delete ports that are not connected or ports you
    // do not need.
    //
    // Change the UsbConnector or portType as needed to match your
    // actual USB configuration.
    //
    // Note:
    // portType=0 seems to indicate normal external USB2 port (as seen in MacBookPro8,1)
    // portType=2 seems to indicate "internal device" (as seen in MacBookPro8,1)
    // portType=4 is used by MacBookPro8,3 (reason/purpose unknown)
    //
    
    DefinitionBlock ("", "SSDT", 2, "hack", "_UIAC", 0)
    {
        Device(UIAC)
        {
            Name(_HID, "UIA00000")
    
            Name(RMCF, Package()
            {
                "8086_a2af", Package()
                {
                    "port-count", Buffer() { 26, 0, 0, 0 },
                    "ports", Package()
                    {
                        "HS03", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 3, 0, 0, 0 },
                        },
                        "HS04", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 4, 0, 0, 0 },
                        },
                        "HS07", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 7, 0, 0, 0 },
                        },
                        "HS08", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 8, 0, 0, 0 },
                        },
                        "HS09", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 9, 0, 0, 0 },
                        },
                        "HS10", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 10, 0, 0, 0 },
                        },
                        "HS11", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 11, 0, 0, 0 },
                        },
                        "HS12", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 12, 0, 0, 0 },
                        },
                        "HS13", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 13, 0, 0, 0 },
                        },
                        "HS14", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 14, 0, 0, 0 },
                        },
                        "SS03", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 19, 0, 0, 0 },
                        },
                        "SS04", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 20, 0, 0, 0 },
                        },
                        "SS07", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 23, 0, 0, 0 },
                        },
                        "SS08", Package()
                        {
                            "UsbConnector", 3,
                            "port", Buffer() { 24, 0, 0, 0 },
                        },
                    },
                },
            })
        }
    }
    //EOF
    

  • Used MaciAS so as to compile SSDT-UIAC-ALL.dsl into SSDT-UIAC-ALL.aml by using File | Save As, format: ACPI Machine Language Binary.
  • Copied SSDT-UIAC-ALL.aml into /Volumes/EFI/EFI/CLOVER/ACPI/patched
  • Rebooted.
    

Clojure: load deps.edn aliases extra-deps from Cider

Let's say this is deps.edn:

{:paths ["src" "resources"]
 :deps {com.datomic/ion {:mvn/version "0.9.26"}
        org.clojure/data.json {:mvn/version "0.2.6"}
        org.clojure/clojure {:mvn/version "1.9.0"}}
 :mvn/repos {"datomic-cloud" {:url "s3://datomic-releases-1fc2183a/maven/releases"}}
 :aliases
 {:dev {:extra-deps {com.datomic/client-cloud {:mvn/version "0.8.63"}
                     com.datomic/ion-dev {:mvn/version "0.9.176"}}}}}

Then, if we use M-x cider-jack-in from Emacs, this will not load the aliases extra-deps.
In order to do so, invoke cider-jack-in interactively:

C-u M-x cider-jack-in

Then, when the REPL command is displayed, use C-a to go to the beginning of the line and add -R:dev (see Clojure Deps and CLI Guide) in order to take into account optional dependencies.