PostGIS not work with SQLAlchemy==2.0.19 #1282
-
|
Describe the bug preparation: pip install -U alembic==1.11.1 Flask-Migrate==4.0.4 Flask-SQLAlchemy==3.0.5 GeoAlchemy2==0.14.0 SQLAlchemy==2.0.19 psycopg2-binary==2.9.3docker run -d --name=postgis-test -p 5432:5432 -e POSTGRES_PASSWORD=123456 -e POSTGRES_DB=test postgis/postgis:12-3.2steps:
output: (venv) D:\workspace\test\test_sqlalchemy>flask db init
Creating directory D:\workspace\test\test_sqlalchemy\migrations ... done
Creating directory D:\workspace\test\test_sqlalchemy\migrations\versions ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\alembic.ini ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\env.py ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\README ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\script.py.mako ... done
Please edit configuration/connection/logging settings in 'D:\\workspace\\test\\test_sqlalchemy\\migrations\\alembic.ini' before proceeding.
(venv) D:\workspace\test\test_sqlalchemy>flask db migrate
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'lake'
INFO [alembic.autogenerate.compare] Detected added index 'idx_lake_geom' on '['geom']'
INFO [alembic.ddl.postgresql] Detected sequence named 'faces_gid_seq' as owned by integer column 'faces(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_faces_countyfp' on 'faces'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_faces_tfid' on 'faces'
INFO [alembic.autogenerate.compare] Detected removed index 'tiger_faces_the_geom_gist' on 'faces'
INFO [alembic.autogenerate.compare] Detected removed table 'faces'
INFO [alembic.ddl.postgresql] Detected sequence named 'addrfeat_gid_seq' as owned by integer column 'addrfeat(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_geom_gist' on 'addrfeat'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_tlid' on 'addrfeat'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_zipl' on 'addrfeat'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_addrfeat_zipr' on 'addrfeat'
INFO [alembic.autogenerate.compare] Detected removed table 'addrfeat'
INFO [alembic.autogenerate.compare] Detected removed table 'zip_lookup_base'
INFO [alembic.ddl.postgresql] Detected sequence named 'pagc_gaz_id_seq' as owned by integer column 'pagc_gaz(id)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'pagc_gaz'
INFO [alembic.ddl.postgresql] Detected sequence named 'cousub_gid_seq' as owned by integer column 'cousub(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'tige_cousub_the_geom_gist' on 'cousub'
INFO [alembic.autogenerate.compare] Detected removed table 'cousub'
INFO [alembic.autogenerate.compare] Detected removed table 'zip_lookup_all'
INFO [alembic.autogenerate.compare] Detected removed index 'countysub_lookup_name_idx' on 'countysub_lookup'
INFO [alembic.autogenerate.compare] Detected removed index 'countysub_lookup_state_idx' on 'countysub_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'countysub_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'zip_state_loc'
INFO [alembic.autogenerate.compare] Detected removed table 'layer'
INFO [alembic.ddl.postgresql] Detected sequence named 'pagc_lex_id_seq' as owned by integer column 'pagc_lex(id)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'pagc_lex'
INFO [alembic.autogenerate.compare] Detected removed index 'county_lookup_name_idx' on 'county_lookup'
INFO [alembic.autogenerate.compare] Detected removed index 'county_lookup_state_idx' on 'county_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'county_lookup'
INFO [alembic.ddl.postgresql] Detected sequence named 'county_gid_seq' as owned by integer column 'county(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_county' on 'county'
INFO [alembic.autogenerate.compare] Detected removed table 'county'
INFO [alembic.autogenerate.compare] Detected removed table 'loader_platform'
INFO [alembic.ddl.postgresql] Detected sequence named 'bg_gid_seq' as owned by integer column 'bg(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'bg'
INFO [alembic.ddl.postgresql] Detected sequence named 'zcta5_gid_seq' as owned by integer column 'zcta5(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'zcta5'
INFO [alembic.autogenerate.compare] Detected removed index 'direction_lookup_abbrev_idx' on 'direction_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'direction_lookup'
INFO [alembic.ddl.postgresql] Detected sequence named 'addr_gid_seq' as owned by integer column 'addr(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_addr_tlid_statefp' on 'addr'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_addr_zip' on 'addr'
INFO [alembic.autogenerate.compare] Detected removed table 'addr'
INFO [alembic.autogenerate.compare] Detected removed table 'state_lookup'
INFO [alembic.ddl.postgresql] Detected sequence named 'pagc_rules_id_seq' as owned by integer column 'pagc_rules(id)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'pagc_rules'
INFO [alembic.ddl.postgresql] Detected sequence named 'state_gid_seq' as owned by integer column 'state(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_state_the_geom_gist' on 'state'
INFO [alembic.autogenerate.compare] Detected removed table 'state'
INFO [alembic.ddl.postgresql] Detected sequence named 'featnames_gid_seq' as owned by integer column 'featnames(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_lname' on 'featnames'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_snd_name' on 'featnames'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_featnames_tlid_statefp' on 'featnames'
INFO [alembic.autogenerate.compare] Detected removed table 'featnames'
INFO [alembic.autogenerate.compare] Detected removed table 'loader_lookuptables'
INFO [alembic.autogenerate.compare] Detected removed table 'tabblock20'
INFO [alembic.autogenerate.compare] Detected removed table 'geocode_settings_default'
INFO [alembic.ddl.postgresql] Detected sequence named 'tract_gid_seq' as owned by integer column 'tract(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'tract'
INFO [alembic.ddl.postgresql] Detected sequence named 'tabblock_gid_seq' as owned by integer column 'tabblock(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed table 'tabblock'
INFO [alembic.autogenerate.compare] Detected removed index 'secondary_unit_lookup_abbrev_idx' on 'secondary_unit_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'secondary_unit_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'loader_variables'
INFO [alembic.autogenerate.compare] Detected removed table 'geocode_settings'
INFO [alembic.ddl.postgresql] Detected sequence named 'place_gid_seq' as owned by integer column 'place(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'tiger_place_the_geom_gist' on 'place'
INFO [alembic.autogenerate.compare] Detected removed table 'place'
INFO [alembic.autogenerate.compare] Detected removed index 'street_type_lookup_abbrev_idx' on 'street_type_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'street_type_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'zip_state'
INFO [alembic.ddl.postgresql] Detected sequence named 'edges_gid_seq' as owned by integer column 'edges(gid)', assuming SERIAL and omitting
INFO [alembic.autogenerate.compare] Detected removed index 'idx_edges_tlid' on 'edges'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_edges_countyfp' on 'edges'
INFO [alembic.autogenerate.compare] Detected removed index 'idx_tiger_edges_the_geom_gist' on 'edges'
INFO [alembic.autogenerate.compare] Detected removed table 'edges'
INFO [alembic.autogenerate.compare] Detected removed table 'zip_lookup'
INFO [alembic.autogenerate.compare] Detected removed index 'place_lookup_name_idx' on 'place_lookup'
INFO [alembic.autogenerate.compare] Detected removed index 'place_lookup_state_idx' on 'place_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'place_lookup'
INFO [alembic.autogenerate.compare] Detected removed table 'topology'
Generating D:\workspace\test\test_sqlalchemy\migrations\versions\7375114c2e0a_.py ... doneInstall SQLAlchemy==1.4.49: steps:
ouput: (venv) D:\workspace\test\test_sqlalchemy>flask db init
Creating directory D:\workspace\test\test_sqlalchemy\migrations ... done
Creating directory D:\workspace\test\test_sqlalchemy\migrations\versions ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\alembic.ini ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\env.py ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\README ... done
Generating D:\workspace\test\test_sqlalchemy\migrations\script.py.mako ... done
Please edit configuration/connection/logging settings in 'D:\\workspace\\test\\test_sqlalchemy\\migrations\\alembic.ini' before proceeding.
(venv) D:\workspace\test\test_sqlalchemy>flask db migrate
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'lake'
INFO [alembic.autogenerate.compare] Detected added index 'idx_lake_geom' on '['geom']'
Generating D:\workspace\test\test_sqlalchemy\migrations\versions\d3335fad144e_.py ... doneExpected behavior To Reproduce from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy
from geoalchemy2 import Geometry
from geoalchemy2.alembic_helpers import include_object, render_item
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:123456@localhost:5432/test"
db = SQLAlchemy(app)
Migrate(app=app, db=db, compare_type=True, include_object=include_object, render_item=render_item)
class Lake(db.Model):
__tablename__ = 'lake'
id = db.Column(db.Integer, primary_key=True)
geom = db.Column(
Geometry(
geometry_type='LINESTRING',
srid=4326,
spatial_index=True,
)
)Error SQLAlchemy==1.4.49 is ok, but not SQLAlchemy==2.0.19 I guess alembic detected additional schemas besides
Versions.
Additional context Have a nice day! |
Beta Was this translation helpful? Give feedback.
Replies: 10 comments 16 replies
-
|
Hi, It's not clear that the issue is, sorry. |
Beta Was this translation helpful? Give feedback.
-
|
@CaselIT Except for public, tables in other schemas will be deleted. This is definitely not right in SQLAlchemy==2.0.19.
|
Beta Was this translation helpful? Give feedback.
-
|
It seems strange that the sqlalchemy causes this change in behaviour. The issue here may be in any of the other packages you are using. For example the ignore object is not working as intended, or the flask migrate may not work correctly. You can try limiting the schema using https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_schemas and/or provide a custom https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_name To offer more help a reproducible example that does not use 3rd party sw (other than sqlalchemy) would be the best option |
Beta Was this translation helpful? Give feedback.
-
|
I did a minimal test using only sqlalchemy, alembic. There will be the same problem. test steps:
output: |
Beta Was this translation helpful? Give feedback.
-
|
Not using geoalchemy2 will also have the same problem, but |
Beta Was this translation helpful? Give feedback.
-
|
I'm not sure why the sqlalchemy version matters here. Alembic behaves the same regardless of sqlalchemy version. How are all the tables that alembic removes created? |
Beta Was this translation helpful? Give feedback.
-
|
These tables are created at container startup. docker run -d --name=postgis-test -p 5432:5432 -e POSTGRES_PASSWORD=123456 -e POSTGRES_DB=test postgis/postgis:12-3.2
Under normal circumstances, only public will be detected, but all schemas. That's why so many tables have been removes.
|
Beta Was this translation helpful? Give feedback.
-
|
All those tables are not listed with a schema in the alembic migration created by autogenetate, so I'm assuming that they are retuneed when looking in the default schema for some reason |
Beta Was this translation helpful? Give feedback.
-
|
I agree with your idea, or perhaps |
Beta Was this translation helpful? Give feedback.
-
|
@CaselIT Hi, I have found evidence of sqlalchemy adjusting the schema, only retaining the schema except for And, I understand that when executing https://github.com/sqlalchemy/alembic/blob/main/alembic/autogenerate/compare.py#L101 @comparators.dispatch_for("schema")
def _autogen_for_tables(
autogen_context: AutogenContext,
upgrade_ops: UpgradeOps,
schemas: Union[Set[None], Set[Optional[str]]],
) -> None:
inspector = autogen_context.inspector
conn_table_names: Set[Tuple[Optional[str], str]] = set()
version_table_schema = (
autogen_context.migration_context.version_table_schema
)
version_table = autogen_context.migration_context.version_table
for schema_name in schemas:
tables = set(inspector.get_table_names(schema=schema_name))
if schema_name == version_table_schema:
...Is there any way to change the schemas, which default to the set {None}? |
Beta Was this translation helpful? Give feedback.
-
|
I understood what's going on here.
this means that all tables are reflected by sqlalchemy 2 when using the search path set by postgis. Also it may make sense mentioning this to geoalembic, since I'm guessing this may be a question of interest to other people. cc @zzzeek |
Beta Was this translation helpful? Give feedback.
-
|
It's not worked. Can the default value of schema be set to |
Beta Was this translation helpful? Give feedback.
-
Not sure what is not working. To change the schema i believe you need to set it on the metadata |
Beta Was this translation helpful? Give feedback.
-
|
What I am puzzled about is why the schemas configuration item is not open, only Another question (source code), if include_schemas:
schemas = set(inspector.get_schema_names())
# replace default schema name with None
schemas.discard("information_schema")
# replace the "default" schema with None
schemas.discard(default_schema)
schemas.add(None)
else:
schemas = {None}There is an opportunity to make |
Beta Was this translation helpful? Give feedback.
-
|
hi - the list of schema names you'd like to inspect can be customized by:
i saw some other comment about "can the default schema be "public"" - this is controlled on the PostgreSQL side using the search_path variable. this should be left at its default of "public" for the connection that you use with Alembic or SQLAlchemy in general (see https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#postgresql-schema-reflection for background). if you are not able to change search_path in the database connection configuration you are using, and want to set search_path on each connection within an alembic env.py script each time it connects, follow the example at https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-databases . |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, def include_name(name, type_, parent_names):
print(33333, name, type_, parent_names)
if type_ == "schema":
return False
else:
return TrueStrangely, there was no |
Beta Was this translation helpful? Give feedback.
-
|
@luolingchun Can you explain how this worked for you? I have tried setting up |
Beta Was this translation helpful? Give feedback.
-
|
Nevermind, I misread the comment from above. Search path can be explicitly passed into database drivers! |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, @luolingchun, this fix worked. For anyone wondering where to write this change, you can do it like this (in your alembic def _include_name(name, type_, parent_names):
# Ignore all schemas except the public schema
# This is because we don't want to create tables from geoalchemy2
if type_ == "schema":
return False
else:
return TrueAnd then: context.configure(..., include_name=_include_name) |
Beta Was this translation helpful? Give feedback.
-
|
Here is a method that uses I have always had a desire to explicitly specify the schema (most of the time we only operate on a specific schema), which would enable automated online migration instead of modifying env.py, and make it easier to understand. I'm not sure what the drawbacks are, to the point that the authors of Alembic and Flask-Migrate do not endorse this idea. |
Beta Was this translation helpful? Give feedback.
-
|
depends on what you want to achieve. that example will make alembic use that schema as the default one, so that all object that don't have a schema explicitly set are created on that schema. |
Beta Was this translation helpful? Give feedback.
-
|
For anyone who found this discussion now, I found a workaround for you. According to issue from postgis/postgis, extra tables and schemas (such like docker-compose.yml postgres:
image: postgis/postgis:15-3.4
ports:
- "5432:5432"
volumes:
- ./postgres-data:/var/lib/postgresql/data
+ - ./postgres/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
...postgres/docker-entrypoint-initdb.d/docker-entrypoint-initdb.d/01.sql + CREATE extension postgis;Now you are only using postgis extension. However, postgis extension make a table env.py + IGNORE_TABLES: list[str] = ["spatial_ref_sys"]
+ def include_object(
+ object: SchemaItem,
+ name: Optional[str],
+ type_: Literal[
+ "schema",
+ "table",
+ "column",
+ "index",
+ "unique_constraint",
+ "foreign_key_constraint",
+ ],
+ reflected: bool,
+ compare_to: Optional[SchemaItem]
+ ) -> bool:
+ if type_ == 'table' and (name in IGNORE_TABLES or object.info.get("skip_autogenerate", False)):
+ return False
+ return True
def run_migrations_offline() -> None:
...
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
+ include_object=include_object,
)
...
def run_migrations_online() -> None:
...
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata,
+ include_object=include_object,
)
... |
Beta Was this translation helpful? Give feedback.
-
|
After a while, I found it more convenient to use app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:123456@localhost:5432/test?options=-csearch_path=public" |
Beta Was this translation helpful? Give feedback.
-
|
I think there is actually a bug in the Alembic/SQLAlchemy interaction. The gist of it is that for Alembic, That's how you get stuff outside of the Let me know if you want a better issue/explanation @CaselIT / @zzzeek. Surgically modifying this line as below fixes the issue, but it's obviously very PG specific. tables = set(inspector.get_table_names(schema=schema_name if schema_name is not None else 'public')) |
Beta Was this translation helpful? Give feedback.
-
As far as I know alembic and sqlalchemy threat none as the same, meaning in postgresql anything the user can see. This is evidenced by the fact that the changes line is in alembic |
Beta Was this translation helpful? Give feedback.
After a while, I found it more convenient to use
search_pathin database connections.