Tuesday, May 31, 2016

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
)

Friday, May 27, 2016

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

Saturday, May 14, 2016

PostgreSQL: size of a table

select pg_size_pretty(pg_relation_size('foo'));

Thursday, May 12, 2016

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;

Tuesday, May 3, 2016

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;