DB Diff causes "cannot drop type" when adding value to enum used in function parameter

[parkernilson]

Describe the bug
When adding a value to an enum, the db diff algorithm renames the type, creates a new one with the new value, and then alters all objects that depend on it to use the newly created enum, then deletes the old enum. However, it does not update functions that use the enum as a parameter type.

To Reproduce
Steps to reproduce the behavior:

1. Create an enum type, with a table and a function that both use it

   create type enum_type_1 as enum ('val1', 'val2');
   
   create table table_with_enum (
     enum_col enum_type_1
   );
   
   create function function_with_enum(param1 enum_type_1) returns void as $$
   begin
     raise notice '%', param1;
   end;
   $$ language plpgsql;

2. Add a value to the enum:

   alter type enum_type_1 add value 'val3';

3. Generate a diff, which will result in:

   alter type "public"."enum_type_1" rename to "enum_type_1__old_version_to_be_dropped";
   
   create type "public"."enum_type_1" as enum ('val1', 'val2', 'val3');
   
   alter table "public"."table_with_enum" alter column enum_col type "public"."enum_type_1" using enum_col::text::"public"."enum_type_1";
   
   drop type "public"."enum_type_1__old_version_to_be_dropped";

   However, the function is not re-created with the new enum, so trying to run the diff results in this error:
   ERROR: cannot drop type enum_type_1__old_version_to_be_dropped because other objects depend on it (SQLSTATE 2BP01) At statement 3: drop type "public"."enum_type_1__old_version_to_be_dropped" because the function is still using "public"."enum_type_1__old_version_to_be_dropped".

Expected behavior
After the new enum is created with the extra val, the function that depends on it should be recreated with the new enum:

alter type "public"."enum_type_1" rename to "enum_type_1__old_version_to_be_dropped";

create type "public"."enum_type_1" as enum ('val1', 'val2', 'val3');

alter table "public"."table_with_enum" alter column enum_col type "public"."enum_type_1" using enum_col::text::"public"."enum_type_1";

drop function function_with_enum(param1 enum_type_1__old_version_to_be_dropped);
create function function_with_enum(param1 enum_type_1) returns void as $$
 begin
   raise notice '%', param1;
 end;
 $$ language plpgsql;

drop type "public"."enum_type_1__old_version_to_be_dropped";

System information
Rerun the failing command with --create-ticket flag.

• Ticket ID: ab6d3e3501ea47c1a3840a5ca505b8fb
• Version of OS: macOS Sonoma 14.5
• Version of CLI: v1.191.3
• Version of Docker: v24.0.2
• Versions of services:

  supabase/postgres      │ 15.1.1.78        │ -
  supabase/gotrue        │ v2.158.1         │ -
  postgrest/postgrest    │ v12.2.0          │ -
  supabase/realtime      │ v2.30.23         │ -
  supabase/storage-api   │ v1.10.1          │ -
  supabase/edge-runtime  │ v1.56.1          │ -
  supabase/studio        │ 20240729-ce42139 │ -
  supabase/postgres-meta │ v0.83.2          │ -
  supabase/logflare      │ 1.4.0            │ -
  supabase/supavisor     │ 1.1.56           │ -
  

Additional context
I made sure to use migra with supabase db diff --use-migra. I have also filed a ticket with Migra here

[parkernilson]

Hello! Any updates on this issue? Has anybody seen it?

[parkernilson]

Update: It looks like when I use supabase db diff --use-pg-schema it generates the following migration:

ALTER TYPE "public"."enum_type_1" ADD VALUE 'val3';

Which works just fine.

I am assuming this is because support for ALTER TYPE was added to postgres and Migra just doesn't know about it, or it is trying to be backwards compatible or something like that.

It looks like as a workaround, I will just manually alter the migration files to use ALTER TYPE whenever I modify an enum that is used as a function parameter type.

[sweatybridge]

Glad that you've found a workaround using --use-pg-schema. We will probably switch the default diff tool once pg-schema-diff ships better support for views.