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