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.

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. — , Jun 24 '15 at 08:15
Thanks, dude for the comment, but I've checked it, postgres is superuer: `postgres=# show is_superuser; is_superuser -------------- on (1 row) ` — Hett, Jun 24 '15 at 08:26

score 99 · Accepted Answer · edited Feb 20 '18 at 09:10

99

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;

edited Feb 20 '18 at 09:10

Tombart

30,520
16
123
136

answered Jun 24 '15 at 08:17

cetver

11,279
5
36
56

1

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? – Hett Jun 24 '15 at 08:21
17

you should run CREATE EXTENSION pg_stat_statements; – Vao Tsun Jun 24 '15 at 08:22
1

Thanks! My bad, I thought it'll be automatically created after adding it to postgresql.conf. – Hett Jun 24 '15 at 08:29
3

Strange thing. CREATE EXTENSION says that it's already installed, but there is no such table in list. What else can be done? – StalkAlex Jan 08 '16 at 12:29
Are you remove second condition (installed_version is not null) ? In PgAdmin look at [Server Groups] -> [Servers] -> [Server name] -> [Databases] -> [DB name] -> [Extensions] – cetver Jan 08 '16 at 23:48
[The docs](https://www.postgresql.org/docs/9.6/static/pgstatstatements.html) 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? – x-yuri Jan 07 '18 at 18:06
2

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. – x-yuri Jan 07 '18 at 18:35

score 7 · Answer 2 · edited Jul 15 '22 at 05:38

7

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'.

edited Jul 15 '22 at 05:38

cursorrux

1,382
4
9
20

answered Jul 13 '22 at 12:51

Siddheshwar Soni

182
1
7

score 3 · Answer 3 · answered Jun 26 '21 at 16:32

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;

score 2 · Answer 4 · answered May 02 '20 at 19:22

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>

pg_stat_statements enabled, but the table does not exist

4 Answers4