# Messagelog Database info

[https://moodle.ria.ee/mod/page/view.php?id=694](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;
```