Conditionally creating tables in PostgresQL with "unlogged" prefix in certain environments

[sihil]

I would like to invoke create table migration operations with a prefix of "unlogged" when running in local and continuous integration environments but without that prefix when the migrations are run elsewhere. From what I understand about the SQLalchemy architecture I think this needs to happen in alembic.

I've considered/investigated various options:

1. write an SQLalchemy metaclass to adjust this, but this happens too early and is baked into the migrations
2. seeing if there is a configuration option that allows a function to be specified that can modify operations before they are invoked (there seem to be options that allow operations to be filtered but not modified)
3. create a new dialect called 'postgres-unlogged' which always creates tables with the unlogged and switch out the dialect depending on environment
4. build something to run operations directly inspired by the docs so I can modify the generated operations before invocation

Out of these I would much prefer 2 being available (perhaps there is a reason this isn't implemented). I've got closest with 4, although my solution doesn't deal with sqlalchemy plugins at the moment and feels like it will be quite brittle.

If anyone has done something similar I'd love to hear about it.

If you have pointers for which of the above will be the cleanest solution or another solution I've not considered I'd love to hear about it.

footnote: I'm a career-long developer but just under a year with Python/SQLalchemy/Alembic

[zzzeek]

I would like to invoke create table migration operations with a prefix of "unlogged" when running in local and continuous integration environments but without that prefix when the migrations are run elsewhere. From what I understand about the SQLalchemy architecture I think this needs to happen in alembic.

OK, there are ways to affect that at the SQLAlchemy level as well, a table prefix is a pretty simple thing so a compiles against CreateTable could achieve this, if you had the compiler hook looking at a global variable.

I've considered/investigated various options:

1. write an SQLalchemy metaclass to adjust this, but this happens too early and is baked into the migrations

I would stay away from metaclasses in all circumstances. SQLAlchemy itself uses them only for py2 compat, in 2.0 they are mostly replaced with __init_subclass__() methods. but beyond that, monkeypatching into SQLAlchemy internals is usually going to lead to problems.

2. seeing if there is a configuration option that allows a function to be specified that can modify operations before they are invoked (there seem to be options that allow operations to be filtered but not modified)

Alembic is also going to use that same CreateTable construct, so hooking into that with @compiles is one way to go, but also, if you have migration scripts written, is it not an option to simply place a conditional in the migration script itself where it runs op.create_table() ? as far as a general event system for Alembic operations, that is not implemented right now though there might be an issue somewhere that it would be nice to have someday. It may also be possible to use the register_operation hook to re-register a new "create_table" operation which delivers this additional context in an automated fashion.

3. create a new dialect called 'postgres-unlogged' which always creates tables with the unlogged and switch out the dialect depending on environment

in this case you are ultimately looking to swap out the visit_create_table method , which is exactly what the @compiles hook does.

4. build something to run operations directly inspired by [the docs](https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-alembic-operation-objects-directly-as-in-from-autogenerate) so I can modify the generated operations before invocation

I think a @compiles hook is the most expedient approach here with custom register_operation around op.create_table() another potentially interesting approach. in both cases it seems like they'd be looking at some global state which indicates the additional table directives you want to affect.

[sihil]

Thanks for taking the time to look at and reply to this @zzzeek, very much appreciated.

I had not come across the @compiles hook and it looks like a promising approach, I will explore that and see how I get on.

[sihil]

Played with this today and it makes the task in hand trivial. I've found myself doing the following (a simplified version, the real one inspects my global config etc) which works as expected but somewhat naughtily accesses a member indicated as private.

@compiles(CreateTable)
def prefix_create_table(create_table, compiler, **kw):
    create_table.element._prefixes = ["UNLOGGED"]
    return compiler.visit_create_table(create_table, **kw)

Thanks again @zzzeek for your time helping me out, it has saved a lot of digging and the result is simple.

[CaselIT]

There is no public api to check the table prefixes. It should be fine to use the private attribute

@sihil I would probably do instead

if ["UNLOGGED"] not in create_table.element._prefixes
  create_table.element._prefixes.append("UNLOGGED")

to avoid removing eventual additional prefixes that are (will be) added to a table

[zzzeek]

Safer would be to just get the "CREATE TABLE" text from the compiler and augment the UNLOGGED prefix:

text = text.replace("CREATE TABLE ", "CREATE TABLE UNLOGGED ")

[sihil]

Thanks @CaselIT - good shout, that's actually very similar to my non-simplified version!

Thanks for the tip @zzzeek (although I note it is CREATE UNLOGGED TABLE if anyone else comes to this later), still a bit of a hack but 💯 for not touching private APIs.

After doing this I'm seeing about a 5/6% performance gain in my tests. Not nothing, but less than I'd hoped for.

[prowler0305]

I came across this as part of trying out a solution I found on StackOverflow that was provided by Mike Bayer about doing partitioning with Oracle.

which can solve my issue. Although I'm struggling on how to get alembic to run the function decorator with @compiles when I do run a "migrate" command to create my migration file. Assuming I understand it is suppose to work that way in the first place.

Any advice would be greatly appreciated.