0

I want to upgrade a Postgres 9.6 database on CloudSQL (GCP) up to version 13. As SQL export/import is the only supported upgrade path, I've exported the PG9.6 database and attempted to import it into a new PG13 instance.

The import fails with the following error though:

ERROR: function public.pg_stat_statements_reset() does not exist

I figured it's triggered by the following line in the dump:

GRANT ALL ON FUNCTION public.pg_stat_statements_reset() TO somerandomuser;

When I try to execute that single line manually on the new PG13 instance, I'm getting the error as well. The same happens on PG12.

On PG11 though, it works fine.

I've made sure the pg_stat_statements extension is installed in all tested DB instances:

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

        name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 pg_stat_statements | 1.8             | 1.8               | track planning and execution statistics of all SQL statements executed
(1 row)

I couldn't find any documentation about a breaking change affecting this. Any ideas on why it is not working?

1
  • The message you should take home from that: never change permissions or anything else on system defined objects or objects from an extension. If necessary, user a SECURITY DEFINER function. Sep 25, 2021 at 20:41

1 Answer 1

3

We have added arguments to pg_stat_statements_reset during postgresql 12 developing cycle (in fact, in version 1.7 of the extension itself). These new arguments have default values, but anyway foo() and foo(param int DEFAULT 0) are different functions from postgresql point of view. You need replace this GRANT to

grant all on FUNCTION pg_stat_statements_reset(oid, oid, bigint) TO somerandomuser ;

Also due to the nature of the pg_stat_statements extension, you can simply drop the extension before upgrading and create again later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.