Re: online vs offline produce different "python stack traces" and general question

[HP3]

>
> Perfect, that's what I thought.


Thanks a lot Mike! 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[Mike Bayer]

you can change the nullability of a column with op.alter_column().
There's no need to create a separate constraint.

On Thu, Sep 6, 2018 at 8:07 PM, HP3  wrote:
> Sorry:
>
> nullable = False
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[HP3]

Sorry: 

nullable = False

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[HP3]

oops Sorry!

Yes, I get the same error when I run the offline generated .sql through 
psql.

At this point, I am running SA 1.2.11 and fudging the upgrade function I 
can bypass the error above.

Basically, there were few op.drop_table() I had to move to the bottom 
(after ### end Alembic commands ###).

The last thing I am trying to work out is a nullable constraint:

I have an existing table acting as a joining table in a many-to-many 
relationship. The new model adds a new column to that table and the 
`nullable = True` is set. Of course, there is no value for that column so 
the migration fails.

I was looking for a op.create_nullable_constraint() so I could remove it 
from the op.add_column().

I guess I could add the column without it and after I fill it with values, 
I could use op.alter_column(nullable=True). 

That should work, right?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[Mike Bayer]

On Thu, Sep 6, 2018 at 5:28 PM, HP3  wrote:
> Hi Mike,
>
> I bumped up to SA to v0.9.10 and alembic completed successfully adding a
> revision. IOW, I didn't have to "fudge" with neither the deadcbed_.py nor
> with the .sql (offline).
>
> However, when running either migration (online/offline), I received the same
> error as before.
>
> sqlalchemy.exc.InternalError: (InternalError) cannot drop table
> groups_history because other objects depend on it
> DETAIL: constraint folders_history_id_fkey on table folders_history depends
> on table groups_history
> constraint projects_history_id_fkey on table projects_history depends on
> table groups_history
> constraint userprofiles_history_id_fkey on table userprofiles_history
> depends on table groups_history
> HINT: Use DROP ... CASCADE to drop the dependent objects too.


how are you getting that error when you run "offline"?  do you mean
when you run the SQL script manually?

the error means that you have two DROP TABLE statements that are in
the incorrect order.   You have to order the drop_table() statements
manually for now, see
https://bitbucket.org/zzzeek/alembic/issues/146/take-topological-ordering-into-account




>
>
>
> (Even with SA 1.2.11, the migration file was nearly identical - there were
> couple of differences dealing with JSONB(astext_type=sa.Text()) and an
> autoincrement in a sequence)
>
>
> I suspect is something specific to our older model.
>
> I will try to craft a simple case that demonstrates the issue and post back.
>
> Thanks!
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[HP3]

Hi Mike,

I bumped up to SA to v0.9.10 and alembic completed successfully adding a 
revision. IOW, I didn't have to "fudge" with neither the deadcbed_.py nor 
with the .sql (offline).

However, when running either migration (online/offline), I received the 
same error as before.

sqlalchemy.exc.InternalError: (InternalError) cannot drop table 
groups_history because other objects depend on it
DETAIL: constraint folders_history_id_fkey on table folders_history depends 
on table groups_history
constraint projects_history_id_fkey on table projects_history depends on 
table groups_history
constraint userprofiles_history_id_fkey on table userprofiles_history 
depends on table groups_history
HINT: Use DROP ... CASCADE to drop the dependent objects too.



(Even with SA 1.2.11, the migration file was nearly identical - there were 
couple of differences dealing with JSONB(astext_type=sa.Text()) and an 
autoincrement in a sequence)


I suspect is something specific to our older model.

I will try to craft a simple case that demonstrates the issue and post back.

Thanks!


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[HP3]

Thank you very much Mike!

The older baseline is on v0.9.7 but I am not sure if I can bump it up 
'blindly' ... I think all constructs are "vanilla" so maybe it's safe.

In my v0.9.7 based model, I used history_meta recipe (slightly modified 
though).

At any rate, after fudging around with some operations (aka commenting 
things out, moving things after others) I managed to produce an offline 
.sql migration that when ran within a single transaction did fail exactly 
as the online migration did. Same exact DB error: "cannot drop table 
groups_history because ..."

At this point, I think I will continue fudging around with the order of 
things and eventually be able interject the bulk_updates/bulk_inserts I 
need.

I'll report back with results after bumping up from 0.9.7 to see if the 
error goes away.

Again, thanks a lot

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: online vs offline produce different "python stack traces" and general question

[Mike Bayer]

On Thu, Sep 6, 2018 at 1:14 PM, HP3  wrote:
> Hello,
>
> As I try alembic (for the first time), I ran into the following stack traces
> when attempting to conduct a "nasty" migration.
>
> I call it "nasty" because the original model was based on sqlalchemy v0.9 +
> python 2.7 and the new model is sqlalchemy v1.2 + python 3.6.
> ON TOP OF THAT, it's a massive table and model restructuring. Backend is
> postgres.
>
> I am thinking that I can leverage alembic to do the heavy lifting of
> creating/dropping stuff while I can interject my own bulk_inserts and
> bulk_updates and preserve the existing data.  Something like:
>
> // versions/deadbeef_nasty.py
>
> def upgrade():
> op.drop_table(...)
> op.drop_table(...)
> ...
>
> # Here is where I am planning to add my bulk_inserts and bulk_updates
>
> op.add_column(...)
> op.drop_column(...)
> ...
>
>
>
> Is this a reasonable approach?

yes


>
> With regards to the stack traces, they differ between online (first stack)
> and offline (second). Hence, this post.
> Does that make sense?


youre getting two totally different error conditions.  In the second
case, something is up with the naming_convention you've passed to
MetaData somewhere, this should be a dictionary value.  If that's not
the case then please confirm the version of SQLAlchemy in use.  0.9 is
very old so you might need to upgrade or at least test that the
SQLAlchemy version resolves.


>
> Question: are online migrations run within a single transaction or not?

this depends on if the database supports transactional DDL (Postgresql
does) and what you have the transaction_per_migration flag set
towards: 
http://alembic.zzzcomputing.com/en/latest/api/runtime.html?highlight=transaction_per_migration#alembic.runtime.environment.EnvironmentContext.configure.params.transaction_per_migration
defaults to False so all migrations are in one transaction for
Postgresql.



>
> Thanks!!!
>
>
> $ alembic upgrade head
>
> /Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/psycopg2/__init__.py:144:
> UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in
> order to keep installing from binary please use "pip install
> psycopg2-binary" instead. For details see:
> .
>
>  """)
>
> INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
>
> INFO [alembic.runtime.migration] Will assume transactional DDL.
>
> INFO [alembic.runtime.migration] Running upgrade 55ebe08ba589 ->
> b6c3cd0ef4cb, version 1
>
> Traceback (most recent call last):
>
>  File "/Users/hp3/.pyenv/versions/server_py2/bin/alembic", line 11, in
> 
>
>  sys.exit(main())
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/config.py",
> line 486, in main
>
>  CommandLine(prog=prog).main(argv=argv)
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/config.py",
> line 480, in main
>
>  self.run_cmd(cfg, options)
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/config.py",
> line 463, in run_cmd
>
>  **dict((k, getattr(options, k, None)) for k in kwarg)
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/command.py",
> line 254, in upgrade
>
>  script.run_env()
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/script/base.py",
> line 427, in run_env
>
>  util.load_python_file(self.dir, 'env.py')
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/util/pyfiles.py",
> line 81, in load_python_file
>
>  module = load_module_py(module_id, path)
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/util/compat.py",
> line 135, in load_module_py
>
>  mod = imp.load_source(module_id, path, fp)
>
>  File "alembic/env.py", line 72, in 
>
>  run_migrations_online()
>
>  File "alembic/env.py", line 67, in run_migrations_online
>
>  context.run_migrations()
>
>  File "", line 8, in run_migrations
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/runtime/environment.py",
> line 836, in run_migrations
>
>  self.get_context().run_migrations(**kw)
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/runtime/migration.py",
> line 330, in run_migrations
>
>  step.migration_fn(**kw)
>
>  File
> "/Users/hp3/Documents/python_workspace/Plannotate2_Cloud/src/server/alembic/versions/b6c3cd0ef4cb_version_1.py",
> line 38, in upgrade
>
>  op.drop_table('groups_history')
>
>  File "", line 8, in drop_table
>
>  File "", line 3, in drop_table
>
>  File
> "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-packages/alembic/operations/ops.py",
> line 1187, in drop_table
>
>  operations.invoke(op)
>
>  File
>

online vs offline produce different "python stack traces" and general question

[HP3]

Hello,

As I try alembic (for the first time), I ran into the following stack 
traces when attempting to conduct a "nasty" migration.

I call it "nasty" because the original model was based on sqlalchemy v0.9 + 
python 2.7 and the new model is sqlalchemy v1.2 + python 3.6. 
ON TOP OF THAT, it's a massive table and model restructuring. Backend is 
postgres.

I am thinking that I can leverage alembic to do the heavy lifting of 
creating/dropping stuff while I can interject my own bulk_inserts and 
bulk_updates and preserve the existing data.  Something like:

// versions/deadbeef_nasty.py

def upgrade():
op.drop_table(...)
op.drop_table(...)
...

# Here is where I am planning to add my bulk_inserts and bulk_updates

op.add_column(...)
op.drop_column(...)
...



*Is this a reasonable approach?*

*With regards to the stack traces, they differ between online (first stack) 
and offline (second). Hence, this post.*
*Does that make sense?*

*Question: are online migrations run within a single transaction or not?*

*Thanks!!!*


$ alembic upgrade head 

/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package 
will be renamed from release 2.8; in order to keep installing from binary 
please use "pip install psycopg2-binary" instead. For details see: 
. 


 """) 

