Clojure: vector 2 map

(reduce #(assoc %1 %2 1) {} ["apn1" "apn2" "apn3"])
{"apn1" 1, "apn2" 1, "apn3" 1}

byobu: avoid terminal scrolling at each status update

Did not find a proper way to avoid terminal scrolling to the bottom when the byobu status is updated.
So, increased the tmux status-interval with a big enough value to not be bothered.

vi ~/.byoburc/.tmux.conf

set -g status-interval 86400

PostgreSQL: Table dependencies via Foreign Keys

Source: How to list tables affected by cascading delete

WITH RECURSIVE t AS (
    SELECT
        c.oid AS origin_id,
        c.oid::regclass::text AS origin_table,
        c.oid AS referencing_id,
        c.oid::regclass::text AS referencing_table,
        c2.oid AS referenced_id,
        c2.oid::regclass::text AS referenced_table,
        ARRAY[c.oid::regclass,c2.oid::regclass] AS chain
    FROM pg_catalog.pg_constraint AS co
    INNER JOIN pg_catalog.pg_class AS c ON c.oid = co.conrelid
    INNER JOIN pg_catalog.pg_class AS c2 ON c2.oid = co.confrelid
--     Add this line as an input parameter if you want to make a one-off query
--     WHERE c.oid::regclass::text = 'YOUR TABLE'
    UNION ALL
    SELECT
        t.origin_id,
        t.origin_table,
        t.referenced_id AS referencing_id,
        t.referenced_table AS referencing_table,
        c3.oid AS referenced_id,
        c3.oid::regclass::text AS referenced_table,
        t.chain || c3.oid::regclass AS chain
    FROM pg_catalog.pg_constraint AS co
    INNER JOIN pg_catalog.pg_class AS c3 ON c3.oid = co.confrelid
    INNER JOIN t ON t.referenced_id = co.conrelid
    WHERE
        -- prevent infinite recursion by pruning paths where the last entry in
        -- the path already appears somewhere else in the path
        NOT (
            ARRAY[ t.chain[array_upper(t.chain, 1)] ] -- an array containing the last element
            <@                                        -- "is contained by"
            t.chain[1:array_upper(t.chain, 1) - 1]    -- a slice of the chain,
                                                      -- from element 1 to n-1
        )
)
SELECT  origin_table,
        referenced_table,
        array_upper(chain,1) AS "depth",
        array_to_string(chain,',') as chain
FROM t
where referenced_table ~ 'shared_db'

Git diff between current branch and master

Source: Git diff between current branch and master but not including unmerged master commits

git diff master...

Linux: Alert when running process finishes

Source: Alert when running process finishes

(while kill -0 $pid; do sleep 1; done) && echo "finished"

PostgreSQL: Running SQL scripts with psql

Source: Running SQL scripts with psql

PGOPTIONS='--client-min-messages=warning' psql -X -q -a -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb -f dump.sql

PostgreSQL: Display materialized view definition

select definition from pg_matviews where matviewname = 'mv_jersey_tap_data_3months_lag';

PostgreSQL: Find numeric rows w/o digit after decimal point

Source: Fetch records that are non zero after the decimal point in PostgreSQL

SELECT count(*) FROM t WHERE amount != trunc(amount)

Delete files older than file

Source: Delete files older than specific file

find __dir__ -type f ! -newer __file__ -delete

PostgreSQL: dump and restore one table

pg_dump --host db2.prod.vty.mbqt --port 5432 --username postgres --no-owner --no-password --format custom --encoding UTF8 --verbose --file /tmp/jerome/jerome_test.custom --table public.jerome_test --data-only dwhmbqt

Then

pg_restore --host db2.prod.vty.mbqt --port 5432 --username postgres --no-owner --no-password --dbname dwhmbqt --data-only --verbose /tmp/jerome/jerome_test.custom

PostgreSQL: tables without PK

Source: http://petereisentraut.blogspot.fr/2009/07/how-to-find-all-tables-without-primary.html

-- Tables of db2 without PK
SELECT table_catalog, table_schema, table_name
    FROM information_schema.tables
    WHERE (table_catalog, table_schema, table_name) NOT IN
          (SELECT table_catalog, table_schema, table_name
               FROM information_schema.table_constraints
               WHERE constraint_type = 'PRIMARY KEY')
      AND table_schema NOT IN ('information_schema', 'pg_catalog') and table_schema ~ '_db$' and table_schema not in ('prov_db', 'queue_db', 'ota_db', 'sla_db');

PostgreSQL: find non-empty tables

Source: https://stackoverflow.com/questions/24734489/show-all-not-empty-tables-in-postgres

SELECT n.nspname, c.relname                                                                                                                                                                           
FROM pg_class c
INNER JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.reltuples != 0 AND c.relkind = 'r' and nspname ~ '_db$'

PostgreSQL: .psqlrc sample

Source: https://github.com/datachomp/dotfiles/blob/master/.psqlrc

PostgreSQL: Avoiding deadlocks

Source: Avoiding PostgreSQL deadlocks when performing bulk update and delete operations

The Github graph

Visualized Git practices for team: branch, merge, rebase

Real programmers

Source: XKCD

Internet lost while VPN Cisco and wired to wifi

The situation is the following:

• Wired to LAN on Windows 7 
• VPN Cisco AnyConnect connected 
• Internet OK

then

Change LAN connection from wired to WIFI => Internet lost unless disconnecting VPN

Solution 1: 
Reboot : )

