Cannot update view

[mfsjr]

Hi,

I am new to Alembic but seem to be able to use it, so far.

No problem getting the first migration for creating the view, ran update to head and it was created, no problems.

When I added one field to the view and autogenerated a new migration was created, but it appears to creating/dropping instead of replacing:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    public_user_display = PGView(
                schema="PUBLIC",
                signature="user_display",
                definition='select u.rowid, u.email, u.created_at, u.deleted_at \n        from users u'
            )

    op.create_entity(public_user_display)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    public_user_display = PGView(
                schema="PUBLIC",
                signature="user_display",
                definition='# not required for op'
            )

    op.drop_entity(public_user_display)
    # ### end Alembic commands ###

Any ideas?

[olirice]

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
"""
)

[mfsjr]

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()

[olirice]

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.

[mfsjr]

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.

[olirice]

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.

[olirice]

v2.6 is out

[mfsjr]

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?

[olirice]

@mfsjr I moved that into a new issue because its a different topic. I'll respond in #5

[olirice]

Feel free to re-open this issue if you feel the original question wasn't resolved.