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