Error running upgrade on Postgres

[armdlllr]

Hello,

I was testing some migrations with aerich and it seems that the upgrade is broken when I'm trying to delete some fields with constraints or indexes :

exemple :

class Protocol(BaseModel)

    id = fields.IntField(pk=True)
    protocol_name = fields.CharField(max_length=255, unique=True)
    protocol_type: ProtocolTypeSelection = fields.CharEnumField(ProtocolTypeSelection)
    protocol_token = fields.ForeignKeyField("models.Token", related_name="token_protocols", on_delete="CASCADE")

    class Meta:
        table = "protocol"

    class PydanticMeta:
        exclude = ("protocol_vaults", "protocol_positions")

    class PydanticMetaOverride:
        exclude = ("protocol_vaults", "protocol_positions")

I'm trying to delete protocol_token and it gives me this migration file

from tortoise import BaseDBAsyncClient

async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        ALTER TABLE "protocol" DROP CONSTRAINT "fk_protocol_token_f05b57e5";
        ALTER TABLE "protocol" DROP COLUMN "protocol_token_id";
        ALTER TABLE "investment_balance_history" ALTER COLUMN "investment_balance_history_date" SET DEFAULT '2023-07-28 15:03:55.053110';"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        ALTER TABLE "protocol" ADD "protocol_token_id" INT NOT NULL;
        ALTER TABLE "investment_balance_history" ALTER COLUMN "investment_balance_history_date" SET DEFAULT '2023-07-28 15:02:26.114393';
        ALTER TABLE "protocol" ADD CONSTRAINT "fk_protocol_token_f05b57e5" FOREIGN KEY ("protocol_token_id") REFERENCES "token" ("id") ON DELETE CASCADE;"""

but then when I'm applying the upgrade, it raises this error :

constraint "fk_protocol_token_f05b57e5" of relation "protocol" does not exist

It's the same with protocol name, if I'm trying to change unique=True to False for protocol name, it will try to delete a non existing indexe.

The only way I found to make it work is to delete rows where it's trying to drop contraints or indexes on the migration file

I'm using postgres v13 and when I take a look inside the database, constraints and indexes are existing but with a different name than the one displayed in the migration file :

"protocol_protocol_token_id_fkey" FOREIGN KEY (protocol_token_id) REFERENCES token(id) ON DELETE CASCADE

Am I doing something wrong or is it a bug ?

[catarium]

had same problem https://github.com/tortoise/aerich/issues/253

[vlakius]

Had the same problem too postgres 15.1 aerich==0.7.2

The problem seems to be that aerich does not correctly identify the foreign key This can be verified by executing this command

SELECT conname
FROM pg_constraint
WHERE conrelid = 'your_table_here'::regclass AND contype = 'f';

the name of the foreign key is different from the name in the migration file. By modifying this in the file and running aerich update the migration is successful.