pg_stat_statements enabled, but the table does not exist

Question

I've postgresql-9.4 up and running, and I've enabled pg_stat_statements module lately by the help of official documentation.

But I'm getting following error upon usage:

postgres=# SELECT * FROM pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements;


postgres=# SELECT pg_stat_statements_reset();
ERROR:  function pg_stat_statements_reset() does not exist
LINE 1: SELECT pg_stat_statements_reset();

I'm logged in to psql with the postgres user. I've also checked the available extension lists:

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'
;
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.2             |                   | track execution statistics of all SQL statements executed
(1 row)

And here's the results of the extension versions query:

postgres=# SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';
        name        | version | installed | superuser | relocatable | schema | requires |                          comment                          
--------------------+---------+-----------+-----------+-------------+--------+----------+-----------------------------------------------------------
 pg_stat_statements | 1.2     | f         | t         | t           |        |          | track execution statistics of all SQL statements executed
(1 row)

Any help will be appreciated.

Mariko

Maybe the Posgres user you are using isn't allowed access the extension. To use pg_stat_statements_reset() you need to be superuser if I'm not mistaken. – Shimmy

Thanks, dude for the comment, but I've checked it, postgres is superuer: postgres=# show is_superuser; is_superuser -------------- on (1 row) – Mariko

Answer 1

Extension isn't installed:

SELECT * 
FROM pg_available_extensions 
WHERE 
    name = 'pg_stat_statements' and 
    installed_version is not null;

If the table is empty, create the extension:

CREATE EXTENSION pg_stat_statements;

Fulminous

Hmm..but I've added these lines to postgresql.conf and restarted the server. shared_preload_libraries = 'pg_stat_statements' # (change requires restart) pg_stat_statements.max = 1000 pg_stat_statements.track = all Should I do anything else? – Mariko

you should run CREATE EXTENSION pg_stat_statements; – Inspection

Thanks! My bad, I thought it'll be automatically created after adding it to postgresql.conf. – Mariko

Strange thing. CREATE EXTENSION says that it's already installed, but there is no such table in list. What else can be done? – Keane

Are you remove second condition (installed_version is not null) ? In PgAdmin look at [Server Groups] -> [Servers] -> [Server name] -> [Databases] -> [DB name] -> [Extensions] – Fulminous

The docs says: "When pg_stat_statements is loaded, it tracks statistics across all databases of the server." Does that mean that it starts tracking statistics across all databases, but to be able to see it I've got to create extension in a database? And that will make me see statistics just for that one database? – Knitwear

Answering my own question, it starts tracking statistics across all databases after being loaded. To be able to access it, you've got to create extension in any database. But only superuser can do it, and it gives access to statistics across all databases. – Knitwear

Answer 2

Follow below steps:

• Create the extension

  CREATE EXTENSION pg_stat_statements;
  

• Change in config

  alter system set shared_preload_libraries='pg_stat_statements';
  

• Restart

  $ systemctl restart postgresql
  

• Verify changes applied or not.

  select * from pg_file_Settings where name='shared_preload_libraries';
  

  The applied attribute must be 'true'.

Sprinkler

Answer 3

I've faced with this issue at configuring Percona Monitoring and Management (PMM) because by some strange reason PMM connecting to database with name postgres, so pg_stat_statements extension have to be created in this database:

yourdb# \c postgres
postgres# CREATE EXTENSION pg_stat_statements SCHEMA public;

Debauch

Answer 4

I Had the same issue when deploying the environment using liquibase for the first time. I understand that my reply maybe is not related with your problem but was the first google result so I think that other guys like me can arrive here with my the same Liquibase Issue.

These are PosGreSQL metadata tables that are retrieved by liquibase when you generate your first xml file.

In my case it only was useless autogenerated code, so I solved it deleteing these lines:

 <changeSet author="martinlarizzate (generated)" id="1588181532394-7">
        <createView fullDefinition="false" viewName="pg_stat_statements"> SELECT pg_stat_statements.userid,
    pg_stat_statements.dbid,
    pg_stat_statements.queryid,
    pg_stat_statements.query,
    pg_stat_statements.calls,
    pg_stat_statements.total_time,
    pg_stat_statements.min_time,
    pg_stat_statements.max_time,
    pg_stat_statements.mean_time,
    pg_stat_statements.stddev_time,
    pg_stat_statements.rows,
    pg_stat_statements.shared_blks_hit,
    pg_stat_statements.shared_blks_read,
    pg_stat_statements.shared_blks_dirtied,
    pg_stat_statements.shared_blks_written,
    pg_stat_statements.local_blks_hit,
    pg_stat_statements.local_blks_read,
    pg_stat_statements.local_blks_dirtied,
    pg_stat_statements.local_blks_written,
    pg_stat_statements.temp_blks_read,
    pg_stat_statements.temp_blks_written,
    pg_stat_statements.blk_read_time,
    pg_stat_statements.blk_write_time
   FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);</createView>
    </changeSet>

Leakage