Web-UI view showing last-refresh times of materialized view / other data

[gstuder-ona]

It'd be possible to generate an up-to-date database-level summary (and corresponding slice) of last-updated times of different named views by wrapping the update commands in some stored procedure magic. These could then be visualized in a simple admin/monitoring UI page, which would let us and our clients see at-a-glance how fresh their data was.

The steps would be 1) wrap our custom view refresh logic in NiFi in a special "time_statement()" postgres function , 2) create a slice around the timing metadata table stored by time_statement(), 3) create a small UI for that slice.

[moshthepitt]

@gstuder-ona what are your thoughts on a hackier, but simpler alternatively: add a timestamp column to the records of the view that is populated using something like NOW();

pros

• really simple
• web app does not need to make another trip to Superset to get this info

cons

• hacky :)
• we have an extra, identical, field for all records in the view. (Although I note that views don't have to care about normalized data)

[gstuder-ona]

@moshthepitt I would totally go that way, but from my internetting it seems like that would basically disable the ability to partially refresh the views. I'm not sure we care though.

[gstuder-ona]

@moshthepitt for the record, I consider an SP wrapper to be a hacky solution as well :-), it's just lighter-touch for the views themselves. Honestly it's pretty annoying that postgres doesn't provide this out of the box - though this seems close?:

https://www.postgresql.org/docs/9.4/pgstatstatements.html

Nice part about our own SP is that it would also let us potentially specify what we considered a "normal" time right next to the statement we're executing (SELECT from time_statement("sql sql", 5000 /ms*/)) and store this in our timing data too - this prevents us from having threshold logic all over the place. Otherwise honestly pgstatstatements seems like the better, same thing.

It didn't seem like we were using it when I tried, but could be missing it.

[moshthepitt]

@gstuder-ona I vote for pgstatstatements

reveal1=> select * from pg_stat_activity where usename = 'canopy' and query = 'REFRESH MATERIALIZED VIEW CONCURRENTLY zambia_irs_jurisdictions' limit 100;
-[ RECORD 1 ]----+----------------------------------------------------------------
datid            | 37446
datname          | reveal1
pid              | 17174
usesysid         | 37447
usename          | canopy
application_name | PostgreSQL JDBC Driver
client_addr      | 10.11.2.52
client_hostname  |
client_port      | 44148
backend_start    | 2019-11-15 20:46:42.388051+00
xact_start       |
query_start      | 2019-11-27 14:04:25.896928+00
state_change     | 2019-11-27 14:05:02.912855+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query            | REFRESH MATERIALIZED VIEW CONCURRENTLY zambia_irs_jurisdictions
backend_type     | client backend

^^ I think using queries such as the above I can whip up a quick view/table that the web app can use.

Makes sense?

[moshthepitt]

My bad, the above is not the right query.

[moshthepitt]

Looks like we need to do some AWS configuration before we have access to this.

According to this https://forums.aws.amazon.com/thread.jspa?messageID=548468, we need to edit our AWS parameter group (I think the one named default.postgres10), and change this:

• track_activity_query_size = 2048 (increase maximum length before which queries are truncated)
• pg_stat_statements.track = ALL (track all statements, including those inside stored procedures)

Then afterwards we need to restart out instance. After which we'll need to create the pgstatsstatements extension.

[moshthepitt]

More from here https://onaio.slack.com/archives/C0482AJP6/p1574869490071200 :

PostgreSQL databases each have their own parameter groups which allows you to update its configuration without affecting other instances. We'd probably need to add these variables to this parameter group then terraform apply https://github.com/onaio/terraform-aws-postgresql/blob/master/storage.tf#L32

[gstuder-ona]

@moshthepitt great, thanks for being less lazy than me. This will just be generally helpful.