CODEWITHSUNDEEP
postgresqlgoogle-cloud-sql
Sean Yuan
Sean Yuan

Reputation: 173

pg_stat_statements_reset() permission denied on Google Cloud SQL PostrgreSQL

Main Issue

I'm unable to execute the function SELECT pg_stat_statements_reset(); in order to profile changes in query optimization due to insufficient permissions. The error message reads: permission denied for function pg_stat_statements_reset

I would like to know if there are any other ways to reset the pg_stats on Cloud SQL PostgreSQL?

Environment

Attempted Steps

I have found a previous answer on this topic which suggested that pg_stat_statements_reset() should work from the default postgres user created through the cloud console. However, the listed solution does not work, it returns the same permission denied error

Related Question

Upvotes: 9

Views: 10140

Answers (2)

Pawel Czuczwara
Pawel Czuczwara

Reputation: 1520

Google Cloud SQL supports several modules for PostgreSQL to extend its functionality.

One of the extensions (modules) is: pg_stat_statements. It allows tracking execution statistics of SQL statements executed by a server. To reset statistics function pg_stat_statements_reset() is used.

Before using extension (module), it has to be installed:

  1. Connect to the PostgreSQL instance from Cloud Shell using default user: postgres
gcloud sql connect [INSTANCE_ID] --user=postgres
  1. When connected to the database, create extension pg_stat_statements
CREATE EXTENSION pg_stat_statements
  1. Execute the function to reset statistics. By default, It can only be executed by superusers:
SELECT pg_stat_statements_reset()
  1. (optional) Grant privilege for stats resetting to other users:
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset TO xuser;

Upvotes: 9

gavenkoa
gavenkoa

Reputation: 48883

With snapshots:

DROP TABLE IF EXISTS stat_snap_1;
DROP TABLE IF EXISTS stat_snap_2;

-- first time
CREATE TABLE stat_snap_1 AS SELECT * FROM pg_stat_statements WHERE queryid IS NOT NULL;
-- second time
CREATE TABLE stat_snap_2 AS SELECT * FROM pg_stat_statements WHERE queryid IS NOT NULL;

SELECT s2.calls - s1.calls, s2.total_time - s1.total_time, s2.*, s1.*
FROM stat_snap_2 s2
FULL OUTER JOIN stat_snap_1 s1 ON s1.queryid = s2.queryid
ORDER BY s2.total_time - s1.total_time DESC NULLS LAST;

Upvotes: 2

Related Questions