Wednesday, April 26, 2017

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

Monday, April 24, 2017

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

Tuesday, April 11, 2017

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]} ] };

Monday, April 3, 2017

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

Tuesday, March 21, 2017

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)

Monday, March 20, 2017

Linux: top ten biggest files

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

Friday, March 17, 2017

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;

Thursday, March 16, 2017

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

Tuesday, February 14, 2017

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;

Monday, February 6, 2017

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

Wednesday, February 1, 2017

Friday, January 27, 2017

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