Re: Persistent changes in rolled-back transactions

> On Nov 9, 2022, at 8:23 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> Or just read the documentation for the current version (I seem to recall it used to be non-transactional, maybe...doesn't matter now).

In a lot of other databases DDL is not transactional and truncate is DDL — most likely confusing it with another RDBMS systems.

DDL is transactional in PostgreSQL thus you can even rollback table changes, etc. which is not possible in other RDBMS systems.

prod=# create table demo (a int);
CREATE TABLE
prod=#
prod=# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
Access method: heap

prod=# begin;
BEGIN
prod=*# alter table demo add column b int;
ALTER TABLE
prod=*# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | integer | | | | plain | | |
Access method: heap

prod=*# rollback;
ROLLBACK
prod=# \d+ demo;
Table "dev.demo"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
Access method: heap