How to handle changing formerly-unnamed constraints (and retrying after errors in migration scripts)

[Ben Sizer]

I had an unnamed UniqueConstraint, and autogenerated upgrade scripts that passed in None as the name - these worked perfectly. Now I am discovering that the downgrade doesn't work, as there is no name to refer to the constraint (i.e. "sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint UniqueConstraint(); it has no name")

So, I'm trying to add a name in the model, and edit the migration scripts accordingly. My strategy was this:

 - the upgrade function would add the constraint with a name in future
 - the downgrade function needs to be able to delete the constraint whatever it was called

The first is easy enough and seems to have no problems.

The second is tricky; if the name passed to drop_constraint is wrong, it raises the expected error (e.g. sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) constraint "provider_name_user_id_key" of relation "user_authentication_method" does not exist)
What I'd like to do, is catch that exception, and try another name (e.g. the name that I know was autogenerated, in this case 'user_authentication_method_pkey') - however, attempting to catch the error and perform a further operation emits a Postgres internal error that suggests I need to end the current transaction and start a new one (i.e. sqlalchemy.exc.InternalError: (psycopg2.InternalError) current transaction is aborted, commands ignored until end of transaction block) and I can't find out how to do that. I tried calling rollback() on a session created around the return value of get_bind() but the error was the same.

To complicate matters, one of the possible DBs is SQLite so I have to do this via op.batch_etc. Thankfully that data is not essential so deleting the whole DB is an option there - not so much for the Postgres data.

So my questions are:

1) How can I handle this drop_constraint call when we didn't specify a constraint name in the first place?
2) If the best way is for me to just attempt to drop constraints by name, trying several names, how can I catch or avoid the first error and try subsequent constraint names?

Thanks,
Ben

[Mike Bayer]

You should be able to use a savepoint around that operation to prevent
this issue from happening. This works for simple things like INSERTs
and hopefully works for DDL as well. Either with an ORM Session or with
the Connection, the begin_nested() method gives you a savepoint
transaction.

>
> To complicate matters, one of the possible DBs is SQLite so I have to do
> this via op.batch_etc. Thankfully that data is not essential so deleting
> the whole DB is an option there - not so much for the Postgres data.

For SQLite I've always been a proponent of dropping the whole thing and
recreating from scratch. The existence of batch mode is due to all the
pressure I get from users who don't want to do it that way, but I still
favor not trying to make SQLite do real migrations - its creators
disagree with this use case hence they've never implemented it.

>
> So my questions are:
>
> 1) How can I handle this drop_constraint call when we didn't specify a
> constraint name in the first place?
> 2) If the best way is for me to just attempt to drop constraints by
> name, trying several names, how can I catch or avoid the first error and
> try subsequent constraint names?
>
> Thanks,
> Ben
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Ben Sizer]

Aha, thanks for that. For the benefit of anyone who comes across this later, my final script looked much like this:

def downgrade():
    try:
        inner_transaction = op.get_bind().begin_nested() # establish a savepoint
        with op.batch_alter_table("user_authentication_method") as batch_op:
        batch_op.drop_constraint(composite_key_name, type_='unique')
    except ProgrammingError:
        # Try again, with the name we probably have for it, at least in Postgres
        inner_transaction.rollback()
        probable_name = "user_authentication_method_pkey"
        with op.batch_alter_table("user_authentication_method") as batch_op:
            batch_op.drop_constraint(probable_name, type_='unique')

No guarantees that the above is /correct/ but it catches the exception and seems to work so far.

Thanks again.

--
Ben