pawurb / ecto_psql_extras Goto Github PK

I'm adding the issue as placeholder to not to forget the idea and gather feedback.

I'd like to add a way to automatically check the health of your database based on both default and customizable conditions. Something on the lines of https://blog.heroku.com/pg-diagnose

Hi @pawurb! Thanks for the extremely useful repo!

Over the last weeks @alexcastano has been working on custom pages for the Phoenix Live Dashboard. Our main goal is to support something like ecto_psql_extras, but directly from the browser.

Now that the foundational work is done, we are discussing where to integrate the functionality. One approach that came to mind is to add the functionality directly here. Would you be interested in adding support for the Phoenix Live Dashboard in ecto_psql_extras? phoenix_live_dashboard would become an optional dependency. The amount of work necessary is also minimal, you can see the page itself here - where the name of columns could probably be defined alongside each query in this repo and the all possible queries could also be made public.

Please let us know what you think, we will be glad to submit a PR! :)

this package has been integrated by phoenix_live_dashboard and the result is great!

One problem is that returning string data for size makes it harder to sort by size.
here is an example in the code base https://github.com/pawurb/ecto_psql_extras/blob/master/lib/queries/index_size.ex#L10
See phoenixframework/phoenix_live_dashboard#204 for more details.

Would it make sense to return integer data for sizes and let the consumer of this library format those?
Or if you want to be more flexible enable passing a parameter to be able to chose the format? (string or integer?)

Let me know if I'm being too vague.

PostgreSQL 13 has removed total_time from pg_stat_statements and replaced it with total_exec_time (https://www.postgresql.org/docs/13/pgstatstatements.html), so any queries using total_time are raising exceptions so now the Outliers and Calls queries aren't functioning properly:

Server: localhost:4000 (http)
Request: GET /dashboard/nonode%40nohost/api_repo_info?nav=outliers
** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 42703 (undefined_column) column "total_time" does not exist

    query: /* 10 queries that have longest execution time in aggregate */

SELECT query AS query,
interval '1 millisecond' * total_time AS exec_time,
(total_time/sum(total_time) OVER()) AS prop_exec_time,
calls,
interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
ORDER BY total_time DESC
LIMIT 10;

        (ecto_sql 3.5.1) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_psql_extras 0.3.2) lib/ecto_psql_extras.ex:53: EctoPSQLExtras.query/3

I would submit a PR but I'm not sure how to detect the PostgreSQL version during a query so total_time can still be used for versions 12 and lower.

Heyo 👋 thanks for putting this together.

I have some regular tasks running that query the health of a database using this library, and some of the SQL is pretty long. The result being that every x seconds, all this SQL can get logged. In my case, every 10 seconds I get ~50 lines logged. I'd like to make this unlogged.

Is there a way to configure the call to not log?

For example, EctoPSQLExtras.query(:extensions, YourApp.Repo, log: false)

Right now, it allows arguments to adjust the sql, but not the options to the underlying repo.query.

I'll be happy to contribute a PR.

When working on this PR #31 and running tests locally. I had to connect to each containers database and run CREATE DATABASE ecto_psql_extras. Should I just add a comment to the README.md about having to create the database manually to get the tests to pass? Or did I miss a step?

Elixir 1.10.4 (compiled with Erlang/OTP 21)
Postgres 12.4
pg_stat_statements installed 1.7

cache_hit and locks work fine, but some other requests lead to crash.

iex> EctoPSQLExtras.query(:calls, Repo)
[debug] QUERY OK db=2.7ms queue=1.5ms idle=1490.3ms
/* 10 queries that have the highest frequency of execution */

SELECT query AS qry,
interval '1 millisecond' * total_time AS exec_time,
to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
to_char(calls, 'FM999G999G990') AS ncalls,
interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
ORDER BY calls DESC
LIMIT 10;
 []
** (ArgumentError) argument error
    (stdlib 3.13.2) :binary.copy(" ", -309)
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:316: TableRex.Renderer.Text.do_render_cell/4
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:288: TableRex.Renderer.Text.render_cell/3
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:277: TableRex.Renderer.Text.render_cell_row/4
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:223: TableRex.Renderer.Text.render_rows/1
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:85: TableRex.Renderer.Text.render/2
    (table_rex 3.0.0) lib/table_rex.ex:26: TableRex.quick_render!/3
    (ecto_psql_extras 0.2.0) lib/ecto_psql_extras.ex:68: EctoPSQLExtras.format/3

iex> EctoPSQLExtras.query(:outliers, Repo)
[debug] QUERY OK db=1.0ms queue=0.9ms idle=1996.8ms
/* 10 queries that have longest execution time in aggregate */

SELECT query AS qry,
interval '1 millisecond' * total_time AS exec_time,
to_char((total_time/sum(total_time) OVER()) * 100, 'FM90D0') || '%'  AS prop_exec_time,
to_char(calls, 'FM999G999G999G990') AS ncalls,
interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time
FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1)
ORDER BY total_time DESC
LIMIT 10;
 []
** (ArgumentError) argument error
    (stdlib 3.13.2) :binary.copy(" ", -20)
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:316: TableRex.Renderer.Text.do_render_cell/4
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:288: TableRex.Renderer.Text.render_cell/3
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:277: TableRex.Renderer.Text.render_cell_row/4
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:223: TableRex.Renderer.Text.render_rows/1
    (table_rex 3.0.0) lib/table_rex/renderer/text.ex:85: TableRex.Renderer.Text.render/2
    (table_rex 3.0.0) lib/table_rex.ex:26: TableRex.quick_render!/3
    (ecto_psql_extras 0.2.0) lib/ecto_psql_extras.ex:68: EctoPSQLExtras.format/3

Queries work fine, they return results. Seems the problem is in formatting?

While working on:

• #46

I've noticed changes we could bring the CI:

• Ubuntu 18 is EOL so we could move to the latest LTS
• Maybe change "Install postgrex 0.17 (except on elixir 1.7.4)" to "Install latest Posgrex" in all cases?

Just ideas at this point, let me know what you think!