[atombender]

One big argument: Transactional DDL. For example:

  begin;
  alter table foos add answer int not null default 42;
  alter table foos drop column plumbus;
  update foos set name = upper(name);
  create table bars (t serial);
  drop table dingbats;
  rollback;  // Or, of course, commit

What's the benefit? Atomic migrations. You can create, alter, drop tables, update data, etc. in a single transaction, and it will either commit complete if all the changes succeed, or roll back everything.

This is not possible in MySQL, or almost any other database [1], including Oracle — DDL statements aren't usually transactional. (In MySQL, I believe a DDL statement implicits commits the current transactions without warning, but I could be wrong.)

Beyond that, I'd mention: PostGIS, arrays, functional indexes, and window functions. You may not use these things today, but once you discover them, you're bound to.

[1] https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...

[Deimorz]

I use transactional DDL in my tests. All the tables, triggers, etc. are set up inside a transaction, and then the actual tests run inside nested transactions. At the end of the test run, the outer transaction gets rolled back, and everything disappears.

I don't know if it accomplishes anything truly new (other than ideas that aren't very useful in practice like being able to have multiple test runs going in parallel), but it's a pretty neat way to be able to do it and works well.

[atombender]

Transactional tests have some downsides, unfortunately. If your tests test transactional code, that code itself cannot create transactions; they have to use savepoints, which aren't quite the same. Transactional tests also don't work with testing anything concurrent, unless you share the session across threads/goroutines/whatever.

Lastly, if a test fails you'd typically like to leave the data behind so that you can inspect it. A transactional test that rolls back on failure won't allow that.

[munk-a]

Save points, with proper management of them, seem to match a conceptual nested transaction as far as I've seen. We've got a test bed connection manager that mocks savepoint creation, rollback and committal into transaction management functions so doing something like beginTransaction && beginTransaction works fine.

[atombender]

There may be some semantic nuances. Savepoints aren't real transactions, so they "inherit" the locks of the parent transaction, for example. But it might not matter in practice in the context of tests.

[Deimorz]

That's true, thanks. None of those are relevant for me, but there definitely are cases where it wouldn't be the right approach.

[pritambaral]

I use Pg's Template Databases[1] for this. Gives me the freedom to use transactions and concurrency in tests.

Without this built-in feature, I'd have used filesystem snapshots, if I didn't mind the time it'd take to stop and start Pg.

----

1: https://www.postgresql.org/docs/current/manage-ag-templatedb...

[h1d]

I don't think one would migrate if Pg's strength is on 'alter table' which isn't what people do on a daily basis

Might want to mention the downside of using MySQL as well. (Am also interested to know as a daily MySQL user.)

[taffer]

If Oracle DDL is not transactional, what's the point of its Edition-Based Redefinition feature?

[atombender]

Oracle's "editions" are more like versioning, I think. Last I checked, only a very limited set of schema objects were editionable (views, triggers, procedures, etc.), not including tables or indexes.