Explicit usage of schema in models and foreign keys fails in public schema with postgres

[alexander-jacob]

Description

I have two models, whereas the second one has a foreign key to the first one.
For models and foreign keys I use explicitly the schema public

class CustomerType(Base):
    __tablename__ = "customer_type"
    __table_args__ = {"schema": "public"}

    id = Column(Integer, primary_key=True)
    code = Column(String(32), comment="customer type code", nullable=False)

class Customer(Base):
    __tablename__ = "customer"
    __table_args__ = {"schema": "public"}

    primary_key = Column(String(32), primary_key=True, comment="UID")
    first_name = Column(String(100), comment="First name", nullable=False)
    last_name = Column(String(100), comment="Last name", nullable=False)
    customer_type_id = Column(Integer, ForeignKey("public.customer_type.id"))

I create the revisions and apply them with alembic upgrade head.

Everything is fine.

Then I create another revision alembic revision --autogenerate --message "wtf"

Expected behavior
Since there are no changes I expect a revision file with empty updrade and downgrade methods.

Actual behavior
Alembic detects the following changes, but there should be no changes.

To Reproduce
To reproduce this behaviour I've setup a demo project https://github.com/alexander-jacob/alembic-issue

Error

(venv) alex@alex:~/alembic-issue$ alembic revision --autogenerate --message "wtf"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed foreign key (customer_type_id)(id) on table customer
INFO  [alembic.autogenerate.compare] Detected added foreign key (customer_type_id)(id) on table public.customer
  Generating /home/alex/alembic-issue/alembic/versions/20221125_095053_5cd5e42303f2_wtf.py ...  done

Versions.

• OS: Kubuntu
• Python: 3.8
• Alembic: 1.8.1
• SQLAlchemy: 1.8.1
• Database: Postgres 13.0
• DBAPI: postgresql

Additional context

• As you can see there is a bug with the inspection when using public schema.
• Maybe this is due to public being the default in search_path?
• If I use a different schema i.e. foo everything works fine, see branch schema in example project https://github.com/alexander-jacob/alembic-issue/tree/schema
• Maybe this is related to Redundant Foreign Key migrations (again) #519

Have a nice day!

[zzzeek]

* As you can see there is a bug with the inspection when using `public` schema.

it's actually not a bug.

* Maybe this is due to `public` being the default in `search_path`?

it is! Alembic's docs don't point out to this, it would likely be a good idea if they did have a little section on models with alternate schema names, but the bulk of this documentation is in the SQLAlchemy docs where it's assumed that's where people are building out models that use schema names, however, it's in reflection docs, so some more cross linking would be helpful here.

The general idea of how reflection interacts with schemas, where there is a "default" schema name, is here: https://docs.sqlalchemy.org/en/14/core/reflection.html#reflection-schema-qualified-interaction

Then, and you might want to just skip to this depending on how much reading you want to do, is the PostgreSQL-specific version of this, where the search_path thing tends to bring this up to 11: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

The tl;dr is:

1. keep search_path set to "public" like it always is
2. when naming "schema" in a Table or model, leave it empty if the name is "public", that is, the name that's in search_path. if the name is not in search_path, then you can set schema.

Can you do it differently? Sure! but 1 and 2 above are the "normal" way.

[ryukinix]

That's pretty confusing and not obvious for someone trying to go outside of the default path, which is not always reliable.

[CaselIT]

Do you have a better suggestions?