Reflect exclude constraints from Postgres tables.

[Chris Withers]

SQLA currently doesn't, which makes Alembic a bit sad sometimes.

[Mike Bayer]

this can be pushed up if someone wants to work on it

[Robin Thomas]

@zzzeek An obstacle to this feature is that engine.reflection only creates generally supported schema objects present in sql.schema, using item dictionaries returned by the dialect's get_XXX_constraints methods as kw arguments to the schema object constructors. ExcludeConstraint is a dialect-specific object in dialects.postgresql. Possible ways forward: 1. Add get_dialect_specific_constraints to dialect interface; this method returns not dictionaries of kwargs, but instead actual dialect-specific constraint objects. This way, dialects can return any dialect-specific constraints not yet present in sql.schema and that aren't returned by the general constraint methods. 2. Add ExcludeConstraint to sql.schema and ensure all dialects report their (lack of) support appropriately.

Since there's a urgent need for my own team's development work to see ExcludeConstraints in alembic, a possible way forward for alembic to support ExcludeConstraints without adding this to engine.reflection is to allow dialect-specific code inside alembic.autogen and its framework. We have custom code we use in our alembic env.py currently to register the needed operations, add a table comparator, and organize the modify operations properly. If alembic grew a dialect framework for autogen, we could provide this code as a pull request to alembic, and it wouldn't require any additions to SQLA.

[Mike Bayer]

@robin900 there's no technical issue to this feature being added, dialects support dialect-specific Inspector objects and Postgresql has the most involved one already: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=pginspector#special-reflection-options . To provide the ExcludeConstraint as part of the Table, the PGInspector would just need to override inspector.reflecttable as well to add these structures.

On the alembic side, support could also be added to the Postgresql DDL implementation for versions of SQLAlchemy that would add ExcludeConstraint to a Table.constraints collection.

As for your own team, no changes to SQLAlchemy or Alembic are needed to have ExcludeConstraint support in Alembic, as you can implement this completely from scratch using Alembic extension points. For a complete example how to support a new DDL construct with alembic including autogenerate, see http://alembic.zzzcomputing.com/en/latest/api/operations.html#operation-plugins for the basics and then http://alembic.zzzcomputing.com/en/latest/api/autogenerate.html#autogen-custom-ops for how to implement autogenerate comparison and rendering features.

[Robin Thomas]

We've already implemented it completely from scratch, using the extension points. I was just exploring how it could most expediently be made part of the alembic package.

[Mike Bayer]

the extension points worked! nice! so yeah, ExcludeConstraint can be added to PGInspector.

[Robin Thomas]

And we'd need to have PGInspector override reflecttable(), although the override could gracefully call superclass method and just add a call to a new method self._reflect_exclude_constraints. (And we'd make a small change to get_indexes for duplicates_constraint checking.) That would do the trick?

[Mike Bayer]

The base Inspector.reflecttable() should call into a hook at the end, something like self.reflect_dialect_specific(). That way PGInspector overrides that and doesn't have to wrap the top-level reflecttable() method.