adjust/pg-telemetry

Useful monitoring views for PostgreSQL, packaged as an extension

pg-telemetry's Issues

Enhancement: More granular waiting metrics

Currently we just show wait_event_type and break out locks separately.

However in some cases we see a lot of lightweight locks with no specifics on what is causing them. This is critical information and would be very helpful.

Support for PG14

Since some fields of pg_stat_statements were changed in PG13, pgtelemetry installation needs to adjust according to the version.

wal_telemetry does not work on write slave

Error is that SELECT query cannot be run on slave.

Problem is because the current function is written as an SQL function which means the whole thing is planned at once so even though the insert never gets run it still gets blacklisted. The correct solution is probably to move it into plpgsql where we would retrieve the current row using dynamic SQL.

make installcheck fails

Hi,

Thanks for making pg-telemetry.

It looks like there is a problem with the tests:

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 20.04.2 LTS
Release:	20.04
Codename:	focal

$ psql --version
psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

$ make installcheck
/usr/lib/postgresql/13/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/13/bin'    --dbname=contrib_regression definitions
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test definitions                  ... FAILED       30 ms

======================
1 of 1 tests failed.
======================

The differences that caused some tests to fail can be viewed in the
file "/home/joel/postgresql-extensions/pg-telemetry/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/home/joel/postgresql-extensions/pg-telemetry/regression.out".

make: *** [/usr/lib/postgresql/13/lib/pgxs/src/makefiles/pgxs.mk:420: installcheck] Error 1

$ cat regression.diffs
diff -U3 /home/joel/postgresql-extensions/pg-telemetry/expected/definitions.out /home/joel/postgresql-extensions/pg-telemetry/results/definitions.out
--- /home/joel/postgresql-extensions/pg-telemetry/expected/definitions.out	2021-04-26 07:51:30.455845875 +0000
+++ /home/joel/postgresql-extensions/pg-telemetry/results/definitions.out	2021-04-26 07:51:46.672061123 +0000
@@ -1,116 +1,79 @@
set client_min_messages to warning;
create extension pgtelemetry cascade;
+ERROR:  column "total_time" does not exist
select count(*) > 0 from pgtelemetry.database_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.database_size" does not exist
+LINE 1: select count(*) > 0 from pgtelemetry.database_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.relation_total_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.relation_total_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.relation_total_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.catalog_total_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.catalog_total_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.catalog_total_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.index_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.index_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.index_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.relation_toast_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.relation_toast_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.relation_toast_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.tablespace_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.tablespace_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.tablespace_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.database_size;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.database_size" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.database_size;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.connections_by_application;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.connections_by_application" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.connections_by_applicat...
+                                 ^
SELECT count(*) > 0 from pgtelemetry.connections_by_state;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.connections_by_state" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.connections_by_state;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.connections_by_ip_source;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.connections_by_ip_source" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.connections_by_ip_sourc...
+                                 ^
SELECT count(*) > 0 from pgtelemetry.longest_running_active_queries;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.longest_running_active_queries" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.longest_running_active_...
+                                 ^
SELECT count(*) > -1 from pgtelemetry.waiting_connections_by_event_type;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.waiting_connections_by_event_type" does not exist
+LINE 1: SELECT count(*) > -1 from pgtelemetry.waiting_connections_by...
+                                  ^
SELECT count(*) > 0 from pgtelemetry.locks_by_type;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.locks_by_type" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.locks_by_type;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.locks_by_mode;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.locks_by_mode" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.locks_by_mode;
+                                 ^
SELECT pgtelemetry.count_waiting_on_locks_more_than_seconds(300) >= 0;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  schema "pgtelemetry" does not exist
+LINE 1: SELECT pgtelemetry.count_waiting_on_locks_more_than_seconds(...
+               ^
SELECT count(*) > 0 from pgtelemetry.tuple_access_stats;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.tuple_access_stats" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.tuple_access_stats;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.autovacuum_stats;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.autovacuum_stats" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.autovacuum_stats;
+                                 ^
SELECT count(*) > 0 from pgtelemetry.statement_query_rows_time;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.statement_query_rows_time" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.statement_query_rows_ti...
+                                 ^
SELECT count(*) > 0 from pgtelemetry.statement_query_buffers;
- ?column?
-----------
- t
-(1 row)
-
+ERROR:  relation "pgtelemetry.statement_query_buffers" does not exist
+LINE 1: SELECT count(*) > 0 from pgtelemetry.statement_query_buffers...
+                                 ^

Add CI

Add CI to make tests easier.

Make long-running queries report state change duration and improve pg_dump handling

• we should report time since last state change in addition to time since the query start
• if the rule being matched contains a specific state we should count the time since state change (otherwise a query running for 1h that just became 'idle in transaction' will immediately trigger the 'idle in transaction' rule without waiting for the 5m specified in the rules.
• pg_dump pattern should use a prefix match to handle pg_dump11 and similar cases of application_name.

PostgreSQL version below 10 support

Documentation states that postgresql versions 9.6+ are supported, but starting with version 1.2 pgtelemetry uses column pg_stat_activity.backend_type which is only available in postgresql v10+.

PGXN and Github mismatch

Hi,

The versions in PGXN and Github are different: 1.6.0 and 1.0.0. I also see no tag for 1.6.0. Can you please fix it?

Thanks!

Devrim