Comments (9)
Could you please provide:
- python version
- alembic version
- alembic_utils version
- exact definition used for the initial PGView
- exact definition used for the updated PGView
- contents of your
env.py
by "exact definition used for XXX PGView" I mean
my_view = PGView(
schema='public',
signature='user_display',
definition="""
SELECT
u.rowid,
u.email,
u.created_at,
u.deleted_at
FROM
users u
"""
)
from alembic_utils.
Thanks for the quick response. First the versions:
Python 3.8.2
alembic==1.4.2
alembic-utils==0.2.5
Initial view:
user_display = PGView(
schema="PUBLIC",
signature="user_display",
definition="""
select u.rowid, u.email, u.created_at from users u
"""
)
Updated view:
user_display = PGView(
schema="PUBLIC",
signature="user_display",
definition="""
select u.rowid, u.email, u.created_at, u.deleted_at from users u
"""
)
My env.py:
# These two lines of code are needed to add the parent directory to the PYTHONPATH so it can find the parent directory
# see https://stackoverflow.com/questions/57468141/alembic-modulenotfounderror-in-env-py
import sys
sys.path = ['', '..'] + sys.path[1:]
# end stanza for PYTHONPATH fix
# noinspection Mypy
from alembic_utils.replaceable_entity import register_entities
from user_display_view import user_display
from models import Base
from logging.config import fileConfig
from sqlalchemy import engine_from_config # type: ignore
from sqlalchemy import pool
from alembic import context # type: ignore
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata
# target_metadata = None
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
register_entities([user_display])
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection, target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
from alembic_utils.
Thank you, I was able to reproduce the problem.
The issue is that once the view is created in the database, it is retrieved with the schema name public
which fails the equality test with the local definition because its schema name is PUBLIC
.
I will fix this in a next release within a few days.
You can avoid this problem for now by replacing "PUBLIC"
with "public"
in your PGView
definitions.
from alembic_utils.
Thanks very much! I changed from "PUBLIC" to "public" and the view update was reflected by alembic by replacing the view, works now. I'll upgrade to the new version when I see it.
Thanks again, this will make a difference to our dev process.
from alembic_utils.
Thought about this a little more
The error you experienced is small edge of a much larger problem where is that any schema, view, or function names containing an uppercase character will be incorrectly handled.
One issue with the "easy fix" of changing the equality checks to be case insensitive is that it prevents a valid usecase where casing is the only difference between two schema names (or view/function names)
For example, this is valid (although probably not a good idea)
create schema dev;
create schema "DEV";
To support that, schema names and names defined in the signature blocks are treated as case sensitive in v0.2.6
+
That means your example would still fail, but it would have failed when you first tried to create the view with an error No schema named "PUBLIC"
. I think that behavior is easier to understand and debug than incorrect migration output you experienced.
To be clear, that means you will still need to use public
rather than PUBLIC
in your PGView
definitions.
from alembic_utils.
v2.6 is out
from alembic_utils.
I rediscovered an unpleasant memory in preparing to retest with v2.6.
Dropping a column from a view cannot be handled using "create or replace", which returns "ERROR: cannot drop columns from view", Postgres docs confirm this, and "alter view" doesn't do it either.
It appears the concern is breaking other views that could depend on the soon-to-be-dropped column (good), even if there are none (bad).
Dropping the view and then dispatching "create or replace" works. If there is a dependent view, the drop will fail, telling you what depends on the view (good).
So it seems that in order for view migrations to work in both directions, "create or replace view" must first drop the view in question. That will fail with a sensible message if there are dependent views and succeed otherwise.
Would it be possible to generate upgrades and downgrades that way, first dropping then creating or replacing?
from alembic_utils.
@mfsjr I moved that into a new issue because its a different topic. I'll respond in #5
from alembic_utils.
Feel free to re-open this issue if you feel the original question wasn't resolved.
from alembic_utils.
Related Issues (20)
- [Question] Using Alembic Utils without SQL Alchemy models HOT 3
- [QUESTION] compatibility with sqlalchemy_utils.functions.create_database HOT 2
- Compatibility with SQLAlchemy 2.0 HOT 4
- How to add a function with an " text[] default array['text', 'text2']"? HOT 1
- PGExtension replace `create` with `create if not exists` HOT 4
- Must be owner of materialized view mat_view_name HOT 2
- Alembic autogenerate broken for 'internal' PG functions HOT 5
- INFO: Transaction approach incompatible with MySQL
- alembic check broken - diff cannot be rendered HOT 1
- Materialized view change detection fails if upstream view has changed HOT 6
- DropOp dependency ordering when dropping multiple associated entities HOT 4
- Publishing next release HOT 1
- remove duplicate "instance" ref
- Duplicated ReplaceableEntity on every migration HOT 5
- PGFunction detection of plpgsql doesn't account for language definition wrapped in quotes HOT 1
- gracefully handle when a migrator needs to modify a column that a view depends on HOT 8
- Creating an ORM Table mapping on a view will generate a migration to create a table HOT 4
- [Question] How to add a unique index to definition for PGMaterializedView HOT 4
- Colon character escaped unnecessarily in view autogeneration HOT 4
- Downgrade does not reflect old definition
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from alembic_utils.