4 ms·
One big argument: Transactional DDL. For example: begin; alter table foos add answer int not null default 42; alter table foos drop column plumbus; upd
by atombender 8y ago
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_PostgreSQL:_A_Competitive_Analysis https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...
- Deimorz 8y agoI 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 8y agoTransactional 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.
- deleted 8y ago[deleted]
- Deimorz 8y agoThat's true, thanks. None of those are relevant for me, but there definitely are cases where it wouldn't be the right approach.
- munk-a 8y agoSave 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 8y agoThere 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.
- pritambaral 8y agoI 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-templatedbs.html https://www.postgresql.org/docs/current/manage-ag-templatedb...
- taffer 8y agoIf Oracle DDL is not transactional, what's the point of its Edition-Based Redefinition feature?
- atombender 8y agoOracle'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.
- h1d 8y agoI 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.)