grep extract

Suppose you want 150 lines after the line containing "2016-05-31 11:06:39.265" from file postgresql-31.csv:

grep -A150 '2016-05-31 11:06:39.265' postgresql-31.csv > /tmp/postgresql-31.csv.extract

PostgreSQL: pgq

Ticker uses following rules by default:

1. Batch is made when there are more than 500 new events (ticker_max_count)
2. Batch is made when there are any number of new events and 3 seconds have passed since last batch (ticker_max_lag)
3. Batch is made if there are no new events, but 1 minute has passed since last batch (ticker_idle_period)

https://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/
Show pgq status

crmmbqt=# select * from pgq.get_consumer_info() where queue_name = 'londiste3_queue';
┌─────────────────┬───────────────────┬─────────────────┬─────────────────┬───────────┬───────────────┬───────────┬────────────────┐
│   queue_name    │   consumer_name   │       lag       │    last_seen    │ last_tick │ current_batch │ next_tick │ pending_events │
├─────────────────┼───────────────────┼─────────────────┼─────────────────┼───────────┼───────────────┼───────────┼────────────────┤
│ londiste3_queue │ .global_watermark │ 02:12:20.5365   │ 00:01:16.560372 │     14376 │          NULL │      NULL │          13333 │
│ londiste3_queue │ londiste3_slave   │ 00:04:48.404077 │ 00:00:00.014105 │     14630 │       5070555 │     14631 │            252 │
│ londiste3_queue │ .slave.watermark  │ 02:09:20.494378 │ 00:00:07.537384 │     14381 │          NULL │      NULL │          10910 │
└─────────────────┴───────────────────┴─────────────────┴─────────────────┴───────────┴───────────────┴───────────┴────────────────┘
(3 rows)

PostgreSQL: deleting duplicates example

delete from resource_db.ht_sim_party where sim_party_id in (
 select sim_party_id from (
  select sim_party_id, sim_id, party_id, rnum from (
   select distinct sim_party_id, sim_id, party_id, row_number() over (partition by party_id) as rnum from resource_db.ht_sim_party 
   where ht_sim_party.from_date <= localtimestamp(0) and localtimestamp(0) < ht_sim_party.to_date
   and '2016-05-30' < ht_sim_party.from_date
  ) as foo 
  where rnum > 1
 ) as bar
)

PostgreSQL: who is locking my table?

See http://stackoverflow.com/questions/17605511/why-would-alter-table-drop-constraint-on-an-empty-table-take-a-long-time

select 
  c.relname,
  l.*,
  psa.*
from pg_locks l
inner join pg_stat_activity psa ON (psa.pid = l.pid)
left outer join pg_class c ON (l.relation = c.oid)
where l.relation = 'prov_db.ht_request_status'::regclass;

PostgreSQL exception stack trace

http://dba.stackexchange.com/questions/96743/how-to-get-exception-context-for-a-manually-raised-exception-in-pl-pgsql

PostgreSQL: xmin / xmax explanation

http://blog.lerner.co.il/in-postgresql-as-in-life-dont-wait-too-long-to-commit/

PostgreSQL: size of a table

select pg_size_pretty(pg_relation_size('foo'));

PostgreSQL: Passing an array of record to a stored procedure

create type my_type as (val1 int, val2 int);

create function my_function(arr my_type[]) returns text language plpgsql as
$$
begin
  return arr::text;
end;
$$;

select my_function(array[row(1,2)::my_type, row(3,4)::my_type]);

    my_function
-------------------
 {"(1,2)","(3,4)"}
(1 row)

PostgreSQL: join opposite

select
t1.*
from table1 t1
left join table2 t2 on t1.id=t2.id
where t2.id is null;

PostgreSQL: select ip range with max masklen

            with
            radius_cdr_pdp(sgsn_address, cdr_src_id) as (
                values ('212.183.144.0'::inet, 1), ('212.183.144.0'::inet, 2), (null, 3)
            ),
            dt_ip_range(ip_range) as (
                values ('212.183.144.0/16'::inet), ('212.183.144.0/24'::inet), ('212.182.0.0/16'::inet), (null)
            ),
            radius_cdr_pdp_max as (
                select max(masklen(dt_ip_range.ip_range)) as max_masklen, cdr_src_id
                from radius_cdr_pdp
                left join dt_ip_range on radius_cdr_pdp.sgsn_address <<= dt_ip_range.ip_range
                group by cdr_src_id
            )
            select radius_cdr_pdp.sgsn_address, dt_ip_range.ip_range, radius_cdr_pdp.cdr_src_id, radius_cdr_pdp_max.max_masklen
            from radius_cdr_pdp
            join radius_cdr_pdp_max using (cdr_src_id)
            left join dt_ip_range
                on radius_cdr_pdp.sgsn_address <<= dt_ip_range.ip_range
                and radius_cdr_pdp_max.max_masklen = masklen(dt_ip_range.ip_range);

PostgreSQL: Create temp table from values

WITH  temp (k,v) AS (VALUES (0,-9999), (1, 100)) SELECT * FROM temp;

PostgreSQL: check element contained in array

Contained:

select 1 = any (array[2, 4, 6]);

NOT contained:

select 1 != all (array[2, 4, 6]);

perl: append line on top of file

perl -pi -e 'print "use \\MBQT\\framework\\Session;\n" if $. == 1' <file>

PostgreSQL: get / set sequence value

Get latest sequence value:

select last_value from identity_id_seq;

Set sequence:

select setval('identity_id_seq', 2000041);

PostgreSQL: Convert table into array

See http://stackoverflow.com/questions/32647297/convert-table-into-array-of-custom-types
Any PostgreSQL table has a virtual column named as table of record type with fields related to table's columns.
E.g.
_param_array := array(select foo from prov_db.get_rz_addon_param_list(i_co_id) as foo);

Linux: Display the top ten running processes sorted by memory usage

ps auxw --sort rss | head

PostgreSQL: execute plpgsql block

do
$$
begin
raise notice 'merde';
end
$$;

PostgreSQL: any (values) syntax

select ht_sim_profile_mno.*
from resource_db.ht_sim_profile_mno join resource_db.dt_host_mno using (host_mno_id)
where host_mno_name = any (values ('MBQT'), ('ORANGE'));

PostgreSQL: setup pgbouncer on CentOS

https://opensourcedbms.com/dbms/setup-pgbouncer-connection-pooling-for-postgresql-on-centosredhatfedora/

PostgreSQL: Materialized View Strategies

https://hashrocket.com/blog/posts/materialized-view-strategies-using-postgresql