A way to run non-transactional DDL commands / PG requires autocommit to modify a type ?!

[sqlalchemy-bot]

Migrated issue, originally created by Wichert Akkerman (@wichert)

Unfortunately not all DDL commands for PostgreSQL can be used in a transaction. I ran into this when trying to add a new value to an enum:

db=# BEGIN;
BEGIN
db=# ALTER TYPE article_type ADD VALUE 'unknown';
ERROR:  ALTER TYPE ... ADD cannot run inside a transaction block

I'm not sure what the best way to handle this is in alembic currently. Perhaps do op.execute('COMMIT') manually? Perhaps alembic needs an in-between-commit operation for this.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Well, this is tricky because the BEGIN is emitted by psycopg2. You might need to just get a raw_connection() from "op.bind" and work with the psycopg2 cursor directly for that operation.

[sqlalchemy-bot]

Wichert Akkerman (@wichert) wrote:

Looks like psycopg2 doesn't do that here for some reason. This code ran without problems:

op.execute('COMMIT')  # See https://bitbucket.org/zzzeek/alembic/issue/123
op.execute('ALTER TYPE article_type ADD VALUE \'unknown\'')

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

that's a little weird, some DBAPIs look at the SQL string to determine "auto-begin" but I didn't think psycopg2 was doing that. If you turn on statement logging in your postgresql database it'll reveal just what's going on with that.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

oh duh, because you said COMMIT, psycopg2 thought it was still in its own transaction block. I'm not sure I'd want to go further than illustrating the workaround here in the alembic docs, something like that. we'd need "database-specific notes".

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: documentation

[sqlalchemy-bot]

Lucas Taylor (@ltvolks) wrote:

Just a note that you can also set the isolation level on the connection itself prior to executing the ALTER statement:

if not op.get_context().as_sql:
    connection = op.get_bind()
    connection.execution_options(isolation_level='AUTOCOMMIT')

op.execute('ALTER TYPE article_type ADD VALUE \'unknown\'')

[sqlalchemy-bot]

Wichert Akkerman (@wichert) wrote:

@ltvolks: I'm afraid that breaks for other reasons. I tried both your version and the simpler op.execute(...., {'isolation_level': 'AUTOCOMMIT'})), but in both cases psycopg will issue a ROLLBACK which blasts away any earlier changes. This is the PostgreSQL statement log:

LOG:  statement: BEGIN
LOG:  statement: SELECT t.oid, typarray
	FROM pg_type t JOIN pg_namespace ns
	    ON typnamespace = ns.oid
	WHERE typname = 'hstore';
	
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: select version()
LOG:  statement: select current_schema()
LOG:  statement: show transaction isolation level
LOG:  statement: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
LOG:  statement: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
LOG:  statement: SELECT 'x' AS some_label
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: show standard_conforming_strings
LOG:  statement: select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname='alembic_version'
LOG:  statement: select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname='alembic_version'
LOG:  statement: 
	CREATE TABLE alembic_version (
		version_num VARCHAR(32) NOT NULL
	)
	
	
LOG:  statement: SHOW default_transaction_isolation
LOG:  statement: ROLLBACK
LOG:  statement: SET default_transaction_isolation TO DEFAULT
LOG:  statement: ALTER TYPE article_type ADD VALUE IF NOT EXISTS 'necklace'
LOG:  statement: INSERT INTO alembic_version (version_num) VALUES ('4bd730372bac')
ERROR:  relation "alembic_version" does not exist at character 13
STATEMENT:  INSERT INTO alembic_version (version_num) VALUES ('4bd730372bac')
LOG:  statement: SET default_transaction_isolation TO 'read committed'

[sqlalchemy-bot]

Lucas Taylor (@ltvolks) wrote:

@wichert: I believe that psycopg2 is issuing a ROLLBACK here because it detects an in-progress transaction. I'm guessing that when you change the isolation level, the driver needs to be in a known state so it issues the ROLLBACK.

If you can, try setting transaction_per_migration=True for the migration context. This is the setting I use and in the statement log below, we can see that there is no ROLLBACK after SHOW default_transaction_isolation, presumably because a transaction has not already begun. This aligns with your earlier comment.

If that isn't acceptable for your workflow, then you might need to isolate your migrations that need to be run outside of a transaction and step through the upgrades incrementally (alembic upgrade +1)

LOG:  statement: BEGIN
LOG:  statement: SELECT t.oid, typarray
    FROM pg_type t JOIN pg_namespace ns
        ON typnamespace = ns.oid
    WHERE typname = 'hstore';
    
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: select version()
LOG:  statement: select current_schema()
LOG:  statement: show transaction isolation level
LOG:  statement: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
LOG:  statement: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
LOG:  statement: SELECT 'x' AS some_label
LOG:  statement: ROLLBACK
LOG:  statement: BEGIN
LOG:  statement: show standard_conforming_strings
LOG:  statement: select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname='alembic_version'
LOG:  statement: select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname='alembic_version'
LOG:  statement: 
    CREATE TABLE alembic_version (
        version_num VARCHAR(32) NOT NULL
    )
    
    
LOG:  statement: COMMIT
LOG:  statement: SHOW default_transaction_isolation
LOG:  statement: SET default_transaction_isolation TO DEFAULT
LOG:  statement: ALTER TYPE article_type ADD VALUE IF NOT EXISTS 'necklace'
LOG:  statement: INSERT INTO alembic_version (version_num) VALUES ('4951454fe44c')
LOG:  statement: SET default_transaction_isolation TO 'read committed'

This log was generated with:

• stock alembic env.py, with the addition of transaction_per_migration=True
• No alembic_version table to begin
• Pre-created ENUM article_type
• Single migration revision containing:

def upgrade():
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')

    op.execute("ALTER TYPE article_type ADD VALUE IF NOT EXISTS 'necklace'")

(Your log can be reproduced exactly with the default transaction_per_migration=False)

[sqlalchemy-bot]

Josh Kupershmidt (@schmiddy) wrote:

@ltvolks I believe you need to remember to set the connection back into READ_COMMITTED mode (or whatever it was prior to being forced into AUTOCOMMIT) at the end of upgrade(), since there may be subsequent migrations after the current one, right? E.g.

def upgrade():
    connection = None
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')

    op.execute("ALTER TYPE article_type ADD VALUE IF NOT EXISTS 'necklace'")

    if connection is not None:
        connection.execution_options(isolation_level='READ_COMMITTED')