We have a heroku postgres database that is running on version 9.6.1
When upgrading our pghero installation to the newest version 2.0.2 we're getting failures that pghero isn't able to find the queryid column in the pg_stat_statements table.
The pg_stat_statements extension is installed.
2017-08-10T09:54:44.002042+00:00 app[web.1]: Completed 500 Internal Server Error in 274ms (ActiveRecord: 186.1ms)
2017-08-10T09:54:44.004012+00:00 app[web.1]:
2017-08-10T09:54:44.004048+00:00 app[web.1]: ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "queryid" does not exist
2017-08-10T09:54:44.004050+00:00 app[web.1]: LINE 1: ...ry_stats AS ( SELECT LEFT(query, 10000) AS query, queryid AS...
2017-08-10T09:54:44.004051+00:00 app[web.1]: ^
2017-08-10T09:54:44.004052+00:00 app[web.1]: HINT: Perhaps you meant to reference the column "pg_stat_statements.query".
2017-08-10T09:54:44.004055+00:00 app[web.1]: : WITH query_stats AS ( SELECT LEFT(query, 10000) AS query, queryid AS query_hash, rolname AS user, (total_time / 1000 / 60) AS total_minutes, (total_time / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE pg_database.datname = current_database() ) SELECT query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * 100.0 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT 100):
It turns out that upgrading the postgres version on heroku does not necessarily update extensions to the most current version.
Updating the extension by running
ALTER EXTENSION pg_stat_statements UPDATE;
fixed the problem.