postgresql_shortcut.sql

--------------------------------- Process
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT pg_cancel_backend(<pid of the process>)
SELECT pg_terminate_backend(<pid of the process>)

--------------------------------- LOCKS
Ref: https://wiki.postgresql.org/wiki/Lock_Monitoring

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

** mostra todos os locks, mas nem todos são problemas
select * from pg_locks

** ver quais queries estão bloqueadas e por qual processo
select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
                                   
--------------------------------- Show definitions

-- Views
select pg_get_viewdef('xxx_view'::regclass, true)

-- Tables
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'trips_view';


---------------------------------
SHOW TRANSACTION ISOLATION LEVEL

---------------------------------- Sub-query para pegar último registro

SELECT * 
FROM table a 
JOIN (SELECT ID, max(date) maxDate
        FROM table
      GROUP BY ID) b
ON a.ID = b.ID AND a.date = b.maxDate


---------------------------------- Distinct ON

SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC

----------------------------------


SELECT datname, temp_files AS "Temporary files",pg_size_pretty(temp_bytes) AS "Size of temporary files" FROM pg_stat_database ;

----------------------------------
# verificar o tamanho dos bancos de dados                                

select 
current_database() as database,
pg_size_pretty(total_database_size) as total_database_size,
schema_name,
table_name,
pg_size_pretty(total_table_size) as total_table_size_format,
pg_size_pretty(table_size) as table_size,
pg_size_pretty(index_size) as index_size
from ( select table_name,
        table_schema as schema_name,
        pg_database_size(current_database()) as total_database_size,
        pg_total_relation_size(table_schema||'.'||table_name) as total_table_size,
        pg_relation_size(table_schema||'.'||table_name) as table_size,
        pg_indexes_size(table_schema||'.'||table_name) as index_size
        from information_schema.tables
        order by total_table_size
    ) as sizes
order by total_table_size desc

---------------------------------- Contagem de horas úteis

select count(*) from ( select dd, extract(DOW from dd) dw, extract(hour from dd) as dh
    from generate_series(signed_contract_at, invoice_sent_at, '1 hour'::interval) dd ) as valid_hours
    where dw not in (6,0) and dh not in (22, 23, 0, 1, 2, 3, 4, 5, 6, 7) ) as SLA_time_SendInvoice
    , CASE 
        WHEN date_trunc('day', invoice_sent_at) = date_trunc('day', signed_contract_at)
        THEN extract (epoch from invoice_sent_at - signed_contract_at)/3600
        WHEN date_trunc('day', invoice_sent_at) != date_trunc('day', signed_contract_at)
        THEN (extract (hours from invoice_sent_at) - 7) +  extract (minutes from invoice_sent_at)::float/60  +
        (21 - extract (hours from signed_contract_at)) - extract (minutes from signed_contract_at)::float/60
    END as time_to_send_invoice_HM