Messagelog Database info

https://moodle.ria.ee/mod/page/view.php?id=694

psql -h 127.0.0.1 -U messagelog <password from /etc/xroad/db.properties>

Kui palju kirjeid on tabelis

select count( *) from logrecord;

Milline on kõige vanem sõnumilogi kirje baasis

select to_timestamp(min( time )::float/1000) from logrecord;

Kui palju on ajatembedamata sõnumeid

select count(1) from logrecord where discriminator::text = 'm'::text and signaturehash is not null;

Viimane ajatembeldamata sõnum

select to_timestamp(min(time)::float/1000) from logrecord where discriminator::text = 'm'::text and signaturehash is not null;

Kui palju on ajatembeldatud aga arhiveerimata sõnumeid

select count(1) from logrecord where timestamprecord in (select id from logrecord where discriminator::text = 't'::text and archived = false);

Viimane ajatembeldatud aga arhiveerimata sõnum

select to_timestamp(min(time)::float/1000) from logrecord where timestamprecord in (select id from logrecord where discriminator::text = 't'::text and archived = false);

Size of the largest row in logrecord table

select t.id, t.archived, (pg_column_size(t.message)) as size from logrecord t where message IS NOT NULL order by size desc;