Failed to drop view when working with Postgresql in alembic migration script

[junepeach]

Hi Michael,

I have several views defined in alembic migration script, and so I defined op.execute('drop view xxx') to drop all of my view sql files. When I ran script 'alembic downgrade -1' to drop all of the tables, views, indexes, MySQL, SQLITE3 worked fine. When working with Postgresql, I got below:
qlalchemy.exc.InternalError: (InternalError) cannot drop table tbl_1 because other objects depend on it
DETAIL: view view_1 depends on table tbl_1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
'\nDROP TABLE tbl_1' {}

Does sqlalchemy/alembic support 'drop table xxx cascade' for most databases?

Thanks,

[Michael Bayer]

the "DROP..CASCADE" thing is a Postgresql thing. The command will do something different on Postgresql than the other backends; it will drop other objects as well.

So you might want to make sure you're dropping all the things the view is dependent on, also, for cross-platform compatibility.

I thought we had a flag for the "CASCADE" thing but we currently don't. There's ways to get that but I think you need to drop the views directly here.

[junepeach]

Thanks for the suggestion.I solved the problem by putting the dependents' drop command first in function def downgrade():
For example, if A refers to/uses B, I need to drop B first, then drop A. I don't know other better way. Will Alembic support 'cascade' in future version?

[Michael Bayer]

A "cascade" feature requires reading the database. For Alembic, reading the database occurs in the "autogenerate" feature, which will render the "drop_table()" instructions in the correct order in your migration script.

if not using autogenerate, the reading has to be done elsewhere. This is a long-supported feature in SQLAlchemy but is not usually as applicable to Alembic, as Alembic is trying to get you to create migrations that work without reading the database. The "drop tables in the correct order" feature requires that the foreign key relationships be known between the tables. If not using autogenerate and not specifying the order directly, you can let SQLAlchemy "reflect" those details from the database. But these approaches won't work in --sql mode.

A migration script such as this will do it:

m = MetaData()

t1 = Table('table1', m, Column('id', Integer, primary_key=True))
t2 = Table('table2', m, Column('id', Integer, primary_key=True), Column('table1id', Integer, ForeignKey('t1.id')))

m.drop_all(op.get_bind(), checkfirst=False)

Or this:

m = MetaData()

t1 = Table('table1', m, autoload=True, autoload_with=op.get_bind())
t2 = Table('table2', m, autoload=True, autoload_with=op.get_bind())

m.drop_all(op.get_bind(), checkfirst=False)

the above two scripts require live DB access and also are more verbose, but that approach is an option if you don't want to manually sort the DROP commands.

On Nov 1, 2012, at 9:54 AM, junepeach wrote:

> Thanks for the suggestion.I solved the problem by putting the dependents' drop command first in function def downgrade():
> For example, if A refers to/uses B, I need to drop B first, then drop A. I don't know other better way. Will Alembic support 'cascade' in future version?
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/WwmZXJN_oXEJ.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[junepeach]

Thank you, I will test your above idea. By the way, I am new to both Alembic and Sqlalchemy. Right now our database has around 90 tables, and 12 views (could be more), and we would like to support as many databases as we can.

Postgresql works fine with both 'alembic revision --autogenerate' and 'alembic upgrade head' commands, but failed with 'alembic downgrade -1' command, giving error:
sqlalchemy.exc.InternalError: (InternalError) cannot drop view view_1 because other objects depend on it
DETAIL: view view_2 depends on view view_1

HINT: Use DROP ... CASCADE to drop the dependent objects too.

'drop view view_1{}

When I removed all of op.execute('drop view xxx') and op.execute('create view xxx as ...') scripts, all of commands work fine even in Postgresql. I think this is because I have put clear relationship in table class in my schema module:

class Mytable(Base):
__tablename__ = 'tbl_mytable
id = Column(String(15), primary_key = True)
name = Column(String(25))
...
children = relationship('Yourtable')

but for view sql statement, I can just use pure sql code op.execute('...') to do both 'create' and 'drop' view work, no relationship involved. Surely it is true that we don't deal with any relationship between view and view, or between view and table in all of the database systems which should be very complicated.

[Michael Bayer]

On Nov 1, 2012, at 12:15 PM, junepeach wrote:

> When I removed all of op.execute('drop view xxx') and op.execute('create view xxx as ...') scripts, all of commands work fine even in Postgresql. I think this is because I have put clear relationship in table class in my schema module:
>
> class Mytable(Base):
> __tablename__ = 'tbl_mytable
> id = Column(String(15), primary_key = True)
> name = Column(String(25))
> ...
> children = relationship('Yourtable')

er no, relationship() has nothing to do with the structures that are created in your database. The error you are getting clearly states that you've emitted a "CREATE VIEW" that depends upon another VIEW in your postgresql schema, so that dependee-view cannot be dropped without dropping the dependent view.

[junepeach]

Yes, it is true for the view, but I also got below error when a view references tables:

qlalchemy.exc.InternalError: (InternalError) cannot drop table tbl_1 because other objects depend on it

DETAIL: view view_1 depends on table tbl_1

HINT: Use DROP ... CASCADE to drop the dependent objects too.

'\nDROP TABLE tbl_1' {}

[Michael Bayer]

This is all in your PG database. The error you see there, is generated by your Postgresql database. If you went to your psql console, and tried to DROP the same view, you'd get the same errors. At this stage, you have to emit the DROP statements in the correct order, and that has nothing to do with the existing Python application.

If, on the other hand, you're trying to determine how the mappings and table metadata you create in your app are rendering the CREATE statements that created these views in the first place, then that's a separate issue. You should not be focusing on the DROP side in this case and instead on the CREATE side, if things are being created that you do not expect.

[junepeach]

Thanks Michael, it is very helpful :)