Charm conflicts with extension enabled via SQL

[TeodorPt]

Steps to reproduce

1. Deploy a postgresql-k8s charm.
2. Run CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA pg_catalog; in the resulting database, directly.
3. Create an index using the extension, e.g. CREATE INDEX example_idx ON example USING gin(name gin_trgm_ops);.

Expected behavior

(?)

Actual behavior

The extension is successfully created in the instance. Later, on various juju events, the charm actively tries to remove pg_trgm because it is not enabled via the plugin_pg_trgm_enable config. It however fails to do so because there's an index depending on it. This is an excerpt from the Postgres logs:

user=operator,db=<redacted>,app=[unknown],client=<redacted>,line=7 LOG:  statement: DROP EXTENSION IF EXISTS pg_trgm;
user=operator,db=<redacted>,app=[unknown],client=<redacted>,line=8 ERROR:  cannot drop extension pg_trgm because other objects depend on it
user=operator,db=<redacted>,app=[unknown],client=<redacted>,line=9 DETAIL:  index <redacted> depends on operator class gin_trgm_ops for access method gin
user=operator,db=<redacted>,app=[unknown],client=<redacted>,line=10 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
user=operator,db=<redacted>,app=[unknown],client=<redacted>,line=11 STATEMENT:  DROP EXTENSION IF EXISTS pg_trgm;

Versions

Juju CLI: 3.5.1-ubuntu-amd64
Juju agent: 3.4.2
Charm revision: 264, version 14.11

Additional Context

I am not sure if this is actually a bug or just a peculiar behavior that we have observed. Nevertheless, reporting here in case there's anything that can be done to improve the experience.

[github-actions]

https://warthogs.atlassian.net/browse/DPE-4801

[marceloneppel]

Hi, @TeodorPt! Thanks for the report.

We'll be working to fix this issue in the next two weeks.

[lucasgameiroborges]

Hi there @TeodorPt, this issue was addressed in #567, where we now have a blocked status in case where the charm tries to disable a plugin but it can't. In such a scenario, the user can proceed in 2 ways:

• Keep the blocking objects and enable the plugin via juju config,
• Delete the blocking objects and wait for update-status event for the plugin to get disabled,

Both actions should unblock the charm. We hope this will provide better UX, for you, feel free to re-open this issue if something behaves unexpectedly. Thank you!

[TeodorPt]

Nice, thank you!