YSQL upgrade fails if pg_stat_statement namespace is modified to public instead of default pg_catalog

[yugabyte-ci]

Jira Link: DB-8853

[jasonyb]

In case a user manually does DROP EXTENSION and CREATE EXTENSION of pg_stat_statements but forgets to specify SCHEMA pg_catalog when creating the extension, they can hit error when applying migration Vxxx__14445__alter_pg_stat_statements.sql. For example, Failed to apply migration 'V35.1__14445__alter_pg_stat_statements.sql' to a database yugabyte: Execute failed: 7, message: ERROR: relation "pg_catalog.pg_stat_statements" does not exist LINE 4: WHERE attrelid = 'pg_catalog.pg_stat_statements'::re... ^. This is because the migration script depends on pg_stat_statements existing in the pg_catalog namespace, but CREATE EXTENSION pg_stat_statements by itself will generally put it in the public namespace.

To determine if you hit this issue, you can run SELECT oid, * FROM pg_extensions, SELECT oid, * FROM pg_class WHERE relname = 'pg_stat_statements', SELECT * FROM pg_yb_migration ORDER BY major DESC, minor DESC on each database and pay particular attention to namespace columns.

Fix for users hitting this is to run ALTER EXTENSION pg_stat_statements SET schema pg_catalog on each affected database.

This issue tracks how to avoid this situation from happening. There are options:

• change the migration script to be more permissive: allow pg_stat_statements to not exist or be in a namespace/schema besides pg_catalog
• disallow dropping the pg_stat_statements extension or changing its schema

Leaning towards the first option, but it assumes pg_stat_statements in a different schema has no issues.

[jasonyb]

Also, the query could be changed to look at pg_extension's extversion column instead, which is less hacky than the current version. But the challenge is you need to do some complex sorting to make sure the extension version is >= the expected version, so it may not be worth it.