Charm unable to change ownership of procedures

[Osama-Kassem]

Steps to reproduce

1. Deploy postgresql-k8s
2. Relate it with an application.
3. Create a procedure using the app's credentials. (add_history_tracing_columns in my case)
4. un-relate the app and relate again.

The charm should try transferring ownership of everything under the public schema to the new relation user, but it fails at transferring ownership of the procedure and the app is stuck in Failed to initialize database relation

Expected behavior

The query executed should be ALTER PROCEDURE public."add_history_tracing_columns"(IN _table_name text) OWNER TO "relation_id_15";

Actual behavior

ALTER FUNCTION public."add_history_tracing_columns"(IN _table_name text) OWNER TO "relation_id_15";

Versions

Operating system: Ubuntu 24.04.1 LTS

Juju CLI: 3.4.6-genericlinux-amd64

Juju agent: 3.4.6

Charm revision: 444

microk8s: MicroK8s v1.29.10 revision 7396

Log output

Juju debug log:

unit-postgresql-k8s-0: 15:08:35 ERROR unit.postgresql-k8s/0.juju-log database:15: Failed to create database: public.add_history_tracing_columns(text) is not a function
CONTEXT:  SQL statement "ALTER FUNCTION public."add_history_tracing_columns"(IN _table_name text) OWNER TO "relation_id_15";"
PL/pgSQL function inline_code_block line 12 at EXECUTE

Additional context

It seems like the root cause of the error is here: https://github.com/canonical/postgresql-k8s-operator/blob/2a7f044766faa9d2873cc2486f6e3af72324ce24/lib/charms/postgresql_k8s/v0/postgresql.py#L377-L378

The _generate_database_privileges_statements function considers everything under the pg_proc table as a function and generates the ALTER FUNCTION ... OWNER TO ... queries, but the pg_proc table also contains procedures (and aggregate and window functions).

Additionally, this also happens to the PostgresQL VM charm, should I create a bug report there as well?

[syncronize-issues-to-jira[bot]]

Thank you for reporting us your feedback!

The internal ticket has been created: https://warthogs.atlassian.net/browse/DPE-6031.

This message was autogenerated

[dragomirp]

Hi, @Osama-Kassem, is this a blocking issue for you? I'm looking into splitting the alter statement by type.