Hacker News Reader

Comment by eatonphil | Hacker News Reader

I don't say this from experience but DDLs in postgres are transactional so I don't understand the problem with wrapping create or replaces in a transaction with no need to cross your fingers.

View on HN

The transaction behavior is fine, it's more that I've had people delete function declarations from scripts while never cleaning them up in the data warehouse and then you end up with old functions kicking around in prod. My use case is mostly warehousing, not application stuff, so I don't care about full blown migrations. Just "these are the functions that we have declared for use, no more no less".

It seems like with DBT we're getting to declarative table construction, but I'm looking forward to declarative maintenance of other data warehouse aspects.

> it's more that I've had people delete function declarations from scripts while never cleaning them up in the data warehouse

Ah that makes sense. I guess you could hack around that by having a script reading all your declarations that are checked into code and removing any that exist in the db that aren't in that list, or something.

You can use database schema compare tools for this. You create a clean empty database using the new DDL and use the tool to compare that schema with the schema for the version in test or prod. The tool will then show the differences and can create a sql script to update the database in test or prod.

I've used this a lot with SQL Server and used the Redgate tools for that and that worked very well. For PostgreSQL, it seems there's a free tool from devart: dbForge Schema Compare [0]. I've never used it and have no idea how well it works...

[0]: https://www.devart.com/dbforge/postgresql/schemacompare/down...

Reply on news.ycombinator.com