Menu
I know that in MySQL ddl statements such as alter table/create table/etc cause an implicit transaction commit.
As we are moving to PostgreSQL is it possible to wrap multiple DDL statments in a transaction?
This would make migration scripts a lot more robust, a failed DDL change would cause everything to rollback.
465
PostgreSQL supports transactional DDL: all DDL commands except “high-caliber” operations aimed at creation and deletion of such objects as DATABASE, TABLESPACE, CLUSTER. PostgreSQL supports multi-level transactions on save points level.
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.
Now, what does true “transactional DDL” mean? It means that all statements should be ACID, regardless of whether they are DML or DDL statements. In practice, with most databases, DDL statements break the transactionality of the enclosing transaction and cause anomalies.
I am sorry to have to inform you, but ALTER TABLE cannot be rolled back. In fact, ALTER TABLE triggers an implicit commit. In your case, the ALTER TABLE will either finish or you will have a temp table left hanging around.
DDL statements are covered by transactions. I can't find the relevant section in the official documentation, but have provided a link to the wiki which covers it.
Just remember that transactions aren't automatically opened in postgresql, you must start them with BEGIN or START TRANSACTION.
Postgresql Wiki about Transactional DDL
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
