Run Alembic Migrations Outside of a Transaction Block
I recently ran into the following error when adding some indices to a PostgreSQL database from within an Alembic migration:
CREATE INDEX CONCURRENTLY cannot run inside a transaction block
Which makes sense, particularly if you're creating the table with the index in the same migration (transaction block) -- how can `CREATE INDEX` know anything about a table that doesn't exist yet?
There are a variety of SQL schema transforms (a.k.a. DDL statements) that work this way. Alembic migrations are always run inside a transaction, and breaking out a given statement is not immediately obvious. Luckily, Alembic provides a way for us to end the transaction early and execute statements on their own ("autocommit"):
def upgrade():
with op.get_context().autocommit_block():
op.execute("ALTER TYPE mood ADD VALUE 'soso'")It's important to note that any statements before the autocommit block will be committed! So it is probably best to do this last, at the end of your migration, in order to keep things simple.
And while this blog post speaks of PostgreSQL, this should apply to any database that SQLAlchemy supports!
More info: Alembic Documentation
H/T Stack Overflow
How to Set an Empty Dictionary/Hash as Default Value for a PostgreSQL HSTORE Column
July 30, 2024.
Tagged postgresql, programming, databases, hstore, the-more-you-know, sqlalchemy, alembic, activerecord, ruby.on.rails
Do you have an HSTORE column on your PostgreSQL database that you don't want to be `null` but need to have a default value? The syntax for this is a little irregular; so I'm posting it here for my own reference and yours:
my_column HSTORE DEFAULT '' NOT NULL
is the line in your `CREATE TABLE`command that you want.
In Ruby on Rails, using an ActiveRecord migration, you would use:
t.hstore :my_column, default: {}sa.Column('my_column', HSTORE(), nullable=False, server_default=sa.text("''")),Additionally, if you want your SQLAlchemy model object to initialize this column with said empty dictionary (instead of `None`), per this StackOverflow post you need take a couple of extra steps in your model:
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict
class Item(db.Model):
my_column = db.Column(MutableDict.as_mutable(HSTORE), nullable=False, default={}, server_default='')
def __init__(self, **kwargs):
kwargs.setdefault('my_column', {})
super(Item, self).__init__(**kwargs)