FireDAC + PostgreSQL: Is DDL Rollback Guaranteed in Transactions?

Answer first

No. FireDAC does not automatically wrap DDL in a transaction on PostgreSQL, and DDL rollback is only guaranteed when you explicitly control the transaction and the statement is transactionally safe in PostgreSQL.

With FireDAC + PostgreSQL, rollback of DDL works if:

• AutoCommit is disabled for the connection
• A TTransaction is started before the DDL
• The DDL statement is one PostgreSQL allows to roll back

If AutoCommit is left enabled, each DDL statement is committed immediately and cannot be rolled back.

Confirmed facts

• PostgreSQL supports transactional DDL. CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, etc., participate in the current transaction block and are undone on ROLLBACK.
• FireDAC does not automatically begin a transaction for DDL. Transaction boundaries are controlled by the application via TTransaction.StartTransaction / Commit / Rollback or via Connection AutoCommit.
• FireDAC delegates transactional guarantees to the underlying database driver. There is no built-in FireDAC detection or warning for non-transactional DDL support.

Likely explanation for the uncertainty

FireDAC’s documentation states transactional guarantees depend entirely on the database. The framework assumes the developer manages transaction scope. Because of that, developers often assume DDL is “safe” because PostgreSQL is transactional, while in practice FireDAC will auto-commit the statement if AutoCommit is true or if no explicit transaction is active.

Steps needed for this case

1. Disable auto-commit for the connection before schema work:

   FDConnection1.Params.AutoCommit := False;

2. Start an explicit transaction and keep it active for the DDL batch:

   FDTransaction1.StartTransaction;

3. Execute the DDL through the connection bound to that transaction.
4. Decide commit or rollback:

   FDTransaction1.Rollback; // or Commit

5. Verify the effect. Query pg_tables / information_schema to confirm the object is absent after rollback.

Important limits

PostgreSQL itself makes some commands non-transactional even inside an explicit transaction. Those will commit immediately regardless of FireDAC settings:

• CREATE DATABASE, DROP DATABASE
• ALTER SYSTEM
• VACUUM FULL, CREATE INDEX CONCURRENTLY
• Certain LOCK statements

Connection pooling and implicit commits on certain FireDAC operations can also break an intended transaction scope if the transaction lifetime is not managed explicitly.

One diagnostic detail that changes the recommendation: which exact DDL statements you need to roll back. If they include CREATE DATABASE, ALTER SYSTEM, or CREATE INDEX CONCURRENTLY, rollback cannot be guaranteed in PostgreSQL at all.