Query stats are available but not enabled about pghero

[edbond]

Had to do:

DROP FUNCTION pg_stat_statements_reset();
DROP FUNCTION pg_stat_statements() CASCADE;
       NOTICE:  drop cascades to view pg_stat_statements
CREATE EXTENSION pg_stat_statements;

pghero is happy.

[StalkAlex]

CREATE EXTENSION pg_stat_statements shows me ERROR: permission denied to create "pg_catalog.pg_stat_statements". Current user is superuser. what should I do?

[edbond]

seems to be not installed?

<db>=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment                     

--------------------+-----------------+-------------------+------------------------------------------------------
-----
 pg_stat_statements | 1.1             |                   | track execution statistics of all SQL statements exec
uted
(1 row)

but I use that to email slow queries daily.

    results = PgStatStatement.select("*, round((total_time / calls)::numeric, 2) as s").
      order("s DESC").all

[ankane]

Very odd. Does pg_stat_statements appear in this query with installed true?

SELECT * FROM pg_available_extension_versions;

Do you recall how pg_stat_statements was originally installed?

[edbond]

No, it's not installed.

        name        | version | installed | superuser | relocatable | schema | requires |                        
  comment                          
--------------------+---------+-----------+-----------+-------------+--------+----------+------------------------
-----------------------------------
 pg_stat_statements | 1.1     | f         | t         | t           |        |          | track execution statist
ics of all SQL statements executed
(1 row)

I didn't run create extension, just add to shared_libraries following instructions:
http://www.postgresql.org/docs/9.2/static/pgstatstatements.html

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.

[edbond]

it seems something wrong with db. Tried with new db on my local (postgresql 9.3.5)

→  createdb zxc
→  psql zxc
zxc=# SELECT * FROM pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist
zxc=# CREATE extension pg_stat_statements;
CREATE EXTENSION
zxc=# SELECT * FROM pg_stat_statements;
 userid |  dbid  |                                                                                               ........

[ankane]

You'd lose historic data, but one option is to drop functions and views as you hit errors with CREATE EXTENSION pg_stat_statements.

DROP FUNCTION pg_stat_statements_reset;
-- DROP VIEW pg_stat_statements; etc

[edbond]

It seems a weird bug with particular DB. Will do cleanup as you advised. Thx

[ankane]

Thanks for following up with your solution! I'm sure others encountering this will find it useful.

[jbejar]

https://github.com/ankane/pghero/blob/master/guides/Query-Stats.md