Solution 2: 

• Disconnect Cisco VPN 
• Windows Device Manager | Network adapters 
• Right click on wireless adapter
• Select Disable

Then

• Right click on wireless adapter
• Select Enable
• Reconnect Cisco VPN

PostgreSQL: dump and restore some schemas

Dump

pg_dump --host localhost --port 5432 --username "postgres"  --clean --no-owner --no-password  --format custom --encoding UTF8 --verbose --file "/tmp/dwhmbqt_2017-05-24.dump.custom" --schema resource_db --schema crm_db --schema iot_db --schema offer_db --schema shared_db dwhmbqt

Restore

pg_restore -U postgres -d dwhmbqt /tmp/dwhmbqt_2017-05-24.dump.custom

PostgreSQL: dump functions code

source: How to take backup of functions only in Postgres

PostgreSQL: copy in and out

Source:

•   In and out of PostgreSQL using COPY 
•   How to insert csv data into postgresql db (remote db)

Import from local CSV to remote DB:

psql -Upostgres -h my_host -d my_db -c "\copy public.nwkmonitor from '/tmp/in.csv' with delimiter as ',' null as '';"

Export from remote DB to local CSV:

psql -Upostgres -h my_host -d my_db -c "\copy public.nwkmonitor2 to '/tmp/out.csv' csv delimiter as ',' header;"

PostgreSQL: lateral join tutorial

Source: Postgres LATERAL JOIN

or better, from Erwin Brandstetter:
Source: Split function-returned record into multiple columns

originally correlated to:
Source: error : subquery must return only one column

PostgreSQL: immutable vs stable

Reminder:

• STABLE : If you call it with the same arguments more than once during the execution of a single query it will return the same value
• IMMUTABLE : It will always return the same value for the same arguments, regardless of database contents or the passage of time

PostgreSQL: move from child table to parent table

Source: Postgres table inheritance: move from parent to child and vice versa

with deleted as (delete from  returning *) insert into  select * from deleted

PostgreSQL: create read only user

Source: How to Create a Read-Only Database User in Postgres for Analytics and Reporting

createuser -U postgres -D -R -S aws_user

alter user aws_user with encrypted password 'MY PASSWORD';
grant connect on database dwhmbqt to aws_user;

do
$$
declare
 _query text;
 _schema text;
begin
 for _schema in select nspname from pg_namespace where nspname ~ '_db$' loop
   _query := format('grant usage on schema %I to aws_user', _schema);
   raise notice '%', _query;
    execute _query;
 end loop;
