I have an issue with pg_stat_statements, the extension is installed and enabled on the database, was working for a long time, however it now seems that it has crashed.
portaldb=> SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+-----------------------------------------------------------
pg_stat_statements | 1.4 | 1.4 | track execution statistics of all SQL statements executed
(1 row)
portaldb=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
name | version | installed | superuser | relocatable | schema | requires | comment
--------------------+---------+-----------+-----------+-------------+--------+----------+-----------------------------------------------------------
pg_stat_statements | 1.4 | t | t | t | | | track execution statistics of all SQL statements executed
(1 row)
The extension is enabled on the portaldb
database
portaldb=> create extension pg_stat_statements;
ERROR: extension "pg_stat_statements" already exists
I was considering removing it and then recreating it but I feel a bit scared to apply a cascade drop, because this is a big e-commerce portal and we don't want lose data or cause any outage.
portaldb=# DROP EXTENSION pg_stat_statements;
ERROR: cannot drop extension pg_stat_statements because other objects depend on it
DETAIL: function get_pg_stats() depends on type pg_stat_statements
HINT: Use DROP ... CASCADE to drop the dependent objects too.
If I try with the user that manages the database we receive the following error:
portaldb=> SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
ERROR: relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
However if we execute the same query under root (postgres user) we receive a blank output.
portaldb=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
userid | dbid | queryid |
portaldb=#
root@Serv01:~# cat /app/postgres/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements'
root@Serv01:~# cat /app/postgres/postgresql.conf | grep pg_stat_statements.
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
Has anyone else come across this situation? I Googled but I didn't find any related case.
get_pg_stats()
function? It is a bad idea to have database objects depend on extensions that way. What do you get for\dx pg_stat_statements
? Maybe the extension is installed in a schema that is not on the normal user'ssearch_path
. The emptySELECT
result is weird. Why are there only three columns? Perhaps the rest got scrolled outside your window? Try to disable the pager with\pset pager off
.