search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 31 forks source link

Widget does not load in Index suggestion page - PoWA 4 #100

Closed hrawulwa closed 4 years ago

hrawulwa commented 4 years ago

The index suggestion widget loads fine in the local setup. However, it throws an error when using Remote setup. On the Repository server, I have Powa-archivist, collector and Powa-web running fine. On the target DB server, I have dedicated "powa" database, where I installed all the extensions (pg_stat_kcache, pg_qualstats, pg_wait_sampling, hypopg and powa). I just created the hypopg extension in the application database where I'm testing my queries. After clicking on the Predicates page for the query, the widget does not load. Attached the image., widget_error

rjuju commented 4 years ago

Hi,

Oh, I thought that I made sure that all errors would be reported on the widget to help diagnosis.

Which versions of powa-web, powa-archivist and pg_qualstats are you using, on both the repository and remote servers?

Do you see any error in the postgres logs of the target remote server? Could you also check in the browser developer tool if there are more information about failed http queries?

hrawulwa commented 4 years ago

Installed the packages from sources. Powa-archivist from powa-archivist-REL_4_0_0.tar.gz pg_qualstats from pg_qualstats-1.0.7.tar.gz Powa-web from powa-web-4.0.0.tar.gz

I can see below errors in the repository server. log file. This error comes when I try to navigate to Predicates section for a given query.

ProgrammingError: (ProgrammingError) function

pg_qualstats_example_query(bigint) does not exist LINE 2: SELECT query, pg_qualstats_example_query(queryid... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. '\n SELECT query, pg_qualstats_example_query(queryid)\n FROM powa_statements\n WHERE queryid = %(queryid)s\n LIMIT 1\n ' {'queryid': u'-1617549918440988575'}

Below are the extensions on the target server, in both powa database and the application database. I thought above error would go away if I create pg_qualstats extension in the application database, as well, apart from powa database.

[postgres@sl73gseldbd214 pg_qualstats-1.0.7]$ psql -d powa psql (11.2) Type "help" for help.

powa=# \dx 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 | 1.0.7 | 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.0 | public | PostgreSQL Workload Analyser-core (8 rows)

powa=# exit [postgres@sl73gseldbd214 pg_qualstats-1.0.7]$ psql -d gsel214db01 psql (11.2) Type "help" for help.

gsel214db01=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- hypopg | 1.1.3 | public | Hypothetical indexes for PostgreSQL pg_qualstats | 1.0.7 | public | An extension collecting statistics about quals pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows)

Thannks Hari

On Fri, May 22, 2020 at 12:28 PM Julien Rouhaud notifications@github.com wrote:

Hi,

Oh, I thought that I made sure that all errors would be reported on the widget to help diagnosis.

Which versions of powa-web, powa-archivist and pg_qualstats are you using, on both the repository and remote servers?

Do you see any error in the postgres logs of the target remote server? Could you also check in the browser developer tool if there are more information about failed http queries?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/100#issuecomment-632819123, or unsubscribe https://github.com/notifications/unsubscribe-auth/APUIAQ2FYHARY2DZG3ECNKDRS2Y3FANCNFSM4NIASJWA .

rjuju commented 4 years ago

Oh, I think I actually missed to update the part of the code that tries to fetch and de-normalize queries to be compatible with the new v4 architecture. I'll fix that after additional testing to make it's working as expected.

Thanks a lot for the report!

hrawulwa commented 4 years ago

Ok, thank you. I'm however wondering how come this is working in the Local setup, using the same versions. The only difference is that local server has tornado 3 version, whereas, in the remote setup, the Repository server has the tornado 4 version. I had to use tornado 4 on the repository, as the powa-web was not launching and failing with error.

Thanks Hari

rjuju commented 4 years ago

Indeed, the code was still able to work on a local setup, but had various issues when remote mode is used. I just pushed 624a0f695a01119cc13e7048da3a431e31dac530 which should fix your problem, and a few other flaws I spotted.

Let me know if that also fixes the issue for you!

hrawulwa commented 4 years ago

Great, this woked. Thanks for the fix. Will you also be merging this code to the source tar at https://github.com/powa-team/powa-web/powa-web-4.0.0.tar.gz? I see another problem which pops up randomly, but this error goes away when the next snapshot is taken by the collector. powa_take_snapshot(4): function "powa_qualstats_snapshot" failed: insert or update on table "powa_qualstats_quals" violates foreign key constraint "powa_qualstats_quals_srvid_fkey"

Is there any way I can avoid this error, or any steps I need to take, for this error to not show up again?

Thanks Hari

Thanks Hari

On Sun, May 24, 2020 at 2:59 AM Julien Rouhaud notifications@github.com wrote:

Indeed, the code was still able to work on a local setup, but had various issues when remote mode is used. I just pushed 624a0f6 https://github.com/powa-team/powa-web/commit/624a0f695a01119cc13e7048da3a431e31dac530 which should fix your problem, and a few other flaws I spotted.

Let me know if that also fixes the issue for you!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/100#issuecomment-633194649, or unsubscribe https://github.com/notifications/unsubscribe-auth/APUIAQ753URTSD6XCEPCMVLRTDHVPANCNFSM4NIASJWA .

rjuju commented 4 years ago

Greats, thanks for the confirmation.

I'll release a new version soon, so you'll only have to get the 4.0.1 tgz archive, or any similar installation source.

For your other issue with pg_qualstats, I also see it from time to time. I checked the code multiple times and didn't find any issue. This is I believe only an consequence of the various stats being gathered from shared memory rather than from some tables, so no ACID isolation is possible. I tried my best to ensure that the various datasources are processed in the right order, but it's always possible to have a new query having a new predicate being executed at the wrong timing, leading to this issue. This is unfortunate but not a blocker, as it'll resolve automatically during the next snapshot.

hrawulwa commented 4 years ago

Great, thanks for the update and the clarification on the pg_qualstats error.

Thanks Hari

On Tue, May 26, 2020 at 8:15 AM Julien Rouhaud notifications@github.com wrote:

Greats, thanks for the confirmation.

I'll release a new version soon, so you'll only have to get the 4.0.1 tgz archive, or any similar installation source.

For your other issue with pg_qualstats, I also see it from time to time. I checked the code multiple times and didn't find any issue. This is I believe only an consequence of the various stats being gathered from shared memory rather than from some tables, so no ACID isolation is possible. I tried my best to ensure that the various datasources are processed in the right order, but it's always possible to have a new query having a new predicate being executed at the wrong timing, leading to this issue. This is unfortunate but not a blocker, as it'll resolve automatically during the next snapshot.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/100#issuecomment-634017574, or unsubscribe https://github.com/notifications/unsubscribe-auth/APUIAQYTFCFHVD4YZ4ZOXC3RTO6HVANCNFSM4NIASJWA .

rjuju commented 4 years ago

FTR version 4.0.1 has been released. Thanks a lot for the report!