0

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.

1
  • What is this 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's search_path. The empty SELECT 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. Jun 7, 2019 at 13:08

1 Answer 1

0

I found the solution, when we enable the extension on database creates a function seems that someone remove the function (there are many users on with database user), we recreate it and now works properly, thank for your help Vérace.

portaldb=> \df+

                                                                                                                                                                                              List of functions
   Schema    |           Name           | Result data type |                                                                                                                                                                
                                                                                                                                                          Argument data types                                               

                                                |  Type  | Volatility | Parallel |  Owner   | Security |  Access privileges  | Language |       Source code        | Description 
-------------+--------------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------+--------+------------+----------+----------+----------+---------------------+----------+--------------------------+-------------
 portaldb | pg_stat_statements       | SETOF record     | showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls bigint, OUT total_time double precision, OUT min_time double prec
ision, OUT max_time double precision, OUT mean_time double precision, OUT stddev_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared
_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double
 precision, OUT blk_write_time double precision | normal | volatile   | safe     | postgres | invoker  |                     | c        | pg_stat_statements_1_3   | 
 portaldb | pg_stat_statements_reset | void             |                                                                                                                                                                


                                                | normal | volatile   | safe     | postgres | invoker  | postgres=X/postgres | c        | pg_stat_statements_reset | 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.