1

I want to change the size of a String column in my PostgreSQL database through alembic.

My first attempt in my local DB was the more straightforward way and the one that seemed logic:

Change the size of the db.Column field I wanted to resize and configure alembic to look for type changes as stated here: add the compare_type=True parameter to the context.configure() of my alembic/env.py. And then run alembic revision --autogenerate which correctly generated a file calling alter_column.

This seems to be OK, but the alembic upgrade head was taking so much time because of the alter column that I cancelled the execution and looked for other solutions as I guess if it takes so long in my computer it would take long in the Heroku server too and I'm not going to have my service paused until this operation is finished.

So I came up with a quite hacky solution that worked perfectly in my machine:

I created my update statement in an alembic file both for upgrade and downgrade:

connection = op.get_bind()


def upgrade():
    connection.execute("update pg_attribute SET atttypmod = 1000+4" + \
                       "where attrelid = 'item'::regclass and attname = 'details'", execution_options=None)


def downgrade():
    connection.execute("update pg_attribute SET atttypmod = 200+4" + \
                       "where attrelid = 'item'::regclass and attname = 'details'", execution_options=None)

And worked really fast in my machine. But When pushing it to my staging app in Heroku and executing the upgrade it prompted ERROR: permission denied for relation pg_attribute. The same happens if I try to execute the update statement directly in psql. I guess this is intentional from Heroku and that I am not supposed to update those kind of tables as I could make malfunction the database if doing it wrong. I guess forcing that update in Heroku is not the way to go.

I have also tried creating a new temporary column, copying all the data from the old-small column into that temp one, deleted the old column, created a new one with the same name as the old one but with the desired size, copied the data from the temp column and deleted it this way:

def upgrade():
    op.add_column('item', sa.Column('temp_col', sa.String(200)))
    connection.execute("update item SET temp_col = details", execution_options=None)
    op.drop_column('item', 'details')
    op.add_column('item', sa.Column('details', sa.String(1000)))
    connection.execute("update item SET details = temp_col", execution_options=None)
    op.drop_column('item', 'temp_col')


def downgrade():
    op.add_column('item', sa.Column('temp_col', sa.String(1000)))
    connection.execute("update item SET temp_col = details", execution_options=None)
    op.drop_column('item', 'details')
    op.add_column('item', sa.Column('details', sa.String(200)))
    connection.execute("update item SET details = temp_col", execution_options=None)
    op.drop_column('item', 'temp_col')

But it also takes ages and doesn't seem to be a really neat way to do it.

So my question is: what is the correct way to resize a string column in postgreSQL in Heroku through alembic without having to wait ages for the alter column to be executed?

Community
  • 1
  • 1
josebama
  • 848
  • 8
  • 11
  • 2
    Why not just do an `alter table T alter column C type text` and leave the length check up to a CHECK constraint? PostgreSQL treats them all as "TEXT with conditions" internally anyway. – mu is too short Feb 21 '14 at 19:32
  • I didn't know about the text field type, I'll use it from now on. But the alter table still takes ages with alembic. So seems that my problem is with alembic. I'm going to edit my question then and execute the alter table in the command line. But I'd still like to know what's the correct way of doing it with alembic, to keep a record of all the changes in the database – josebama Feb 21 '14 at 20:06
  • You're going to have to live with the long `ALTER` I'm afraid; I don't think Pg's constraint engine optimizes the case of *increasing* the length of a varchar length constraint. It could safely assume the constraint is already valid and doesn't need a recheck on `ALTER`, but I don't think it does. – Craig Ringer Feb 22 '14 at 04:28

0 Answers0