INFO [alembic.runtime.migration] Context impl PostgresqlImpl. 

INFO [alembic.runtime.migration] Will assume transactional DDL. 

INFO [alembic.runtime.migration] Running upgrade 55ebe08ba589 -> 
b6c3cd0ef4cb, version 1 

Traceback (most recent call last): 

 File "/Users/hp3/.pyenv/versions/server_py2/bin/alembic", line 11, in 
 

 sys.exit(main()) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/config.py", line 486, in main 

 CommandLine(prog=prog).main(argv=argv) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/config.py", line 480, in main 

 self.run_cmd(cfg, options) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/config.py", line 463, in run_cmd 

 **dict((k, getattr(options, k, None)) for k in kwarg) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/command.py", line 254, in upgrade 

 script.run_env() 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/script/base.py", line 427, in run_env 

 util.load_python_file(self.dir, 'env.py') 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/util/pyfiles.py", line 81, in load_python_file 

 module = load_module_py(module_id, path) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/util/compat.py", line 135, in load_module_py 

 mod = imp.load_source(module_id, path, fp) 

 File "alembic/env.py", line 72, in  

 run_migrations_online() 

 File "alembic/env.py", line 67, in run_migrations_online 

 context.run_migrations() 

 File "", line 8, in run_migrations 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/runtime/environment.py", line 836, in run_migrations 

 self.get_context().run_migrations(**kw) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/runtime/migration.py", line 330, in run_migrations 

 step.migration_fn(**kw) 

 File "/Users/hp3/Documents/python_workspace/Plannotate2_Cloud/src/server/
alembic/versions/b6c3cd0ef4cb_version_1.py", line 38, in upgrade 

 op.drop_table('groups_history') 

 File "", line 8, in drop_table 

 File "", line 3, in drop_table 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/operations/ops.py", line 1187, in drop_table 

 operations.invoke(op) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/operations/base.py", line 319, in invoke 

 return fn(self, operation) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/operations/toimpl.py", line 70, in drop_table 

 operation.to_table(operations.migration_context) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/ddl/impl.py", line 198, in drop_table 

 self._exec(schema.DropTable(table)) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/alembic/ddl/impl.py", line 115, in _exec 

 return conn.execute(construct, *multiparams, **params) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/sqlalchemy/engine/base.py", line 729, in execute 

 return meth(self, multiparams, params) 

 File "/Users/hp3/.pyenv/versions/2.7.14/envs/server_py2/lib/python2.7/site-
packages/sqlalchemy/sql/dd