Add enum value broke github action CI (migrations)

[eposha]

After the change ENUM I get migration error

This migration created from supabase db pull

alter type "public"."content_category" rename to "content_category__old_version_to_be_dropped";

create type "public"."content_category" as enum ('react', 'nextjs', 'html', 'css', 'js', 'ts');

drop type "public"."content_category__old_version_to_be_dropped";

This is an error

ERROR: cannot drop type content_category__old_version_to_be_dropped because other objects depend on it (SQLSTATE 2BP01)
At statement 2: drop type "public"."content_category__old_version_to_be_dropped"  

How to fix this?

[cervantes-x]

It works for me when I change the generated schema from:

alter type "public"."enum_name" rename to "enum_name__old_version_to_be_dropped";

create type "public"."enum_name" as enum ('enum_value1', 'enum_value2', 'enum_value3');

drop type "public".enum_name__old_version_to_be_dropped";

to

alter type "public"."enum_name" add value 'value_to_add';

Why is there a need to drop the type first instead of just altering the value?

[gc-ft]

I believe this actually risks being able to remove enum types, which could break things even further data wise.

alter type <enum name> add value <value> seems a much better approach for the migration scripts to use.

For anyone wondering:
The reason the current approach sometimes fails (since sometimes the rename, new, drop old approach works) is when the enum type is used in a function or policy and therefor can not be dropped (since internally postgres renames the enum type used in the function and policy to the new name caused by the alter type ... rename.

I believe this should be changed in the way migration scripts are built.