PostgreSQL Schema and Data Statement Mixing Not Supported (SQLSTATE 25007)

PostgreSQL raises ERROR: schema and data statement mixing not supported with SQLSTATE 25007 and condition name SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED when an attempt is made to combine schema-manipulation statements (DDL) and data-manipulation statements (DML) within a transaction context that does not permit such mixing.

What This Error Means

SQLSTATE class 25 covers invalid transaction state errors — conditions where a statement cannot be executed given the current state of the transaction or session. Code 25007 specifically signals that the combination of DDL and DML within the same transaction is not permitted in the current context.

In standard PostgreSQL, DDL and DML can generally coexist inside an explicit transaction block: you can CREATE TABLE and INSERT rows in the same BEGIN/COMMIT block. SQLSTATE 25007 therefore does not come from PostgreSQL's own engine under everyday usage — it is defined in the SQL standard and reserved for environments that enforce stricter separation, such as certain procedural-language extensions, foreign data wrappers, or third-party replication and connection-pooling middleware that proxies PostgreSQL.

When this error is raised, the current transaction is placed in an aborted state. No further statements will execute until the transaction is either rolled back with ROLLBACK or, if a savepoint was used, rolled back to that savepoint. The connection itself remains open.

Common Causes

1. Foreign data wrappers or external engines with restricted transaction semantics. Some FDWs and distributed SQL layers built on top of PostgreSQL (e.g., Citus in certain configurations, or non-PostgreSQL backends accessed via postgres_fdw) enforce that DDL and DML cannot be interleaved within the same remote transaction.

2. Procedural language or extension enforcing strict statement ordering. An extension or stored-procedure framework may call the server-side function ereport(ERROR, (errcode(ERRCODE_S_R_E_SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED), ...)) to reject mixed statements, usually to preserve consistency guarantees the extension needs.

3. Middleware or proxy routers intercepting transactions. Connection poolers or query routers (PgBouncer in certain modes, or application-level sharding layers) may reject mixed DDL/DML transactions when routing across nodes.

4. Application ORM generating a transaction that mixes schema migrations with data writes. Some migration frameworks (Flyway, Liquibase, ActiveRecord) open a single transaction that contains both schema changes and seed/data statements; a proxy or FDW in the path may reject this.

How to Fix schema_and_data_statement_mixing_not_supported

1. Separate DDL and DML into distinct transactions.

   Run all schema changes in one transaction block, commit it, then run data changes in a separate transaction:

   -- Transaction 1: schema changes only
   BEGIN;
   ALTER TABLE orders ADD COLUMN fulfilled_at TIMESTAMPTZ;
   COMMIT;
   
   -- Transaction 2: data changes only
   BEGIN;
   UPDATE orders SET fulfilled_at = NOW() WHERE status = 'shipped';
   COMMIT;
   

2. Check the FDW or extension documentation for allowed transaction modes.

   If you are using a foreign data wrapper, consult its documentation to understand whether DDL on the foreign side must be executed outside an open data transaction. For postgres_fdw, DDL on the remote server typically requires its own separate connection or transaction.

3. Configure your migration tool to issue DDL outside of the data transaction.

   Flyway, Liquibase, and similar tools have options to run migrations with DDL in auto-commit mode or as a separate phase:

   • Flyway: set outOfOrder=false and review whether executeInTransaction should be false for DDL-heavy migrations.
   • Liquibase: use runInTransaction="false" on changesets that contain DDL if downstream components cannot handle mixed transactions.
   • ActiveRecord / Rails: use disable_ddl_transaction! in migrations that mix DDL with data backfills, and split the migration into two.

4. Identify the component raising the error.

   The error message and stack trace should indicate the source. Check the PostgreSQL server log (typically /var/log/postgresql/postgresql-<version>-main.log) for the CONTEXT or DETAIL lines that name the extension or function that issued the error:

   grep "25007\|schema_and_data_statement_mixing" /var/log/postgresql/postgresql-*.log
   

5. Review proxy or pooler configuration.

   If a connection pooler sits between the application and PostgreSQL, check whether it operates in transaction pooling mode, which can interfere with how DDL and DML are routed. Switching to session mode, or routing DDL statements to a direct connection, often resolves the conflict.

Additional Information

• SQLSTATE 25007 belongs to class 25 (Invalid Transaction State). Related codes in the same class include 25000 (generic invalid transaction state), 25001 (ACTIVE_SQL_TRANSACTION), 25002 (BRANCH_TRANSACTION_ALREADY_ACTIVE), 25006 (READ_ONLY_SQL_TRANSACTION), and 25P01/25P02 (PostgreSQL-specific NO_ACTIVE_SQL_TRANSACTION and IN_FAILED_SQL_TRANSACTION).
• PostgreSQL's own engine does not raise 25007 in typical server-side execution — the condition is defined in the SQL standard and is surfaced by extensions or middleware that build on top of PostgreSQL.
• Drivers such as psycopg2, asyncpg, and JDBC will surface this as a TransactionRollbackError or a generic ProgrammingError subclass depending on the driver version. Always inspect the pgcode attribute (psycopg2) or getSQLState() (JDBC) to confirm it is 25007.
• Because PostgreSQL itself rarely emits this code natively, encountering it almost always points to a third-party component in the data path. Identifying that component is the most important diagnostic step.

Frequently Asked Questions

Can I reproduce SQLSTATE 25007 with plain PostgreSQL and no extensions? Under standard PostgreSQL, no. The engine permits DDL and DML in the same transaction block (a feature that distinguishes PostgreSQL from some other databases). The error is defined in the SQL standard and PostgreSQL reserves the code, but it is surfaced by extensions, FDWs, or external middleware rather than the core engine itself.

Why does this error appear after I added a connection pooler? Some poolers in transaction-mode pooling can interfere with multi-statement transactions, especially when DDL and DML are mixed and need to be routed differently. Switching to session-mode pooling, or ensuring DDL runs on a direct connection, is the typical fix.

My ORM migration ran fine on a local Postgres instance but fails in staging — why? Your staging environment likely has additional infrastructure between the application and the database: a proxy, a distributed SQL layer, or an FDW. That component enforces stricter transaction semantics than plain PostgreSQL does. Splitting the migration into a DDL phase and a DML phase (separate transactions) will make it portable across both environments.

Does rolling back fix the error? Rolling back (ROLLBACK) clears the aborted transaction state, allowing subsequent statements to run. It does not fix the root cause — you still need to restructure the transaction so that DDL and DML are not mixed in a context that prohibits it.