end;
$$

do
$$
declare
 _query text;
 _schema text;
begin
 for _schema in select nspname from pg_namespace where nspname ~ '_db$' loop
  _query := format('grant select on all tables in schema %I to aws_user', _schema);
  raise notice '%', _query;
   execute _query;
 end loop;
end;
$$

Then in pg_hba.conf:

host    dwhmbqt         aws_user           10.192.0.0/16   md5

PostgreSQL: out of shared memory

Sometimes, some transactions are big enough to break the limit of max_locks_per_transaction, and this will cause "out of shared memory" errors.

Identify processes with their number of locks:

select distinct pid, count(*) over (partition by pid) as count from pg_locks

Then identify the query:

select * from pg_stat_activity where pid = 119254

Prevent VPN from changing DNS on Windows 7

Source: Prevent VPN from changing DNS on Windows 7 / 8 developer preview
After Cisco AnyConnect VPN connection:
ping 8.8.8.8 OK
ping google.com KO

netsh interface ipv4 show dnsservers

netsh interface ipv4 add dnsservers name="Local Area Connection 3" address=192.168.1.1 index=1 validate=no

PostgreSQL: size of database

dwhmbqt=# select * from pg_size_pretty(pg_catalog.pg_database_size('dwhmbqt'));                                                                                                                                     
┌────────────────┐
│ pg_size_pretty │
├────────────────┤
│ 858 GB         │
└────────────────┘

PostgrSQL: generate series from date

select generate_series('01/08/2012'::date, '01/03/2017'::date, '1 month'::interval)::date

PostgreSQL: select from values

select co_name, count from (values('PROUT 24', 0), ('65', 0)) as s(co_name, count)

PostgreSQL: Insert zero value rows for missing column value

Source: Insert zero value rows for missing column value
Before:

select month, co_name, count from (
    select distinct co_name, date_trunc('month', from_date)::date as month, count(*) over (partition by co_name, date_trunc('month', from_date)) as count
    from crm_db.ht_co_info where co_name in ('PROTECTION 24', '65')    
) as foo
order by month

After:

with foo as (
    select distinct co_name, date_trunc('month', from_date)::date as month, count(*) over (partition by co_name, date_trunc('month', from_date)) as count
    from crm_db.ht_co_info where co_name in ('PROTECTION 24', '65')
)
select distinct c.co_name, g.month, coalesce(foo2.count, 0)
from (select distinct foo.co_name from foo) c
cross join (select (generate_series('2012-08-01'::date, '2017-04-01'::date, '1 month'::interval))::date) as g(month)
left join foo as foo2 on foo2.co_name = c.co_name and foo2.month = g.month
order by month, co_name

Perl: sort Data::Dumper

Default sort:

$Data::Dumper::Sortkeys  = 1;

Sort by keys:

$Data::Dumper::Sortkeys = sub { [sort {$a cmp $b} keys %{$_[0]}] };

Sort by values:

$Data::Dumper::Sortkeys = sub { [ sort { $_[0]->{$a} <=> $_[0]->{$b} } keys %{$_[0]} ] };

PostgreSQL: order by a list

Source: ORDER BY the IN value list
See answer #15 from Erwin Brandstetter

select ''''||lpad(co_id::text, 12, '0')||''',', serial_id
from crm_db.ht_co_sim
join resource_db.ht_sim_info using (sim_id)
join unnest('{8933240100000334751,
8933240100000334750,
8933240100000334731,
8933240100000334749,
8933240100000334730,
8933240100000334748,
8933240100000334729,
8933240100000334728,
8933240100000334747,
8933240100000334727,
8933240100000334746,
8933240100000334726,
8933240100000334725,
8933240100000334745,
8933240100000334724,
8933240100000334744,
8933240100000334733,
8933240100000334723,
8933240100000334732,
8933240100000334722}'::bigint[]) with ordinality as t(serial_id, ord) using (serial_id)
where ht_co_sim.from_date <= localtimestamp(0) and localtimestamp(0) < ht_co_sim.to_date
and ht_sim_info.from_date <= localtimestamp(0) and localtimestamp(0) < ht_sim_info.to_date
order by t.ord

Perl: lookahead operators

Source: negative regex for perl string pattern match

(?=) - Positive look ahead assertion foo(?=bar) matches foo when followed by bar
(?!) - Negative look ahead assertion foo(?!bar) matches foo when not followed by bar
(?<=) - Positive look behind assertion (?<=foo)bar matches bar when preceded by foo
(?<!) - Negative look behind assertion (?<!foo)bar matches bar when NOT preceded by foo
(?>) - Once-only subpatterns (?>\d+)bar Performance enhancing when bar not present
(?(x)) - Conditional subpatterns
(?(3)foo|fu)bar - Matches foo if 3rd subpattern has matched, fu if not
(?#) - Comment (?# Pattern does x y or z)

Linux: top ten biggest files

du -a /var | sort -n -r | head -n 10

PostgreSQL: exclusive lock on a table

Source: Exploring Query Locks in Postgres

crmmbqt=# begin; lock table radius_db.radacct in access exclusive mode;
BEGIN
Time: 0.516 ms
LOCK TABLE
Time: 1001.108 ms

Then

crmmbqt=# rollback;
ROLLBACK
Time: 0.714 ms

PostgreSQL: list biggest tables

select table_schema||'.'||table_name as table, pg_size_pretty(size) as size from (select table_schema, table_name, pg_total_relation_size(table_schema||'.'||table_name) as size from information_schema.tables order by 3 desc) as foo;

PostgreSQL: dump and restore a table

Dump table:

pg_dump --table offer_db.dt_pool_ip -FC crmmbqt > /tmp/dt_pool_ip.sql

Restore table:

pg_restore --dbname crmmbqt --table=offer_db.dt_pool_ip dt_pool_ip.sql

Git: branching

Git Branching - Basic Branching and Merging

PostgreSQL: size of a relation

Relation w/o inheritance:

select pg_size_pretty(pg_total_relation_size('radius_db.radacct'));

Relation w/ inheritance:

select pg_size_pretty(sum(pg_total_relation_size(inhrelid))::bigint + pg_total_relation_size('ods_db.radius_cdr_pdp')) from pg_inherits where inhparent='ods_db.radius_cdr_pdp'::regclass;

PostgreSQL: detect duplicates

Detect duplicates in at_file after a given date:

select * from monitor_db.at_file
 where exists ( 
 select 'x' 
                  from monitor_db.at_file i
                 where i.file_id = at_file.file_id
                   and i.process_id = at_file.process_id
                   and i.ctid > at_file.ctid
                   and log_date > '2017-01-31'
             )

Linux: Run script as user without login shell

Source : http://serverfault.com/questions/351046/run-script-as-user-who-has-nologin-shell

su -s /bin/bash -c '/tmp/site.pl' apache

Perl: epoch(now)

perl -MTime::Local -e 'print timegm(gmtime)'

PostgreSQL: size of a row

Source: Measure the size of a PostgreSQL table row

  SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   radius_db.radacct t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);
 

mod_perl: how to use environment variable

In httpd.conf:

PerlSetEnv PG_FLG 1

In mod_perl code:

sub is_pure_pg {
    return exists $ENV{PG_FLG};
}

PostgreSQL: rotate pgbouncer logs

Simplest method: switch to syslog

vi /etc/pgbouncer/pgbouncer.ini

; logfile = /var/log/pgbouncer.log
syslog = 1

psql -p6432 -Upostgres -d pgbouncer -c reload

PostgreSQL: split record returned by function into columns

Source: PostgreSQL function or stored procedure that outputs multiple columns?

SELECT a, (func(a)).* FROM table1;

Git: undo push

Source: How to undo (almost) anything with Git

git revert <SHA>