UndefinedTable for powa_stat_bgwriter_history and powa_all_relations_history_db with PoWA 4.0.0beta1

pessonnier commented 4 years ago

hello, I use

postgreSQL 10.12
PoWA 4.0.0beta1 compiled from the github tag
powa-web 4.0.0 from https://pypi.io/packages/source/p/powa-web/powa-web-4.0.0.tar.gz

and the following extensions

                                   List of installed extensions
    Name        |  Version   |   Schema   |                        Description                        
--------------------+------------+------------+-----------------------------------------------------------
btree_gist         | 1.5        | public     | support for indexing common datatypes in GiST
hypopg             | 1.1.3      | public     | Hypothetical indexes for PostgreSQL
pg_qualstats       | 2.0.1      | public     | An extension collecting statistics about quals
pg_stat_kcache     | 2.1.1      | public     | Kernel statistics gathering
pg_stat_statements | 1.6        | public     | track execution statistics of all SQL statements executed
pg_wait_sampling   | 1.1        | public     | sampling based statistics of wait events
plpgsql            | 1.0        | pg_catalog | PL/pgSQL procedural language
powa               | 4.0.0beta1 | public     | PostgreSQL Workload Analyser-core
(8 rows)

then, while using powa-web, I obtain the following errors in the log

[E 200429 10:56:57 web:1792] Uncaught exception GET /server/0/metrics/bgwriter/?from=2020-04-29+09%3A56%3A56%2B0200&to=2020-04-29+10%3A56%3A56%2B0200 (127.0.0.1)
    HTTPServerRequest(protocol='http', host='localhost:8888', method='GET', uri='/server/0/metrics/bgwriter/?from=2020-04-29+09%3A56%3A56%2B0200&to=2020-04-29+10%3A56%3A56%2B0200', version='HTTP/1.1', remote_ip='127.0.0.1')
    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
        cursor, statement, parameters, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
        cursor.execute(statement, parameters)
    psycopg2.errors.UndefinedTable: relation "powa_stat_bgwriter_history" does not exist
    LINE 22:           FROM powa_stat_bgwriter_history bgwh
                            ^

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/dist-packages/tornado/web.py", line 1701, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/home/loic/prog/powa-web-4.0.0/powa/dashboards.py", line 95, in get
        values = self.execute(query, params=url_params)
      File "/home/loic/prog/powa-web-4.0.0/powa/framework.py", line 343, in execute
        return engine.execute(query, **params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 2191, in execute
        return connection.execute(statement, *multiparams, **params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 984, in execute
        return meth(self, multiparams, params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1103, in _execute_clauseelement
        distilled_params,
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
        e, statement, parameters, cursor, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
        sqlalchemy_exception, with_traceback=exc_info[2], from_=e
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
        raise exception
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
        cursor, statement, parameters, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "powa_stat_bgwriter_history" does not exist
    LINE 22:           FROM powa_stat_bgwriter_history bgwh                           ^

[E 200429 10:57:07 web:1792] Uncaught exception GET /server/0/metrics/database_all_relations/powa/?from=2020-04-29+09%3A57%3A07%2B0200&to=2020-04-29+10%3A57%3A07%2B0200 (127.0.0.1)
    HTTPServerRequest(protocol='http', host='localhost:8888', method='GET', uri='/server/0/metrics/database_all_relations/powa/?from=2020-04-29+09%3A57%3A07%2B0200&to=2020-04-29+10%3A57%3A07%2B0200', version='HTTP/1.1', remote_ip='127.0.0.1')
    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
        cursor, statement, parameters, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
        cursor.execute(statement, parameters)
    psycopg2.errors.UndefinedTable: relation "powa_all_relations_history_db" does not exist
    LINE 17:           FROM powa_all_relations_history_db arh
                            ^

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/dist-packages/tornado/web.py", line 1701, in _execute
        result = method(*self.path_args, **self.path_kwargs)
      File "/home/loic/prog/powa-web-4.0.0/powa/dashboards.py", line 95, in get
        values = self.execute(query, params=url_params)
      File "/home/loic/prog/powa-web-4.0.0/powa/framework.py", line 343, in execute
        return engine.execute(query, **params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 2191, in execute
        return connection.execute(statement, *multiparams, **params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 984, in execute
        return meth(self, multiparams, params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1103, in _execute_clauseelement
        distilled_params,
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
        e, statement, parameters, cursor, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
        sqlalchemy_exception, with_traceback=exc_info[2], from_=e
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
        raise exception
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
        cursor, statement, parameters, context
      File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
        cursor.execute(statement, parameters)
    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "powa_all_relations_history_db" does not exist
    LINE 17:           FROM powa_all_relations_history_db arh                            ^

and this one, on the bottom of the web page

Index suggestions
Impossible to suggest indexes: impossible to connect to the remote database.
(psycopg2.errors.UndefinedColumn) column "allow_ui_connection" does not exist LINE 2: SELECT allow_ui_connection ^ [SQL: SELECT allow_ui_connection FROM powa_servers WHERE id = 0 ] (Background on this error at: http://sqlalche.me/e/f405)

i have the same errors with python 2.7 and 3.6 i can't use PoWA 4.0.0 because of isuue powa-team/powa-archivist#23

what did i missed ?

rjuju commented 4 years ago

Hello,

Indeed you're using an older version of powa-archivist that's not supported with the latest UI. However the released version of powa-archivist 4.0.0 should have the fix for https://github.com/powa-team/powa-archivist/issues/23 and will work with powa-web 4.0.0. Do you still hit some issue when using the released version of powa-archivist?

pessonnier commented 4 years ago

if I use powa REL_4_0_0 from https://github.com/powa-team/powa-archivist/archive/REL_4_0_0.tar.gz i got a

ERROR:  "res.seq_can_per_sec" is not a known variable
LINE 12:     res.seq_can_per_sec = (a.seq_scan - b.seq_scan)::double ...
             ^

at create extention powa;

so I edited powa--4.0.0.sql and add the two missing 's' line 584 and 585

    res.seq_can_per_sec = (a.seq_scan - b.seq_scan)::double precision / sec;
    res.idx_can_per_sec = (a.idx_scan - b.idx_scan)::double precision / sec;

then everything gone well

I check the github repository, the error is present in HEAD and tag REL_4_0_0

should I post this issue in powa-archivist ?

rjuju commented 4 years ago

Oh indeed, that's another issue, good catch! If you, please can post a new issue in powa-archivist, I'll take care of that soon, thanks a lot!

rjuju commented 4 years ago

FTR the failure only happens up to pg10, newer versions apparently don't try to validate all source code. I'll have to remember that to avoid more brown paper bag bugs.

rjuju commented 4 years ago

I'm closing this issue as this is handled in https://github.com/powa-team/powa-archivist/issues/30.

powa-team / powa-web

UndefinedTable for powa_stat_bgwriter_history and powa_all_relations_history_db with PoWA 4.0.0beta1 #98