Clojure: vector 2 map

1 2	(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

1	set -g status-interval 86400

PostgreSQL: Table dependencies via Foreign Keys

Source: How to list tables affected by cascading delete

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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

1	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

1	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

1	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

1	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

1 2 3 4 5 6 7 8

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

1 2 3 4

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

1	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

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

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

1	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

1	with deleted as (delete from <child> returning *) insert into <parent> select * from deleted</parent></child>

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13

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; $$

1 2 3 4 5 6 7 8 9 10 11 12 13

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:

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

Then identify the query:

1	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

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

PostgreSQL: select from values

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

1 2 3 4 5

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:

1 2 3 4 5 6 7 8 9

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:

1	$Data::Dumper::Sortkeys  = 1;

Sort by keys:

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

Sort by values:

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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

1 2 3 4 5

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

Then

1 2 3

crmmbqt=# rollback; ROLLBACK Time: 0.714 ms

PostgreSQL: list biggest tables

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

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

Relation w/ inheritance:

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

1 2 3 4 5 6 7 8 9

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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:

1	PerlSetEnv PG_FLG 1

In mod_perl code:

1 2 3

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

PostgreSQL: rotate pgbouncer logs

Simplest method: switch to syslog

vi /etc/pgbouncer/pgbouncer.ini

1 2	; 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?

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

Git: undo push

Source: How to undo (almost) anything with Git

git revert <SHA>