> On Aug 27, 2021, at 1:32 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > > On 8/27/21 11:19 AM, Miles Elam wrote: >> What is the general consensus within the community on idempotent DDL scripts, ie. consistent usage of IF EXISTS/IF NOT EXISTS/OR REPLACE for SQL init files that get checked into source control? >> My experience has been that it's much easier to manage than an ever-growing set of migration files, but I'm just a data point of one. Obviously for other engines that don't support transactional DDL, it's a non-starter, which leads me toward the notion that its lack of obvious popularity is due to its limited cross-engine viability. But PG does have transaction DDL, preventing incomplete DDL updates. However this may just be my personal bias talking. Yet I cannot seem to discount the advantages over ever-increasing volumes of Flyway-style migration files & directories: For production or dev environments? The latter is made easier if the baseline moves forward regularly. For the former the sticky bit possibly has as much to do with data migration as DDL evolution, especially if there are multiple instances (many customers) >> * being able to do diffs of changes in source control >> * limiting the size of the total SQL over time relative to what's actually in the DB structure >> * much more easily determining the most current version of a function/procedure (this bit me hard in the past with dozens of migration files) >> * the ability to prune old changes that no longer apply to any deployed version of the database >> * treating database structure as code >> The drawbacks I've run across are those areas where the EXISTS/REPLACE constructs aren't implemented like roles management, domains, constraints, etc. However those cases seem to be handled with only minor increases in complexity with judicious use of inline plpgsql. >> In others' opinions, has DDL idempotency been viable for maintenance of PG databases fo you in production? > > For me at least you will need to show examples of what you trying to achieve. I'm not seeing how a migration(change) can happen without a change of some sort. More to the point how *EXISTS/OR REPLACE helps? > >> - Miles Elam > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx > >
