Powa-web UI displays "Error while calling public.powa_kcache_src"

[hrawulwa]

I have deployed remote setup and registered one remote DB server in the repository. Versions: Powa archivisit_4_1_2, powa-collector-1.1.1 and powa-web-4.1.1 The UI displays below error: Error while calling public.powa_kcache_src: structure of query does not match function result type DETAIL: Returned type text does not match expected type bigint in column 6. CONTEXT: PL/pgSQL function powa_kcache_src(integer) line 33 at RETURN QUERY The General overview and System resources show no data. See attached image. All the extensions are in place in the remote DB server. postgres=# \dx List of installed extensions Name | Version | Schema | Description
--------------------+-----------+------------+-------------------------------------------------------------- btree_gist | 1.5 | public | support for indexing common datatypes in GiST dblink | 1.2 | public | connect to other PostgreSQL databases from within a database hypopg | 2.0.0beta | public | Hypothetical advisor for PostgreSQL pg_profile | 0.0.7 | public | PostgreSQL load profile repository and report builder pg_qualstats | 1.0.7 | public | An extension collecting statistics about quals pg_repack | 1.4.5 | public | Reorganize tables in PostgreSQL databases with minimal locks 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 pgfincore | 1.2 | public | examine and manage the os buffer cache pgstattuple | 1.5 | public | show tuple-level statistics plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language powa | 4.1.2 | public | PostgreSQL Workload Analyser-core (13 rows)

postgres=# show shared_preload_libraries; shared_preload_libraries

pg_stat_statements,auto_explain,pg_qualstats,hypopg,pgstattuple,pg_stat_kcache,pgfincore,pg_prewarm,pg_wait_sampling,pg_buffercache,pg_repack,btree_gist,pglogical (1 row)

What could be problem here?

Thanks Hari

[hrawulwa]

[banlex73]

I would check the version of extension pg_kcache on target and on powa repository should match (?) and be the latest available

вт, 23 бер. 2021 о 07:22 hrawulwa @.***> пише:

[image: powa_kcache_src_error] https://user-images.githubusercontent.com/65568835/112161568-46ebc800-8bb9-11eb-8143-4d8011803529.PNG

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/135#issuecomment-804943830, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYDCD4ZZMOS7VAMUV4LTFCP4BANCNFSM4ZVI6KZQ .

[hrawulwa]

In the remote setup, all the extensions will go on the target server. The repository server only has powa extension (4.1.2). I have similar setup in non-prod, but with older version of powa (4.0.1) and works fine. I'm not sure if I need to reinstall latest version of pg_stat_kcache on remote server. The current version there is 2.1.1

Thanks Hari

[banlex73]

I would try to install the latest pg_stat_kcache | 2.2.0 on remote server. but I suspect that it will require PG cluster restart.

вт, 23 бер. 2021 о 09:08 hrawulwa @.***> пише:

In the remote setup, all the extensions will go on the target server. The repository server only has powa extension (4.1.2). I have similar setup in non-prod, but with older version of powa (4.0.1) and works fine. I'm not sure if I need to reinstall latest version of pg_stat_kcache on remote server. The current version there is 2.1.1

Thanks Hari

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/135#issuecomment-805029810, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYBZEOR7UEYOZJXVOKLTFC4G5ANCNFSM4ZVI6KZQ .

[hrawulwa]

Since pg_stat_kcache is already present in shared_preload_libraries, I went ahead and tried to reinstall the latest version. However, in psql, when trying to create extension, fails. Here are steps I followed:

postgres=# drop extension pg_stat_kcache; DROP EXTENSION

tar -xvzf /tmp/pg_stat_kcache-REL2_2_0.tar.gz cd pg_stat_kcache-REL2_2_0 [postgres@sl73togadbp001 pg_stat_kcache-REL2_2_0]$ make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pgbin/tog1p/11.7/include/postgresql/server -I/pgbin/tog1p/11.7/include/postgresql/internal -D_GNU_SOURCE -I/usr/local/include/libxml2 -c -o pg_stat_kcache.o pg_stat_kcache.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_stat_kcache.so pg_stat_kcache.o -L/pgbin/tog1p/11.7/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/pgbin/tog1p/11.7/lib',--enable-new-dtags

[postgres@sl73togadbp001 pg_stat_kcache-REL2_2_0]$ make install /usr/bin/mkdir -p '/pgbin/tog1p/11.7/lib/postgresql' /usr/bin/mkdir -p '/pgbin/tog1p/11.7/share/postgresql/extension' /usr/bin/mkdir -p '/pgbin/tog1p/11.7/share/postgresql/extension' /usr/bin/install -c -m 755 pg_stat_kcache.so '/pgbin/tog1p/11.7/lib/postgresql/pg_stat_kcache.so' /usr/bin/install -c -m 644 .//pg_stat_kcache.control '/pgbin/tog1p/11.7/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_stat_kcache--2.1.1.sql .//pg_stat_kcache--2.2.0.sql .//pg_stat_kcache--2.1.2--2.1.3.sql .//pg_stat_kcache--2.1.0.sql .//pg_stat_kcache--2.1.1--2.1.2.sql .//pg_stat_kcache--2.1.0--2.1.1.sql .//pg_stat_kcache--2.1.3--2.2.0.sql .//pg_stat_kcache--2.1.2.sql .//pg_stat_kcache--2.1.3.sql '/pgbin/tog1p/11.7/share/postgresql/extension/'

--Now when trying to create extension fails: postgres=# create extension pg_stat_kcache; ERROR: could not find function "pg_stat_kcache_2_2" in file "/pgbin/tog1p/11.7/lib/postgresql/pg_stat_kcache.so"

Please advise.

Thanks Hari

[banlex73]

100% you have to restart Postgres ERROR: could not find function "pg_stat_kcache_2_2 I had the same issue before

вт, 23 бер. 2021 о 09:48 hrawulwa @.***> пише:

Since pg_stat_kcache is already present in shared_preload_libraries, I went ahead and tried to reinstall the latest version. However, in psql, when trying to create extension, fails. Here are steps I followed:

postgres=# drop extension pg_stat_kcache; DROP EXTENSION

tar -xvzf /tmp/pg_stat_kcache-REL2_2_0.tar.gz cd pg_stat_kcache-REL2_2_0 @.*** pg_stat_kcache-REL2_2_0]$ make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/pgbin/tog1p/11.7/include/postgresql/server -I/pgbin/tog1p/11.7/include/postgresql/internal -D_GNU_SOURCE -I/usr/local/include/libxml2 -c -o pg_stat_kcache.o pg_stat_kcache.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_stat_kcache.so pg_stat_kcache.o -L/pgbin/tog1p/11.7/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/pgbin/tog1p/11.7/lib',--enable-new-dtags

@.*** pg_stat_kcache-REL2_2_0]$ make install /usr/bin/mkdir -p '/pgbin/tog1p/11.7/lib/postgresql' /usr/bin/mkdir -p '/pgbin/tog1p/11.7/share/postgresql/extension' /usr/bin/mkdir -p '/pgbin/tog1p/11.7/share/postgresql/extension' /usr/bin/install -c -m 755 pg_stat_kcache.so '/pgbin/tog1p/11.7/lib/postgresql/pg_stat_kcache.so' /usr/bin/install -c -m 644 .//pg_stat_kcache.control '/pgbin/tog1p/11.7/share/postgresql/extension/' /usr/bin/install -c -m 644 .//pg_stat_kcache--2.1.1.sql .//pg_stat_kcache--2.2.0.sql .//pg_stat_kcache--2.1.2--2.1.3.sql .//pg_stat_kcache--2.1.0.sql .//pg_stat_kcache--2.1.1--2.1.2.sql .//pg_stat_kcache--2.1.0--2.1.1.sql .//pg_stat_kcache--2.1.3--2.2.0.sql .//pg_stat_kcache--2.1.2.sql .//pg_stat_kcache--2.1.3.sql '/pgbin/tog1p/11.7/share/postgresql/extension/'

--Now when trying to create extension fails: postgres=# create extension pg_stat_kcache; ERROR: could not find function "pg_stat_kcache_2_2" in file "/pgbin/tog1p/11.7/lib/postgresql/pg_stat_kcache.so"

Please advise.

Thanks Hari

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/135#issuecomment-805060281, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYDDC3YUQ4ARNAEZWVDTFDA7HANCNFSM4ZVI6KZQ .

[hrawulwa]

Ok. let me try on any non-prod server with the same setup and confirm back.

Thanks Hari

[rjuju]

Hello @hrawulwa , Yes I confirm what @banlex73 you need to restart postgres to update pg_stat_kcache, or at least if you want to update the definition of the SQL objects, so that error is expected (and required).

That being said powa should take care of handling backward compatibility of the extensions. Did you update powa-archivist to the lastest version on your remote server? This should be enough to fix your issue, and updating powa-archivist should not require a restart anymore since version 4.

[hrawulwa]

Yes, I had updated powa-archivist to the latest version (4.1.2) on the remote server, to match with the repository server. However, pg_stat_kcache of 2.1.1 version was not compatible and the UI threw the error I reported. The error went away only after reinstalling 2.2.0 version and restarting the DB server.

Thanks Hari

[rjuju]

@hrawulwa Oh, it turns out that this error comes from the backward compatibility code in powa-archivist.

For some reason the extension creation script doesn't complain about the type mismatch (simply returning NULL doesn't have a defined datatype and postgres falls back to text, which can't be automatically casted to bigint, thus the error) at extension creation time but only at runtime, and it seems that I missed something when testing this code when I initially wrote it :(

Thanks a lot for the report, that's a problematic bug that will be fixed in the next version of powa-archivist!

[rjuju]

I just pushed a fix at https://github.com/powa-team/powa-archivist/commit/699d8b73c62728802a2c91c359b52aae6a60bc59. Thanks again for reporting the problem!