Add if_exists for dropping postgres constraints

[agriffin-grow]

Describe the use case

In #1520, I added support for if_exists/if_not_exists on table commands. Previously it existed on only index commands.

This is a companion issue to support DROP CONSTRAINT IF EXISTS

Databases / Backends / Drivers targeted

Postgresql

Example Use

op.drop_constraint('foobar', 'table1', if_exists=True)

should produce

ALTER TABLE table1 DROP CONSTRAINT IF EXISTS foobar

Additional context
I'll be submitting a PR for this in a short while

[zzzeek]

please take a look at #1626 and #1627 also so we can finish all possible IF EXISTS PRs for good

[agriffin-grow]

@zzzeek that PR seems a little bit of a different scope if I'm reading it right - it seems to focus on the autogeneration, right?

[zzzeek]

the PR wasn't done the way I ultimately want, and the important part is adding "if_exists" / "if_not_exists" to the AddColumnOp/DropColumnOp (and not postgresql_if_exists, lots of databases support this syntax for column adds/drops). the autogeneration part of that PR is not even needed

[agriffin-grow]

@zzzeek just looking at the base, non-dialect specific sqlalchemy support, it only covers these three objects:

Which means the add/drop column support is a little bit different than the way this PR and my previous one works.

[zzzeek]

Your PR adds "if_exists" / "if_not_exists" to AddConstraintOp / DropConstraintOp. The other PR adds "if_exists" / "if_not_exists" to AddColumnOp / DropColumnOp. So at that level the PRs are exactly the same kind of change. I guess when you refer to sqlalchemy.schema, you mean that SQLAlchemy core already supports if_exists for indexes and constraints, that's fine, the "add column" / "drop column" DDL is contained within Alembic, so to implement that would only need the PostgreSQL additions seen at https://github.com/sqlalchemy/alembic/pull/1627/files#diff-dfb1afa7741656714fa6eaeb44c106f91efa751b2f2b93f46b336c0126e704ef which is pretty short.

it just would be nice to get all the "if_exists" requests out of the way. Personally I've never understood the use case for these parameters in migration files which are expected to work from an exact known version of a schema.

[agriffin-grow]

Personally I've never understood the use case for these parameters in migration files which are expected to work from an exact known version of a schema.

The use case is almost always partially applied migrations IME. If I have a migration that adds an index and then does something else, and the index succeeds but the next step fails, running the migration again either requires an "if exists" clause, or manual DB surgery to remove the index and run the migration again.

[zzzeek]

Personally I've never understood the use case for these parameters in migration files which are expected to work from an exact known version of a schema.

The use case is almost always partially applied migrations IME. If I have a migration that adds an index and then does something else, and the index succeeds but the next step fails, running the migration again either requires an "if exists" clause, or manual DB surgery to remove the index and run the migration again.

but if you're on PostgreSQL which is where most of these IF EXISTS clauses seem to be used, you are using transactional DDL. There aren't "partial migrations"

[agriffin-grow]

Sure, most of the time, but we make liberal use of autocommit blocks and postgres_concurrently.

In this specific constraint case that made me put up this PR, I believe it was simply part of an autocommit block when it shouldn't have been.

[zzzeek]

anyway our contributor for #1627 seems to have returned so lets just get this one through for the features you've included so far

[sqla-tester]

Aaron Griffin has proposed a fix for this issue in the main branch:

Support DROP CONSTRAINT IF EXISTS https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/5878

[sqla-tester]

Aaron Griffin has proposed a fix for this issue in the main branch:

Support DROP CONSTRAINT IF EXISTS https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/5878