'relation “public.alembic_version” does not exist' when using `version_table_schema` (X-Post Stackoverflow)

[Zack S]

(X-Post from Stackoverflow: http://stackoverflow.com/q/36511941/703040)

I'm writing some custom code for Alembic to keep my database always updated in my dev environment for a project. The project involves a database with the following:

• A public schema for shared data
  
• A single schema per client "database"
  
• One schema that acts as a prototype for all of the client schemas (orgs)
  

At this moment, I'm not worried about the multiple client schemas, only keeping the public and prototype schemas up-to-date. My env.py script works great for the public schema, but not prototype because alembic is trying to use the version table from public when working with prototype.

So, I thought I could use the version_table_schema option to maintain one version table in the public schema and one in the prototype schema. However, as soon as I start using that, I get a 'relation "public.alembic_version" does not exist' error when I attempt to do the upgrade.

The only difference that I see is that, when I use version_table_schema set to the appropriate schema, the generated revision scripts actually contain a line to op.drop_table('alembic_version'). The line ONLY exists when version_table_schema is in use.

I'm hoping that I'm just missing something minor.

I've posted the source files on SO already if they are helpful.

Thanks!

[Mike Bayer]

On 04/11/2016 12:34 PM, Zack S wrote:
> (X-Post from Stackoverflow: http://stackoverflow.com/q/36511941/703040)
>
>
> I'm writing some custom code for Alembic to keep my database always
> updated in my dev environment for a project. The project involves a
> database with the following:
>

> * A |public| schema for shared data
> * A single schema per client "database"
> * One schema that acts as a |prototype| for all of the client schemas

> (orgs)
>
> At this moment, I'm not worried about the multiple client schemas, only
> keeping the |public| and |prototype| schemas up-to-date. My env.py
> script works great for the |public| schema, but not |prototype| because
> alembic is trying to use the version table from |public| when working
> with |prototype|.
>
> So, I thought I could use the |version_table_schema|

> <http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=version_table_schema#alembic.runtime.environment.EnvironmentContext.configure.params.version_table_schema> option

> to maintain one version table in the |public| schema and one in the
> |prototype| schema. However, as soon as I start using that, I get a

> '/relation "public.alembic_version" does not exist/' error when I

> attempt to do the upgrade.
>
> The only difference that I see is that, when I use
> |version_table_schema| set to the appropriate schema, the generated
> revision scripts actually contain a line to

> |op.drop_table('alembic_version')|. The line ONLY exists when

> |version_table_schema| is in use.
>
> I'm hoping that I'm just missing something minor.

you're likely hitting the very confusing schema rules that apply to
Postgresql. See
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
for details. Short answer is that schema of "blank" and schema of
"public" are two different things on the Python side, leading to a lot
of confusion.

In order to convince autogenerate to not affect alembic_version at all
no matter where it pops up, you probably need to create an exclusion
rule using include_object:
http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object

def include_object(object, name, type_, reflected, compare_to):
if (type_ == "table" and name == 'alembic_version'):
return False
else:
return True

>
>
> I've posted the source files on SO already

> <http://stackoverflow.com/q/36511941/703040> if they are helpful.
>
>
> 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-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Zack S]

The include_object function did it!

I hadn't even thought that alembic might be cannibalizing its own version tables but it makes sense since the different upgrade scripts were not aware of one another.

Thanks so much!

> an email to sqlalchemy-alembic+unsub...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsub...@googlegroups.com>.